I am trying to convert SQL Shapes to ESRI Shapefiles using the instructions in this blog post from a few years back and OSGeo4W.


I'm most of the way there, but I'm losing a shape here and there on account of some of them being GeometryCollection shape types. Is it possible to convert GeometryCollections to Polygons on the fly and get all of these shapes out in the same shapefile at once? Or should I just extract them separately and match them up later?

Not sure it's necessary, but this is the command I'm using in the OSGeo4W Shell

ogr2ogr -f "ESRI Shapefile" "C:\FolderName\AllShapes.shp" 
trusted_connection=yes;" -sql "Select ShapeID, ShapeTypeID as ShapeType, 
ShapeData From .dbo.Shapes Where ShapeData.STGeometryType() In 

That command works if I dump out 'geometrycollection', but, as I mentioned, I'm then missing a bunch of shapes.

  • if you happen to use or know how to use PostGIS then this function will help you out postgis.net/docs/ST_CollectionExtract.html
    – ziggy
    Aug 31 '17 at 17:28
  • @ziggy I think he's just using MSSSQL Server as the data source... Aug 31 '17 at 18:10
  • 1
    Have you tried adding the tables to QGIS and exporting them as shapefiles using the simple right-click > save as tool? Aug 31 '17 at 18:10
  • Yes, but for whatever reason, I can't get that to work for most of the shapes (possibly because of the same GeometryCollections concerns?). A bunch come out, but not everything. I actually was able to sort this out, but in my experience accessing SQL shapes through both Arc and Q GIS does not work, or at least not completely.
    – HenryClay
    Aug 31 '17 at 18:28

You can't add a geometry collection to a shapefile. A geometry collection can contain anything (line, point, polygons, multi*) while shapefiles can only be a single type (multipolygon in your case).

You will need to extract the geometry collections and handle them separately - if they contain (multi)polygons then you can add them to the shapefile.

  • Thanks! That set me on the right path to pull just the polygons out of those GeometryCollections into a separate table, which I then used the same command that I initially posted on. The same blog I'd referenced initially actually had steps for doing this in a separate post. alastaira.wordpress.com/2011/01/21/…
    – HenryClay
    Aug 31 '17 at 18:24

There seems to be no ST_CollectionExtract() on Microsoft SQL Server. I was able to accomplish roughly the same result, however, by using STBuffer(). YMMV, but I tried just about every other function that seemed remotely applicable, like BufferWithTolerance(), EnvelopeAggregate(), and UnionAggregate().

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.