Viewing PostGIS spatial view in QGIS

Question

I am trying to reverse engineer a consultants work. They are using a postgis spatial view as a source layer in MapServer:

LAYER
            NAME 'Parcels2009'
            CONNECTIONTYPE postgis
            DATA "
                    parcel_geometry FROM (
                            SELECT id, year, parcel_working_title, appropriation_title, legal_citation, link_to_accomplishment_plan, project_type, acres, county, township, range, section, description, easement_ohf, fee_ohf, x, y, parcel_geometry
                            FROM map_view_final
                            WHERE year = '2009'
                    ) AS foo USING UNIQUE id USING srid=4326
            "

I can view the table in postgres:

And following this question I can add the layer into QGIS:

But the layer has no data/empty attribute table. If I add the layer with the "Spatial Type" set to 'NoGeometry' it will load the table just fine.

I suspect the issue is with my geometry column.

When I try to the following pgsql2shp command I get the following error:

pgsql2shp -f parcels -h localhost -u ####-P #### #### "SELECT * FROM map_view_final where parcel_geometry is not null"


Initializing...
Done (postgis major version: 2).
Output shape: Null Shape
Dumping: XUnknown WKB type (7) for record 0

I should note that I have similar issue trying to use ArcGIS to accomplish my task:

In the end I just need a shapefile to QC the data and for use in a separate project.

Answer 1

It appears that you have a mix of geometry types within each record. You will likely need to use the ST_CollectionExtract function to pull relevant features out of the GeometryCollections within your source table.

You could use the following to pull any polygons out of the table and store them in their own table.

CREATE TABLE map_polygons AS
SELECT id, year, parcel_working_title, appropriation_title, legal_citation, link_to_accomplishment_plan
     , project_type, acres, county, township, range, section, description, easement_ohf, fee_ohf, x, y
     , ST_CollectionExtract(parcel_geometry,3) as parcel_geometry
  FROM map_view_final
 WHERE year = '2009' 
   AND ( ST_CollectionExtract(parcel_geometry,3) IS NOT NULL
       AND NOT ST_IsEmpty(ST_CollectionExtract(parcel_geometry,3))
   )
;