0

I’m trying to update geometry table using the following command but get

parse error – invalid geometry HINT: “MULTIPOINT(“ <-- parse error at position 11 within geometry

Update geo_table
Set geom =ST_ConvexHull(
                            ST_GeomFromText('MULTIPOINT(co_ord_string)', 4326));
WHERE 
Id =1234;

originally I created this data with a perl script something like

 insert into geo_table (stuff,date, co_ord_string,geom) values (‘Hello’,’02-07-2018 00:00:00’, T_ConvexHull(ST_GeomFromText('MULTIPOINT($co_ord_string)', 4326));

The co_ord_string is a series of long lat points separated by a comma. These don’t create perfect polygons as they may contain internal points but using ST_ConvexHull a polygon is created

only by viewing the data in Qgis I can see there are errors in the original coordinates that need manually editing (Missing minus signs some points or other issues ) and the geometry recreating

Could someone help with the syntex

  • What is in coordstring? – Ian Turton Jul 3 '18 at 7:16
  • “MULTIPOINT(“, ‘Hello’ These are not ASCII quotes. Did you copy/paste everything correctly into this question? – CL. Jul 3 '18 at 11:16
  • @Ian Turton co_ord_string is the column name containing a list of Long Lat points separated by a comma. If this value is manually copied the query works. – Holly Jul 4 '18 at 17:47
  • @CL the Non ASCII Quotes are due to having to email question home in order to post I forgot to replace them sorry – Holly Jul 4 '18 at 17:48
0

I found out how to do this by concatenating the query so it gets the values from the database object co_ord_string

UPDATE geo_table
SET geom =ST_ConvexHull(
 ST_GeomFromText('MULTIPOINT(' || co_ord_string|| ')', 4326)
);
WHERE Id =1234;

Maybe this will help others

  • You can accept your own answer with the checkbox – BERA Aug 9 '18 at 9:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.