PostGIS Update parse error – invalid geometry

Question

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.

What is the correct syntax?

• Ian Turton: What is in coordstring?
• CL.: "MULTIPOINT(", ‘Hello’ These are not ASCII quotes. Did you copy/paste everything correctly into this question?
• Holly: @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: @CL the Non ASCII Quotes are due to having to email question home in order to post I forgot to replace them sorry

Answer 1

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;

• Bera: You can accept your own answer with the checkbox