# Plot ODK polygon on leaflet map **URL:** \ **Category:** Support\ **Tags:** odk-aggregate\ **Created:** [May 22, 2020, 11:10am UTC](https://forum.getodk.org/t/plot-odk-polygon-on-leaflet-map/26617 "2020-05-22T11:10:56Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![dohji](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/dohji/32/8288_2.png) [@dohji](https://forum.getodk.org/u/dohji)\ **Post date:** [May 22, 2020, 11:10am UTC](https://forum.getodk.org/t/plot-odk-polygon-on-leaflet-map/26617/1 "2020-05-22T11:10:56Z")
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 ---
**Author:** ![mathieubossaert](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/mathieubossaert/32/6131_2.png) [@mathieubossaert](https://forum.getodk.org/u/mathieubossaert)\ **Post date:** [May 22, 2020, 1:01pm UTC](https://forum.getodk.org/t/plot-odk-polygon-on-leaflet-map/26617/2 "2020-05-22T13:01:09Z")
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 😉 > [@Geo fields should be stored in wkt format (Well Known Text)](https://forum.getodk.org/t/geo-fields-should-be-stored-in-wkt-format-well-known-text/7048): > > Good evening everybody, I would like to suggest some improvement of the spatial data storage. Actually, geopoint are stored into 4 attributes (longitude, latitude, elevation and accuracy), Geotrace and geoshape are stored in a text field. The format of that string is not so simple. The use of Well Known Text format could be a great enhancement. All the databases supported by odk do understand that GIS format. [https://fr.wikipedia.org/wiki/Well-known\_text](https://fr.wikipedia.org/wiki/Well-known_text) Actually we have to create some fun sq… And > [@ODK geoshape/geotrace/geopoint to WKT](https://forum.getodk.org/t/odk-geoshape-geotrace-geopoint-to-wkt/21062): > > After kml export by @Xiphware and geojson export by @detrygeoffroy, I simply adapted @Xiphware example forms to generate wkt (point, linestring, polygon) from geopoint, geotrace and geoshape odk fields. In opposition to geojson and kml, WKT does only describes the geometry, it does not contains any attribute value. So you can collect data and open exported CSV within QGIS with "delimited text layer" tool, choose the column which store wkt, select the projection (4326) and show y… When you'll get a chance, please take some time to [Introduce yourself here! - #1132 by lamo\_jimmy](https://forum.getodk.org/t/introduce-yourself-here/6671/1132) ---
**Author:** ![dohji](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/dohji/32/8288_2.png) [@dohji](https://forum.getodk.org/u/dohji)\ **Post date:** [May 22, 2020, 2:29pm UTC](https://forum.getodk.org/t/plot-odk-polygon-on-leaflet-map/26617/3 "2020-05-22T14:29:38Z")
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. ---
**Author:** ![mathieubossaert](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/mathieubossaert/32/6131_2.png) [@mathieubossaert](https://forum.getodk.org/u/mathieubossaert)\ **Post date:** [May 22, 2020, 2:44pm UTC](https://forum.getodk.org/t/plot-odk-polygon-on-leaflet-map/26617/4 "2020-05-22T14:44:37Z")
Just call it with null values for columns you don't have. ```auto 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)** ---
**Author:** ![dohji](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/dohji/32/8288_2.png) [@dohji](https://forum.getodk.org/u/dohji)\ **Post date:** [May 22, 2020, 2:59pm UTC](https://forum.getodk.org/t/plot-odk-polygon-on-leaflet-map/26617/5 "2020-05-22T14:59:26Z")
Thank you @mathieubossaert You are a lifesaver 😇 ---
**Author:** ![dohji](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/dohji/32/8288_2.png) [@dohji](https://forum.getodk.org/u/dohji)\ **Post date:** [May 22, 2020, 8:54pm UTC](https://forum.getodk.org/t/plot-odk-polygon-on-leaflet-map/26617/6 "2020-05-22T20:54:11Z")
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 ---
**Author:** ![mathieubossaert](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/mathieubossaert/32/6131_2.png) [@mathieubossaert](https://forum.getodk.org/u/mathieubossaert)\ **Post date:** [May 23, 2020, 6:34am UTC](https://forum.getodk.org/t/plot-odk-polygon-on-leaflet-map/26617/7 "2020-05-23T06:34:32Z")
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](https://framagit.org/formationodk/formulaires/-/wikis/gestion-et-r%C3%A9cup%C3%A9ration-des-donn%C3%A9es-en-base#cr%C3%A9ation-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. ---
**Author:** ![dohji](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/dohji/32/8288_2.png) [@dohji](https://forum.getodk.org/u/dohji)\ **Post date:** [May 23, 2020, 8:22am UTC](https://forum.getodk.org/t/plot-odk-polygon-on-leaflet-map/26617/8 "2020-05-23T08:22:32Z")
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 ---
**Author:** ![mathieubossaert](https://getodk.b-cdn.net/user_avatar/forum.getodk.org/mathieubossaert/32/6131_2.png) [@mathieubossaert](https://forum.getodk.org/u/mathieubossaert)\ **Post date:** [May 23, 2020, 2:19pm UTC](https://forum.getodk.org/t/plot-odk-polygon-on-leaflet-map/26617/9 "2020-05-23T14:19:00Z")
Great. But I will take a look at this because I don't think security definer is needed here...