3

I created a view with

select a, st_union(geom)
from table
group by a

srid: 25832, geometry type: Multipolygon

The view has no SRID and the geometry type is "GEOMETRY".

Now i want to calculate the area with the geometry in the view. Because of the missing SRID the results are not correct. How can i set the SRID and geometry type for the gemoetries for the view?

4

You will need to explicitly cast the calculated geometries as the correct type, including their typemods, or PostgreSQL will not register the typemods in the View:

SELECT a,
       ST_Union(geom)::GEOMETRY(MULTIPOLYGON, 25832) AS geom
FROM   <table>
GROUP BY
       a
;

Note that this will fail if the calculated geometries do not match fully with the type definition!

5
  • I'm curious if you have intuition as to why this is the case. Why doesn't the SRID of the original geometry (assuming it is set) propagate to the VIEW? – amball Oct 25 '20 at 22:42
  • 1
    @amball PostgreSQL doesn't have a clue that there may be a link between the source type and this 100% newly created piece of data; it only knows that ST_Union returns a (generic) GEOMETRY type. – geozelot Oct 26 '20 at 9:53
  • But normally PostgreSQL seems to retain the SRID. E.g. SELECT ST_SRID(ST_Union(geom)) FROM mytable; returns the original SRID. Is this something particular to a VIEW? – amball Oct 27 '20 at 10:15
  • 1
    @amball indeed, the actual information is stored per instance of and in the type itself (no loss of information)! However, the crucial part is where these are defined as type specific constraints via the type-modifiers: for e.g. DDL on the relations, these relation attributes are followed across the system tables pg_class and pg_attributes, but PostgreSQL does not follow these when handled as function parameters (and this includes all operators, as they are implemented as functions), meaning that they are checked when passed to a function, but not retained in the process. – geozelot Oct 27 '20 at 12:05
  • thanks for the detailed clarification! – amball Oct 28 '20 at 15:13
1

https://postgis.net/docs/ST_SetSRID.html

ST_SetSRID and ST_Multi:

select a, st_setsrid(st_multi(st_union(geom)), 25832) from table group by a
1

I got to mix the statements:

select a, 
st_multi(ST_Union(geom))::GEOMETRY(MULTIPOLYGON, 25832) 
as geom from table GROUP BY  a

Best Regards

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.