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.

6
  • 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. Aug 4 '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 4 '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)? Aug 4 '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 4 '16 at 18:10
  • I think it is all polygons, based on the MapServer Layer definitions SQL queries.
    – CCantey
    Aug 4 '16 at 18:18
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))
   )
;
0

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.