2

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?

2
  • What version of PostGIS? The intended behaviour is that functions keep the SRID in created geometry. This is fixed in recent versions.
    – dr_jts
    Apr 26, 2021 at 17:46
  • I am currently using version 3.1.1 Apr 26, 2021 at 23:35

1 Answer 1

6

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.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.