I wrote up a quick PL/pgSQL script that creates two new columns for centroid_longitude and centroid_latitude and uses data from a PostGIS geometry column and the ST_X(), ST_Y(), and ST_Centroid() PostGIS functions to calculate the values.
alter table nhd_hr_nhdwaterbody ADD COLUMN centroid_latitude numeric(19,11);
alter table nhd_hr_nhdwaterbody ADD COLUMN centroid_longitude numeric(19,11);
UPDATE nhd_hr_nhdwaterbody SET centroid_longitude=ST_X(ST_Centroid(geom)), centroid_latitude=ST_Y(ST_Centroid(geom));
When I run the script for a database that has several million entries, I get the following error.
ERROR: lwgeom_centroid: GEOS Error: IllegalArgumentException: Invalid number of points in LinearRing found 3 - must be 0 or >= 4
As the error clearly states, the script is having an issue because ST_Centroid() requires that a geometry has 0 points or >= 4, but some of my data do not have that many points. For my current application, I would just like to skip the entries that do not meet the criteria of having 0 or greater than or equal to 4 points.
Is there a way for me to modify this script so that the invalid geometries simply have their latitude and longitude values set to null?
Example Geometry:
"01060000A0E610000001000000010300008001000000090000002CCEE69D9E9151C0286E1A1F3E3C46400000000000000000682D06E09F9151C0C0E9DF28403C46400000000000000000A48D6E9F9F9151C078231061423C464000000000000000009C9D35FF9E9151C0B848616C433C46400000000000000000C88E061C9D9151C020568951433C4640000000000000000008A4F59B9C9151C0B8CFD508433C4640000000000000000060C4A6B09C9151C0004FD209423C464000000000000000003884B6DB9C9151C0088727EE3F3C464000000000000000002CCEE69D9E9151C0286E1A1F3E3C46400000000000000000"
Just add a WHERE clause which tests the number of points (untested):
UPDATE nhd_hr_nhdwaterbody SET
centroid_longitude=ST_X(ST_Centroid(geom)),
centroid_latitude=ST_Y(ST_Centroid(geom))
WHERE NOT ST_NumPoints(geom) between 1 and 3;
The co-ords for the ones that don't get updated should remain NULL.