3

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:

enter image description here

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

enter image description here

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:

enter image description here

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

Community
  • 1
CCantey
  • 1,468
  • 1
  • 16
  • 20
  • Could you try this? SELECT ST_GeometryType(parcel_geometry), count(*) FROM map_view_final GROUP BY ST_GeometryType(parcel_geometry); Are they all polygons or are there some geometrycollections in there? Also, what version of PostGIS are you running? 1.x or 2.x? You can execute select PostGIS_full_version(); to details on your setup if you're unsure. – John Reiser Aug 04 '16 at 17:39
  • When I run this, it returns a 'null' record for 666, and ST GeometryCollection for 1667... I'm running 2.1.3 – CCantey Aug 04 '16 at 18:06
  • It appears then that you have 666 empty geometries and 1667 geometry collections. Are the features within this table supposed to be one type (polygon, point, etc)? – John Reiser Aug 04 '16 at 18:09
  • I am concerned with the polygons - but looking at the webmap, maybe there are points. Do you know of a way to parse the datatypes out? I don't see any attributes in the table that might give this away. – CCantey Aug 04 '16 at 18:10
  • I think it is all polygons, based on the MapServer Layer definitions SQL queries. – CCantey Aug 04 '16 at 18:18
  • Please see the answer below - it has sample code you can use to extract polygons. – John Reiser Aug 04 '16 at 18:21

1 Answers1

8

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))
   )
;
John Reiser
  • 1,136
  • 8
  • 14