I have a table with a geometry collection (polygon and point). QGIS can not read the geometry collection, so I want to create a view for the polygon type and the point type. My SQL function for the polygon type looks like this:
CREATE VIEW parcel_polygons AS (
SELECT
row_number() OVER(ORDER BY inspire_local DESC) AS OID
, inspire_local
, inspire_namespace
, label
, nationalcadref
, areavalue
, validfrom
, beginlifespanversion
, zoning
, geom::geometry(Polygon, 25833)
FROM cp_parcel_postgis
WHERE GeometryType(geom) = 'GEOMETRYCOLLECTION(POLYGON)'
I do not get any errors and the view is created. Unfortunately, the view - table is empty.
What am I doing wrong?
ST_Dump worked. However, I now have the problem that in my geom column of the view geometry types are polygon and point and my ID column (OID) doubles the digits. If I could delete the lines, the problem would be solved. But how do I delete lines in a view?
Unfortunately, the following creation of a rule did not succeed:
CREATE RULE point_delete AS ON DELETE TO parcel_polygons_view DO INSTEAD (
  DELETE FROM parcel_polygons_view WHERE GeometryType (geom) = 'POINT';
);