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

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.

| improve this answer | |
  • Thanks very much. It works. This fast help is amazing. I love the communtity :) – oliver.f Dec 14 '17 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'
| improve this answer | |

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.