I'm not an expert. Yesterday I found out that SRID in PostGIS is property of the column - an actual constraint that is even stored in pg_constraints table and then retrieved through geometry_columns view. Previously I thought that SRID is set and stored per geometry record.

So given that, I now have several questions:

  1. If it's an actual constraint why it's not enforced - e.g. why is that I can still write into the column a geometry with any SRID?
  2. Why methods like ST_SRID or ST_SETSRID accept geometry as an argument rather than column name?
  3. Also currently to fetch the geometries from DB we use local hardcoded map of SRIDs by region, can't we use SRIDs from geometry_columns instead?
  4. What's the point of storing a SRID per column like that if it's not even used for anything anywhere?
  • Read this postgis.net/docs/using_postgis_dbmanagement.html, it may answer some of your questions. It has some errors but it is not as heavy as portal.opengeospatial.org/files/?artifact_id=25355 and portal.opengeospatial.org/files/?artifact_id=25354 but I recommend to read them also.
    – user30184
    Sep 8, 2020 at 7:31
  • It is preferred if you can post separate questions instead of combining your questions into one. That way, it helps the people answering your question and also others hunting for at least one of your questions. Thanks!
    – Ian Turton
    Sep 8, 2020 at 7:33
  • 2
    A column is a data type container, strictly typed to the (C) base data type it was created with (either new or via ALTER COLUMN); the system will enforce all inherent constraints of that data type on INSERT or UPDATE, including their typemods. Assuming your column is registered in the system relations: if it is of type GEOMETRY, it will accept all equal types, not considering their typemods; but if your column is of type GEOMETRY(4326), the system will enforce the CRS typemod. The typemods are property of the type, and a column is defined by these types.
    – geozelot
    Sep 8, 2020 at 8:13
  • @IanTurton these are very simple and very interrelated questions. I doubt they will make any sense without context or each other.
    – jayarjo
    Sep 8, 2020 at 9:54