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
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.
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()
VOLATILE NOT LEAKPROOF
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" ));
Then I created a trigger like this
CREATE TRIGGER insert_farm_polygon
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
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