0

I have a table where where SRID of my geometry is set to 0

I am trying to update the SRID using

SELECT UpdateGeometrySRID('location','geom',4326)

The equivalent DDL statement would be

ALTER TABLE public.location ALTER COLUMN geom TYPE geometry(Geometry, 4326) USING public.ST_SetSRID(geom,4326)

However, I get an error

"[22023] ERROR: Geometry has Z dimension but column does not"

I understand that this is likely because my geometry column has mixed z (3d) and non-z (2d) information. I have seen some solutions that force 3d or 2d using something like:

ALTER TABLE location ALTER COLUMN geom TYPE geometry(MultiLineStringZ) USING ST_Force_3D(geom);

However, this solution won't work for me because I need to maintain the 2d/3d values and I have mixed sub types (MultiLineStringZ, Point, Polygon) and fully dynamic geometry column, so I can't specify the geometry subtype modifier (e.g ALTER COLUMN geom TYPE geometry(Geometry, 4326) vs ALTER COLUMN geom TYPE geometry(MultiLineStringZ)).

Another post mentioned this type of issue but I can't figure out a solution.

Xander
  • 1
  • I don't think it is possible to specify an srid using the generic GEOMETRY type (not the geometry typemod, which restricts to 2D). You can add a constraint though, like in AddGeometryColumn – JGH Sep 09 '22 at 12:38
  • @JGH Okay so if I understand correctly, there isn't a way to update/specify a srid for the generic geometry column type (which I am working with) so the work around would be to use AddGeometryColumn() and use the srid (and generic geometry) constraints, then move my existing data into the new column? Sorry if I am not understanding correctly/making sense. I am new to PostGIS and working on a database/schema that I didn't set up. – Xander Sep 09 '22 at 19:06
  • no no, sorry, it was meant to illustrate what to do, i.e. to add a constraint to the column preventing geometry with a different SRID from being inserted, something like ALTER TABLE location ADD CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 4326); Of course you would still have to update the srid of each row first UPDATE location SET geom = st_setsrid(geom,4326); – JGH Sep 09 '22 at 19:39

0 Answers0