Why does ST_MakeValid() strip SRID from already-defined geometries?

Question

I'm attempting to use the PostGIS ST_MakeValid() function on a series of mostly-concentric isodistance multipolygons . . .

. . . which are defined with clear geometry type and SRID (and while they may not be perfectly valid, are still valid enough to render in QGIS as shown above):

trade=# \d tmp1
                           Table "public.tmp1"
  Column   |            Type             | Collation | Nullable | Default 
-----------+-----------------------------+-----------+----------+---------
 the_geom  | geometry(MultiPolygon,4326) |           |          |

Unfortunately, the ST_MakeValid() function is stripping both SRID and geometry type when I use it to create a new table:

trade=# CREATE TABLE tmp2 AS (SELECT ST_MakeValid(the_geom) AS the_geom_valid FROM tmp1);
SELECT 25
trade=# \d tmp2
                    Table "public.tmp2"
     Column     |   Type   | Collation | Nullable | Default 
----------------+----------+-----------+----------+---------
 the_geom_valid | geometry |           |          |

. . . and ST_SetSRID() cannot resolve it, either by creating a new table:

trade=# CREATE TABLE tmp3 AS (SELECT ST_SetSRID(the_geom_valid,4326) AS the_geom_srid FROM tmp2);
SELECT 25
trade=# \d tmp3
                    Table "public.tmp3"
    Column     |   Type   | Collation | Nullable | Default 
---------------+----------+-----------+----------+---------
 the_geom_srid | geometry |           |          |

. . . or by nesting functions:

trade=# CREATE TABLE tmp4 AS (SELECT ST_SetSRID(ST_MakeValid(the_geom),4326) AS the_geom_all FROM tmp1);
SELECT 25
trade=# \d tmp4
                    Table "public.tmp4"
     Column    |   Type   | Collation | Nullable | Default 
 --------------+----------+-----------+----------+---------
  the_geom_all | geometry |           |          |

. . . or even by using everyone's favorite ST_MakeValid() semi-substitute, ST_Buffer():

trade=# CREATE TABLE tmp5 AS (SELECT ST_Buffer(the_geom,0) AS the_geom_buffer FROM tmp1);
SELECT 25
trade=# \d tmp5;
                     Table "public.tmp5"
     Column      |   Type   | Collation | Nullable | Default 
-----------------+----------+-----------+----------+---------
 the_geom_buffer | geometry |           |          |

I can't find any documentation that suggests this is expected behavior when using ST_MakeValid() - how can I create valid geometries without losing geom type and SRID?

Hi, neighbor. Maybe give ST_Multi(ST_CollectionExtract(ST_MakeValid(geom), 3))::geometry(MultiPolygon, 4326) a shot? — dbaston
@dbaston that was totally it; Darrell mentioned it below as well. Thanks! — Bill Morris
Yup, only difference is ST_CollectionExtract effectively giving you a safe cast. — dbaston

Darrell Fuhriman Darrell Fuhriman · Accepted Answer · 2019-11-01T21:58:58

The SRID is being preserved. For instance, try this:

SELECT st_srid(the_geom_buffer) FROM tmp2 LIMIT 1;

and you should see something like:

┌─────────┐
│ st_srid │
├─────────┤
│    4326 │
└─────────┘

The reason for this is that ST_MakeValid() can return any of a number of types, but can't know what they'll be ahead of time. From the documentation:

In case of full or partial dimensional collapses, the output geometry may be a collection of lower-to-equal dimension geometries or a geometry of lower dimension.

So the only option is to return a generic geometry.

However, if you are confident that won't happen, you can force it using a cast (or an ALTER statement post hoc):

CREATE TABLE tmp2 AS 
  SELECT 
    ST_MakeValid(the_geom)::geometry(MultiPolygon, 4326) AS the_geom_valid
  FROM tmp1;

Why does ST_MakeValid() strip SRID from already-defined geometries?

1 Answers