5

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';
);

2 Answers 2

2

Since st_dump is a set returning function I think you need to make it a sub-query to filter by geometry type. To get points you would just change the where clause to = 'ST_Point'.

CREATE VIEW parcel_polygons AS (

    SELECT
        polys_and_points.*
    FROM
        (SELECT
            row_number() OVER(ORDER BY inspire_local DESC) AS OID
            , inspire_local
            , inspire_namespace
            , label
            , nationalcadref
            , areavalue
            , validfrom
            , beginlifespanversion
            , zoning
            , (ST_dump(geom)).geom
        FROM cp_parcel_postgis) as polys_and_points
    WHERE
        st_geometrytype(polys_and_points.geom) = 'ST_Polygon'
)

I feel like there should be a cleaner way to write this, but should work as is.

1
  • Thanks very much. It works. This fast help is amazing. I love the communtity :) Commented Dec 14, 2017 at 15:13
2

To "explode" the collection into individual geometries, you need to use ST_dump. Also I believe the geometry type will only be GEOMETRYCOLLECTION

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
,  (st_dump(geom)).geom as geom
FROM cp_parcel_postgis
WHERE GeometryType(geom) = 'GEOMETRYCOLLECTION'
0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.