Ensuring SRID on geometry column in PostGIS

Question

I am using a table that has a geometry type column called "wkt". Its definition in the table looks like this:

create table sometable (
   ...
   wkt geometry not null,
   ...
);

I want to change it to make sure that any geometry inserted will have an SRID of 4326. As a result, I want to do something like this to the "wkt" column:

wkt geometry(POINT, 4326) not null

But, this specifies that the geometry for the column is a POINT and I want the column to take any geometry as specified in the current definition.

How can I ensure that an SRID is applied to the column? Would I have to add a constraint to the "wkt" column such as "enforce_srid_the_geom" (as I gathered from the post How to change the SRID of exisisting data in PostGIS?) OR could I use some other form of the geometry type that I don't know about?

Answer 1

You don't need constraints for PostGIS 2.x, just us typmods. For 2D geometries with SRID=4326, the typmod is geometry(Geometry,4326). Or for M-dimension geometries, it would be geometry(GeometryM,4326) (you get the idea).

Example:

create temp table sometable(wkt geometry(Geometry,4326));

-- works
insert into sometable(wkt) values('SRID=4326;POINT(1 2)')
insert into sometable(wkt) values('SRID=4326;LINESTRING(1 2, 3 4)')

-- does not work
insert into sometable(wkt) values('POINT(1 2)')
-- ERROR:  Geometry SRID (0) does not match column SRID (4326)
insert into sometable(wkt) values('SRID=4326;POINT M(1 2 3)')
-- ERROR:  Geometry has M dimension but column does not

But as others have cautioned, make sure you can use the wkt data in the end-user applications. Also, it isn't technically stored as well-known text—it is stored as well-known binary or WKB.

Answer 2

@BradHards is right. Having mixed types is probably going to cause problems down the road. If you're sure your tools can handle mixed types, or you're going to be retrieving them some other way (through DB views, or programatically) you can split the constraints into two parts, either of which is optional:

To modify your existing table

-- Only check the SRID
ALTER TABLE mytable ADD CONSTRAINT enforce_srid_wkt CHECK (ST_Srid(wkt) = 4326);

-- Only check the dimensions of the geometry (2 is for polygons only)
ALTER TABLE mytable ADD CONSTRAINT enforce_dimension_wkt CHECK (ST_Dimension(wkt) = 2);

If you're defining a new table just add it on a new line

create table sometable (
   ...
   wkt geometry not null,
   CONSTRAINT enforce_srid_the_geom CHECK (ST_Srid(wkt) = 4326)
);

More reading / details:

• https://postgis.net/docs/ST_SRID.html
• https://postgis.net/docs/ST_Dimension.html