1

I am trying to update the SRID of a PolygonZ column using:

select UpdateGeometrySRID('public', 'bfp_clusters', 'convex_hull', 4326) ;

I get the following error:

SQL Error [22023]: ERROR: Geometry has Z dimension but column does not
  Where: SQL statement "ALTER TABLE public.bfp_clusters ALTER COLUMN convex_hull TYPE  geometry(Geometry, 4326) USING public.ST_SetSRID(convex_hull,4326);"
PL/pgSQL function updategeometrysrid(character varying,character varying,character varying,character varying,integer) line 80 at EXECUTE
SQL statement "SELECT public.UpdateGeometrySRID('',$1,$2,$3,$4)"
PL/pgSQL function updategeometrysrid(character varying,character varying,character varying,integer) line 5 at SQL statement

1 Answer 1

1
ALTER TABLE public.bfp_clusters
ALTER COLUMN convex_hull TYPE geometry(GeometryZ, 32643)
USING ST_Force3D(convex_hull);

followed by:

select UpdateGeometrySRID('public', 'bfp_clusters', 'convex_hull', 4326) ;

does the trick!

2
  • Instead of "select UpdateGeometrySRID('public', 'bfp_clusters', 'convex_hull', 4326) ; "--- could I have used: UPDATE public.bfp_clusters SET convex_hull = ST_SetSRID(convex_hull, 4326); ?
    – analyst92
    Aug 23, 2023 at 11:14
  • 1
    UpdateGeometrySRID will modify the table AND will set the SRID of each geometry, while the update will only set the SRID of each geometry. Since you have used a different SRID in your alter statement and in the update statement, the last command will error out while the 1st would work (and wreck the data by setting a different SRID without reprojecting the geometries)
    – JGH
    Aug 23, 2023 at 13:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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