Invalid SRID return from PostGIS ST_Centroid Function

Question

I'd like to get the centroid of a set of polygons with SRID 4326

I start from this simple query to verify the ST_Centroid function

CREATE TABLE test AS
SELECT ogc_fid, geom, ST_Centroid(geom) centriod_geom 
FROM s_table

However, when I check the SRID of two geometry columns in the test table, it appears the SRID of centriod_geom changes to 0.

SELECT Find_SRID('public', 'test', 'geom');
SELECT Find_SRID('public', 'test', 'centriod_geom');

geometry column	find_srid
geom	4326
centriod_geom	0

What should I do if I need the centriod_geom to be 4326?

Answer 1

PostGIS implements type specific meta information (geometry type & SRID) via the PostgreSQL typemod system catalogues; a typemod adds specific constraints to (base) types, and cascades their restrictions to e.g. columns. Without specifically adding these constraints to the extension types (GEOMETRY/GEOGRAPHY), their defaults (SRID = 0) will be assumed.

When constructing geometries in a query, you will have to explicitly cast them with their desired typemod constraints to be recognized in the system catalogues (which are used by e.g. the geometry_columns/geography_columns Views, and functions like Find_SRID):

CREATE TABLE test AS
  SELECT ogc_fid,
         geom,
         ST_Centroid(geom)::GEOMETRY(POINT, 4326) AS centriod_geom 
  FROM   s_table
;

---

Note that having more than one geometry column may lead to issues with GIS client software.