[postgis-users] HELP - Geometrycollection - CREATE VIEW - Empty Table

[Tõnis Kärdi]

That's good you got that resolved :)

Cheerio,
Tõnis
________________________________________
Saatja: postgis-users <postgis-users-bounces at lists.osgeo.org> ofoerster at posteo.de <ofoerster at posteo.de> nimel
Saadetud: 14. detsember 2017 17:24
Adressaat: PostGIS Users Discussion
Teema: Re: [postgis-users] HELP - Geometrycollection - CREATE VIEW - Empty      Table

Hi Tõnis,

thanks for the quick answer. A friend solved the problem with this
function:

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

best regards,
Oli

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------


Am 14.12.2017 14:11 schrieb Tõnis Kärdi:
> Hi Oli,
>
> Have you checked the distinct values you are getting for geometrytype?
> Something in the lines of
>
> select distinct geometrytype(geom) from cp_parcel_postgis;
>
> Is 'GEOMETRYCOLLECTION(POLYGON)' listed there?
>
> I think the geometrytype of a geometrycollection is simply
> 'GEOMETRYCOLLECTION'. So most probably the filter you are using
> filters everything out.
>
> All the best,
> Tõnis
>
> ________________________________________
> Saatja: postgis-users <postgis-users-bounces at lists.osgeo.org>
> ofoerster at posteo.de <ofoerster at posteo.de> nimel
> Saadetud: 14. detsember 2017 14:30
> Adressaat: postgis-users at lists.osgeo.org
> Teema: [postgis-users] HELP - Geometrycollection - CREATE VIEW - Empty
> Table
>
> Hello PostGIS Community,
>
> I have a Problem. 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
> is empty.
> What am I doing wrong?
>
> Thanks for any help,
> Oli
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users