[GIS] 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.