[postgis-users] ST_Union - Resulting SRID set to 0

[Sandro Santilli]

On Thu, Jun 27, 2013 at 10:48:47AM -0400, Jason Greenlaw - NOAA Affiliate wrote:
> Hello all,
> 
> I have a table with multipolygon geometry in EPSG:3857 (web mercator) that
> I'm trying to dissolve (grouping by a field) into another table using an
> aggregate call to ST_Union, but for some reason the SRID isn't carrying
> over (SRID gets set to zero).  I've also tried calling ST_SetSRID on the
> result but that too results in SRID = 0.
> 
> I'm fairly new to PostGIS - can anyone point out my mistake?
> 
> Here's the query I'm using:
> 
>     CREATE TABLE ds1_dissolved AS SELECT
> code,ST_Union(ST_SnapToGrid(wkb_geometry, 0.0001)) AS wkb_geometry FROM ds1
> WHERE ST_IsValid(wkb_geometry) GROUP BY code;
> 
> or, with an additional call to ST_SetSRID:
> 
>     CREATE TABLE ds1_dissolved AS SELECT
> code,ST_SetSRID(ST_Union(ST_SnapToGrid(wkb_geometry, 0.0001)), 3857) AS
> wkb_geometry FROM ds1 WHERE ST_IsValid(wkb_geometry) GROUP BY code;
> 
> Both methods seem to work except for the SRID being set to zero as reported
> in geometry_columns:
> 
>     # SELECT * FROM public.geometry_columns WHERE
> f_table_name='ds1_dissolved';
> 
>      f_table_catalog | f_table_schema |        f_table_name        |
> f_geometry_column | coord_dimension | srid |     type
> 
> -----------------+----------------+----------------------------+-------------------+-----------------+------+--------------
>      dbname          | schemaname     | ds1_dissolved | wkb_geometry      |
>               2 |    0 | GEOMETRY
>     (1 row)
> 
> Describing the original table using \d reports the type of the
> 'wkb_geometry' field as:  geometry(MultiPolygon,3857)
> Describing the dissolved table using \d reports the type of the
> 'wkb_geometry' field as: geometry
> 
> Any help greatly appreciated!

The SRID of your geometries is NOT zero, only the _type_ of your geometry
doesn't constraint a SRID. You can check the SRID of your geometries with
a query like this:

 SELECT distinct ST_Srid(wkb_geometry) from schemaname.ds1_dissolved;

If you want to _constraint_ a SRID you'll have to cast it
in the "CREATE TABLE AS" query:

 CREATE TABLE ds1_dissolved AS SELECT code,
    ST_Union(ST_SnapToGrid(wkb_geometry, 0.0001))::geometry(geometry,3857)
    AS wkb_geometry
 FROM ds1 WHERE ST_IsValid(wkb_geometry) GROUP BY code;

Similarly, you could constraint a type (instead of the generic "geometry),
althought ST_Union may return unexpected types.

--strk;