Plot ODK polygon on leaflet map

Support
1

I created a form that collects polygon data. I want to be able to plot the polygons on a leaflet map but before then, the shape field has to be converted to a geom.
I tried creating a view within which the raw shape is converted to geom using PostGIS ST_GeomFromText. The problem is that each point in the polygon comes with the accuracy and altitude and this makes the PostGIS ST_GeomFromText see the polygon as invalid.

This is what ODK saves for a polygon 5.58993076 -0.46402716 94.0 3.0;5.58988656 -0.46405251 95.0 3.0;5.58993374 -0.46416658 94.0 3.0;5.58997246 -0.46415045 90.0 3.0;5.58993076 -0.46402716 94.0 3.0;.
I need it to be like this 5.58993076 -0.46402716,5.58988656 -0.46405251,5.58993374 -0.46416658,5.58997246 -0.46415045,5.58993076 -0.46402716

I need your help @yanokwa and the gurus

2

Hi @dohji,

you should take a look at this discussion and this showcase. You will find a function to transform ODK geom to postgis geometry and a show case to transform ODK geo fields to WKT :wink:

And

When you'll get a chance, please take some time to Introduce yourself here! - #1132 by lamo_jimmy

3

Thanks for the reply @mathieubossaert
I have seen the function but I can't quite understand how to use it. My data does not have a lat/long, it only has the string of coordinates. I will be very grateful if you can help me.

4

Just call it with null values for columns you don't have.

SELECT 
   data."_URI",  
   odk.geom_from_odk_to_postgis(null::numeric, null::numeric, geortrace_column, geoshape_column) AS geom
FROM "YOUR_DATA_TABLE"

So instead of calling ST_GeomFromText(geotrace) in you view
you can call geom_from_odk_to_postgis(null::numeric, null::numeric, geortrace, null::text)

5

Thank you @mathieubossaert You are a lifesaver :innocent:

6

I created a table of _URI and geom
I created a trigger function that calls the geom_from_odk_to_postgis function to insert the polygon and _URI into my table.
This is my function
CREATE FUNCTION sme_aggregate.create_farm_mapping_polygon()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
INSERT INTO sme_aggregate.farm_mapping_polygons("_URI", geom)
VALUES(NEW."_URI",sme_aggregate.geom_from_odk_to_postgis(null::numeric, null::numeric, null::text, NEW."BASIC_FARM_SHAP" ));
RETURN NEW;
END;
$BODY$;

Then I created a trigger like this
CREATE TRIGGER insert_farm_polygon
AFTER INSERT
ON sme_aggregate."BUILD_FARM_MAPPING_FORM_1590142647_CORE"
FOR EACH ROW
EXECUTE PROCEDURE sme_aggregate.create_farm_mapping_polygon();

Now the ODK collect cannot send finalized form. It shows generic exception error
Meanwhile, the functions and triggers work fine when I try a normal insert query on the core table

7

Hi @dohji,
Your trigger should return null instead of new.
https://framagit.org/formationodk/formulaires/-/wikis/gestion-et-récupération-des-données-en-base#création-de-la-fonction-dinsertion

In this example I use a view to generate objects that are not already created and then insert those lines into the table. I ecécité the trigger for each statement instead of each row.

8

It is working fine now. The problem had to do with two things. The function returning NEW instead of NULL and also the language specification was like LANGUAGE 'plpgsql' COST 100 VOLATILE NOT LEAKPROOF, I had to change it to LANGUAGE 'plpgsql' COST 100 VOLATILE SECURITY DEFINER

Thanks @mathieubossaert

9

Great. But I will take a look at this because I don't think security definer is needed here...