Exporting Spatial Data From SQL Server to ESRI Shapefile

I love the site stats you get from WordPress. This morning I noticed that, in the 9 months that I’ve been writing this blog, nearly 200 people have come to my site after searching for the term “OGR2OGR” in a search engine. That’s more than any other single word search term. Around another 200 people have come here after searching for variations and combinations on this theme, including “OGR2OGR Sql Server”, “OGR2OGR MSSqlSpatial”, “OGR SQL”, “OGR Import shapefile” etc.

What I’m not sure, though, is how to interpret this statistic or best act upon it. Does it mean, for example, that my articles about OGR2OGR – the open source toolkit for spatial data conversion and manipulation – are helpful, or high quality? Sure, several of them get listed on the first page of results for a Google search, but does that simply mean that nobody else on the internet is writing about OGR2OGR? And do the people that come here searching for information about OGR2OGR actually find what they’re looking for, or do they end up leaving empty-handed?

I don’t know the answers to these questions, but since there are obviously some people out there looking for this kind of information (and since I know, from experience, that OGR2OGR can be an absolute bugger to get working correctly) here’s another post about OGR2OGR anyway… and this time it’s about exporting spatial data from SQL Server. There’s plenty of information on the ‘net describing how to import spatial data from ESRI shapefiles into SQL Server (including my own posts, such as here, here and here, for example). However, what I haven’t seen any examples of yet is how to do the reverse: taking geometry or geography data from SQL Server and dumping it into an ESRI shapefile.

There’s plenty of reasons why you might want to do this – despite its age and relative limitations (such as the maximum filesize per file, limited fieldname length, the fact that each file can contain only a single homogenous type of geometry, etc…), the ESRI shapefile format is still largely the industry standard and pretty much universally read by all spatial applications. Recently, I needed to export some data from SQL Server to shapefile format so that it could be rendered as a map in mapnik, for example (which, sadly, still can’t directly connect to a MS SQL Server database).

So, here’s a step-by-step guide, including the pitfalls to avoid along the way.

Setup

To start with, make sure you’ve got a copy of OGR2OGR version 1.8 or greater (earlier versions do not have the MSSQL driver installed). You can either build it from source supplied on the GDAL page or, for convenience, download and install pre-compiled windows binaries supplied as part of the OSgeo4W package.

Now, let’s set up some test data in a SQL Server table that we want to export. To test the full range of OGR2OGR features (or should that say, “the full range of error messages you can create”?), I’m going to create a table that contains two different geometry columns – an original geometry and a buffered geometry, populated using a range of different geometry types:

CREATE TABLE OGRExportTestTable (
  shapeid int identity(1,1),
  shapename varchar(32),
  shapegeom geometry,
  bufferedshape AS shapegeom.STBuffer(1),
  bufferedshapearea AS shapegeom.STBuffer(1).STArea()
);

INSERT INTO OGRExportTestTable (shapename, shapegeom) VALUES
('Point #1', geometry::STGeomFromText('POINT(13 10)', 2199)),
('Point #2', geometry::STGeomFromText('POINT(7 12)', 2199)),
('Line #1', geometry::STGeomFromText('LINESTRING(0 0, 8 4)', 2199)),
('Polygon #1', geometry::STGeomFromText('POLYGON((2 2, 4 2, 4 4, 2 4, 2 2))', 2199)),
('Line #2', geometry::STGeomFromText('LINESTRING(0 10, 10 10)', 2199));

Here’s what the contents of this table looks like in the SSMS Spatial Results tab:

image

Exporting from SQL Server to Shapefile with OGR2OGR (via a string of errors along the way)

The basic pattern for OGR2OGR usage is given at http://www.gdal.org/ogr2ogr.html, with additional usage options for the SQL Server driver at http://www.gdal.org/ogr/drv_mssqlspatial.html. So, let’s start by just trying out a basic example to export the entire OGRExportTestTable from my local SQLExpress instance to a shapefile at c:\temp\sqlexport.shp, as follows: (change the connection string to match your server/credentials as appropriate)

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport.shp"
"MSSQL:server=localhost\sqlexpress;database=tempdb;tables=OGRExportTestTable;trusted_connection=yes;"

This will fail with a couple of errors, but the first one to address is as follows:

ERROR 1: Attempt to write non-point (LINESTRING) geometry to point shapefile.

SQL Server will allow you to mix different types of geometry (Points, LineStrings, Polygons) within a single column of geometry or geography data. An ESRI shapefile, in contrast, can only contain a single homogenous type of geometry. To correct this, rather than trying to dump the entire table by specifying the tables=OGRExportTable in the connection string, we’ll have to manually select rows of only a certain type of geometry at a time by specifying an explicit SQL statement. Let’s start off by concentrating on the points only. This can be done with the –sql option, as follows:

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport.shp"
"MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;"
-sql "SELECT * FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = 'POINT'"

This time, a new error occurs:

ERROR 1: C:\temp\sqlexport.shp is not a directory.

ESRI Shapefile driver failed to create C:\temp\sqlexport.shp

Although our first attempt to export the entire table failed, it still created an output file at C:\temp\sqlexport.shp. Seeing as we didn’t specify the behaviour for what to do when the output file already exists, when we run OGR2OGR for the second time it has now errored. To correct this, we’ll add the –overwrite flag.

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport.shp"
"MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;"
-sql "SELECT * FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = 'POINT'"
-overwrite

Running again and the error is now:

ERROR 6: Can’t create fields of type Binary on shapefile layers.

Ok, so remember that our original shapefile had two geometry columns. When you create a shapefile, one column is used to populate the shape information itself, while every other column becomes an attribute of that shape, stored in the associated .dbf file. Since the remaining geometry column is a binary value, this can’t be stored in the .dbf file. To resolve this, rather than using a SELECT *, we’ll explicitly specify each column to be included in the shapefile. You could, if you want, then omit the second geometry column completely from the list of selected fields. Instead, I’ll use the ToString() method to convert it to WKT, which can then be stored as an attribute value:

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport.shp"
"MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;"
-sql "SELECT shapeid, shapename, shapegeom, bufferedshape.ToString(), bufferedshapearea  FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = 'POINT'"
-overwrite

This should now run without errors, although you’ll still get a warning:

Warning 6: Normalized/laundered field name: ‘bufferedshapearea’ to bufferedsh

The names of any attribute fields associated with a shapefile can only be up to a maximum of 10 characters in length. In this case, OGR2OGR has manually truncated the name of the buferedshapearea column for us, but you might not like to use the garbled “bufferedsh” as an attribute name in your shapefile. A better approach would be to specify an alias for long column names in the –sql statement itself. In this case, perhaps just “area” will suffice:

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport.shp"
"MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;"
-sql "SELECT shapeid, shapename, shapegeom, bufferedshape.ToString(), bufferedshapearea AS area  FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = 'POINT'"
-overwrite

And now, for the first time, you should be both error and warning free:

image

So, now, just repeat the above but substituting the POINT, LINESTRING, and POLYGON geometry types, and creating three separate corresponding shapefiles:

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport_point.shp" "MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;" -sql "SELECT shapeid, shapename, shapegeom, bufferedshape.ToString(), bufferedshapearea AS area  FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = 'POINT'" -overwrite

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport_linestring.shp" "MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;" -sql "SELECT shapeid, shapename, shapegeom, bufferedshape.ToString(), bufferedshapearea AS area  FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = 'LINESTRING'" -overwrite

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport_polygon.shp" "MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;" -sql "SELECT shapeid, shapename, shapegeom, bufferedshape.ToString(), bufferedshapearea AS area  FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = 'POLYGON'" -overwrite

All done, right? Let’s just have a quick check on the created files to make sure they look ok. You can do this using ogrinfo with the –al option, which will give you a summary of the elements contained in any spatial data set. I mean, I’m sure they’re fine and everything, but…. hang on a minute:

ogrinfo -al c:\temp\sqlexport_point.shp

Here’s the POINT shapefile:

image

Initially looks ok, the shapefile contains 2 Point features – they’ve got the correct attribute fields, but both points seem to have been incorrectly placed at coordinates of POINT(0.0 2.0). Huh?

What about the LINESTRING shapefile:

image

This is even worse – even though we specified that only LineString geometries should be returned from the SQL query, the created shapefile thinks it contains 2 Point features (Geometry: Point, near the top). And those points both lie at POINT (0.0 0.0)…

And the POLYGON shapefile:

image

Same problem as the LineString – it’s effectively an empty Point shapefile.

The problem seems to be that, even though we’re only returning geometries of a certain type from the SQL query, we haven’t explicitly stated that to the shapefile creator, so OGR2OGR is creating three empty shapefiles first (each set up to receive the default geometry type of POINT), and then trying to populate them with unmatching shape types, leading to corrupt data. To explicitly state the geometry type of the shapefiles created, we need to supply the SHPT layer creation option for each shapefile as specified at http://www.gdal.org/ogr/drv_shapefile.html, by adding –lco “SHPT=POLYGON”, –lco “SHPT=ARC” (for LineStrings) etc. as follows:

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport_point.shp"
"MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;"
-sql "SELECT shapeid, shapename, shapegeom, bufferedshape.ToString(), bufferedshapearea AS area  FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = 'POINT'"
-overwrite
-lco "SHPT=POINT"

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport_linestring.shp"
"MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;"
-sql "SELECT shapeid, shapename, shapegeom, bufferedshape.ToString(), bufferedshapearea AS area  FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = 'LINESTRING'"
-overwrite
-lco "SHPT=ARC"

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport_polygon.shp" "MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;"
-sql "SELECT shapeid, shapename, shapegeom, bufferedshape.ToString(), bufferedshapearea AS area  FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = 'POLYGON'"
-overwrite
-lco "SHPT=POLYGON"

Unfortunately, that brings us round to almost exactly the same error as we first started with when creating the LineString and Polygon shapefiles (Gah! I thought we’d got rid of them!):

ERROR 1: Attempt to write non-linestring (POINT) geometry to ARC type shapefile

ERROR 1: Attempt to write non-polygon (POINT) geometry to POLYGON type shapefile

We are only selecting geometries of the matching type for each shapefile by filtering the query based on STGeometryType(), so why does OGR2OGR think that we are selecting other types of geometries? What’s more, we haven’t yet explained why the point shapefile (which was, after all, being populated only with point geometries), incorrectly placed both points at coordinates POINT(0.0 2.0). It seems that something is corrupting the results of the SQL statement.

And here’s the “Ta-dah!” moment. According to http://www.gdal.org/ogr/drv_mssqlspatial.html, when retrieving spatial data from SQL Server, “The default [GeometryFormat] value is ‘native’, in this case the native SqlGeometry and SqlGeography serialization format is used”. However, this doesn’t actually appear to hold true. SQL Server stores geometry and geography data in a format very similar to, but slightly different from Well-Known Binary (WKB). The SQL Server binary values for the two points in the OGRExportTestTable are:

0x00000000010C0000000000002A400000000000002440
0x00000000010C0000000000001C400000000000002840

The Well-Known Binary of these two points is , instead, as follows:

0x01010000000000000000002A400000000000002440
0x01010000000000000000001C400000000000002840

As you can see, they’re very similar – the coordinate values are serialised as 8-byte floating point binary values in both cases, but the MSSQL Server native serialisation has a different and slightly longer (i.e. one byte more) header. Thus, if OGR2OGR is expecting to receive one type of data, but actually gets the other, all the bytes will be displaced slightly. This could explain both why OGR believed it was receiving the incorrect geometry types and also why the point coordinates were wrong.

To correct this, rather than retrieving the shapegeom value directly, use the STAsBinary() method in the SQL statement to retrieve the WKB of the shapegeom column instead.

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport_point.shp"
"MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;"
-sql "SELECT shapeid, shapename, shapegeom.STAsBinary(), bufferedshape.ToString(), bufferedshapearea AS area  FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = 'POINT'"
-overwrite
-lco "SHPT=POINT"

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport_linestring.shp"
"MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;"
-sql "SELECT shapeid, shapename, shapegeom.STAsBinary(), bufferedshape.ToString(), bufferedshapearea AS area  FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = 'LINESTRING'"
-overwrite
-lco "SHPT=ARC"

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport_polygon.shp" "MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;"
-sql "SELECT shapeid, shapename, shapegeom.STAsBinary(), bufferedshape.ToString(), bufferedshapearea AS area  FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = 'POLYGON'"
-overwrite
-lco "SHPT=POLYGON"

Right, we are definitely making progress now. Trying ogrinfo again reveals that all of the layers have the correct number of features, or the appropriate type, and all have the right coordinate values. Yay!

image

There’s just one nagging thing and that’s the Layer SRS WKT: (unknown). When we converted from the SQL Server native serialisation to Well-Known Binary, we lost the metadata of the spatial reference identifier (SRID) associated with each geometry. This information contains the details of the datum, coordinate reference system, prime meridian etc. that make the coordinates of each geometry relate to an actual place on the earth’s surface. In the shapefile format, this information is contained in a .PRJ file that usually accompanies each .SHP file but, since we haven’t supplied this information to OGR2OGR, none of the created shapefiles currently have associated .PRJ files, so ogrinfo reports the spatial reference system as “unknown”.

To create a PRJ file, we need to append the –a_srs option, supplying the same EPSG id as was supplied when creating the original geometry instances in the table. (Of course, if you have instances of more than one SRID in the same SQL Server table, you’ll have to split these out into separate shapefiles, just as you have to split different geometry types into separate shapefiles)

The Final Product

So here’s the final script that will separate out points, linestrings, and polygons from a SQL Server table into separate shapefiles, via the WKB format but maintaining the SRID of the original instance, and retaining all other columns as attribute values in the .dbf file. Tested and confirmed to work with columns of either the geometry or geography datatype in SQL Server 2008/R2, or SQL Server Denali CTP3.

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport_point.shp"
"MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;"
-sql "SELECT shapeid, shapename, shapegeom.STAsBinary(), bufferedshape.ToString(), bufferedshapearea AS area  FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = 'POINT'"
-overwrite
-lco "SHPT=POINT"
-a_srs "EPSG:2199"

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport_linestring.shp"
"MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;"
-sql "SELECT shapeid, shapename, shapegeom.STAsBinary(), bufferedshape.ToString(), bufferedshapearea AS area  FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = 'LINESTRING'"
-overwrite
-lco "SHPT=ARC"
-a_srs "EPSG:2199"

ogr2ogr -f "ESRI Shapefile" "C:\temp\sqlexport_polygon.shp"
"MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes;"
-sql "SELECT shapeid, shapename, shapegeom.STAsBinary(), bufferedshape.ToString(), bufferedshapearea AS area  FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = 'POLYGON'"
-overwrite
-lco "SHPT=POLYGON"
-a_srs "EPSG:2199"

And here’s the resulting shapefile layers loaded into qGIS:

image

This entry was posted in Spatial, SQL Server and tagged , , , . Bookmark the permalink.

41 Responses to Exporting Spatial Data From SQL Server to ESRI Shapefile

  1. Hi Alastaira,
    I did not came on this blog by searching for OGR related stuff, but because on Sql Server Spatial forums I saw you as the man with the spatial knowledge. Always clear, amazingly technical and precise.
    As I am developing around Sql Server spatial features, subscribing to your blog posts was natural. Keep on writing, I really like reading you.

  2. alastaira says:

    That’s very kind of you to say so, Xavier, and I’m glad you like reading my posts🙂
    If there’s any spatial topics you’d like to see me write about in the future, just let me know and I’ll see what I can do!

  3. Thanks for your proposition !
    Oh yeah, I’d like to know how to best organize spatial data storage in SQL Server.
    Imagine I have a table containing layers of France city boundaries. The boundaries change every quarter.
    On that data I perform simple querying (STIntersects), compute polylines (GeogUnionAggredate from SQLSpatialTools) and compute distances.
    How do I have to set up storage for best performance (all in on table, a table for each version, partitioning) ?
    How can I know how to tune Spatial indexing ?

    More generally, what are the best practices for spatial data storage and index tuning in SQL Server ?

    – more secondary
    – what are the best tools for working with sql server
    – without EntityFramework, what is the best way to consume and display geography types through WCF and / or web services (I do that with Silverlight, but I did it my way which may not be the best)

    … more ideas to come

  4. deeneon says:

    First of all:
    THANKS A LOT!

    This is exactly what I was looking for, even though I wasn’t searching for OGR stuff, I got here. I enjoyed this reading a lot, in fact I’ve read it 3 times already one after another. I’m taking your exercise to practice and let’s see what happens!

    Cheers and my best regards.
    David.

    • alastaira says:

      Hey – no problem – glad you liked it!
      Please let me know how you get on following these steps in practice, or if you encounter any issues along the way, and I’ll update the post.

  5. Justin A says:

    Another awesome post, Alastair🙂 I’ll be using this technique to double check my polys in sql server🙂🙂

    “If there’s any spatial topics you’d like to see me write about in the future, just let me know and I’ll see what I can do!” .. I’ve got one🙂 displaying polys on Google Maps and Bing Maps (I know you’re a bing maps specialist, but many of us use Gmaps still). This means retrieving them from Sql Server 2008 via .NET and then converting them into the appropriate format for the map. Oh, for bonus points … also have the option to encode the poly lines (http://code.google.com/apis/maps/documentation/utilities/polylineutility.html), on the server to reduce bandwidth from server to client🙂🙂

    Can I give you a polygon to play with as a test scenario? (read <— curve ball edge case .. *giggle*)
    🙂

  6. Charles Grayson says:

    Alastair,
    Well done. Rather than slashing my way through these issues, you made a nice highway from the other side!!
    Charles
    BTW– I just learned that ESRI has an “Arcview for Home Use Program.” The license is $100.00 per year. http://www.esri.com/arcgis-for-home/index.html

  7. lance says:

    First of all, your blog is a great resource. I can’t believe I’ve never come across it in the past.

    I’m running 2008 R2 and the latest build of Ogr2Ogr (from the OSGeo4w link you provide above). Cutting and pasting your table creation SQL, then running the final script gives me an error saying ERROR 1: GetNextRawFeature(): Corrupt data. for each geometry exported. I’ll get a valid dbf and prj file, but the actuall geometry is empty. Any ideas as to why this may be happening?

    • alastaira says:

      Hi Lance – thanks for the comments! Just a hunch, but has OGR2OGR created a geometry_columns table in your SQL Server database? If so, try deleting (or, at least, renaming) it, and try again. Does that help?

      • lance says:

        Thanks for replying! In answer to your question – no there is no geometry_columns table in the database. The only table in the test db (besides the system tables) is the table created using the SQL in your example. of course that table was populated by the SQL command as well and the SSMS Spatial Results tab looks exactly like the one in your example above.

      • alastaira says:

        Hmm. I just tried it with a blank database (SQL Server 2008 R2 Express) and OGR2OGR (v1.8.0) and it worked ok for me😦

  8. lance says:

    Darn, I just tried it on 2 new virtual W7 x64 machines with clean installs of SQLExpress 2008 R2 (10.50.1617) and OSGeo4W (1.8). Same errors. Very frustrating. Thanks for your earlier help, though!

    Lance

  9. Marek says:

    Hi,

    same as others.. Great Job! I have one issue with my case. I export to SHP table with 10 polygons, than import this SHP to other table using SQLSpatialTool Shape2SQL.

    Works but, longitude is swapped with latitide.

    Any advice here?
    Many thanks!

    Marek

    • alastaira says:

      Hi Marek,
      Are you using geometry or geography datatype? And can you give the OGR2OGR command you issued? There was an issue a long time ago about lat-long ordering in SQL Server, but that was in the CTPs of SQL Sever 2008, so shouldn’t have anything to do with it…

      • Marek says:

        Hi,

        thanks for quicke response. Here you are with more details:
        1. I use geography with SRID 4326 (these are real polygons described by GPS coordinates which I would like to present on a map later).
        2. Comman I use:
        C:\OSGeo4W\bin\ogr2ogr.exe -f “ESRI Shapefile” “C:\Users\marek\Desktop\gis\sqlexport_polygon.shp” “MSSQL:server=localhost\sqlexpress;database=Zones;trusted_connection=yes;” -sql “SELECT ZoneName, Zone FROM Zones WHERE Zone.STGeometryType() = ‘POLYGON'” -overwrite -lco “SHPT=POLYGON”
        3. I use Microsoft SQL Server 2008 R2 (SP1) – 10.50.2500.0 (Intel X86) (Build 7601: Service Pack 1).

        I have used some shapefile viewer to check if it is not shape to sql import issue, but it is not. This tool also shows lat-long swaped.

        Many thanks,
        Marek

      • Marek says:

        Hi,

        do you need some more details? I have reviewed my solution and cannot find any bug there. Should upload my database somewhere for you?

        Many thanks for support.
        Marek

      • Marek says:

        Hi,

        am still struggling with this issue. Tried to ad to SELECT Zone.STAsBinary() but this is causing an error during proceeding: ERROR 1: GetNextRawFeature(): Corrupt data.

        Count on your support.

        Many thanks.

  10. alastaira says:

    @Marek – I have no idea what’s happening, but I’ve tested this on several versions of SQL Server including SQL 2008 R2 and SQL 2012 and I can’t recreate what you’re describing. Using the method as described in the “Final Product” section of the blog article and then loading the resulting shapefile in QGIS gives me exactly the same result as if I’d looked at the data in SQL Server Management Studio. And that’s using geography Polygons (EPSG:4326) representing country outlines of the world.

  11. Alex Petrie says:

    Hi

    I am also getting the same error as Marek when I add STAsBinary(). I am running this on a geography datatype. Without STAsBinary(), it builds the shapefile OK, but it is defining it as a 3D polygon, and the polygon is turning a lat & lon into lat & lon & elevation:

    SCRIPT: (I’ve ***’d the credentials!)

    C:\>ogr2ogr -f “ESRI Shapefile” “C:\sfile\sqlexport_grid.shp” “MSSQL:server=GBRP
    OCSQLMAN03D.cfvd.***.com;database=od_engine;UID=***;PWD=***;”
    -sql “SELECT [shapeid], [shapename], [shapegeog], [shapegeog].STArea() as area
    FROM [dbo].[v_grid_sample]” -overwrite -lco “SHPT=POLYGON” -a_srs “EPSG:4326”
    layer names ignored in combination with -sql.

    RESULT:

    Layer name: sqlexport_grid
    Geometry: 3D Polygon
    Feature Count: 1
    Extent: (-6.453330, -6.453330) – (59.496000, 59.496000)
    Layer SRS WKT:
    GEOGCS[“GCS_WGS_1984”,
    DATUM[“WGS_1984”,
    SPHEROID[“WGS_84”,6378137,298.257223563]],
    PRIMEM[“Greenwich”,0],
    UNIT[“Degree”,0.017453292519943295]]
    shapeid: Integer (10.0)
    shapename: String (20.0)
    area: Real (24.15)
    OGRFeature(sqlexport_grid):0
    shapeid (Integer) = 3727250
    shapename (String) = 3727250
    area (Real) = 252189.571310520170000
    POLYGON ((59.4915 -6.45333 59.4915,-6.44445 59.496 -6.44445,59.496 -6.45333 59
    .4915,-6.45333 0.0 0,0 0 0))

    Any ideas?

    Thank you for such a great blog!

  12. alastaira says:

    @Alex, @Marek – What happens when you run this?:

    ogr2ogr -f “ESRI Shapefile” “C:\temp\sqlexport_test.shp” “MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes” -sql “SELECT ‘test’ AS id, geography::STGeomFromText(‘POLYGON((0 50, 2 50, 2 54, 0 54, 0 50))’, 4326).STAsBinary() AS shape;” -overwrite -lco “SHPT=POLYGON” -a_srs “EPSG:4326”

    ogrinfo -al “C:\temp\sqlexport_test.shp”

    • Alex Petrie says:

      It doesn’t seem to create the file, but doesn’t seem to state why? Here is the statement and immediate response:

      C:\temp>ogr2ogr -f “ESRI Shapefile” “C:\temp\sqlexport_test.shp” “MSSQL:server=localhost\sqlexpress;database=tempdb;trusted_connection=yes” -sql “SELECT ‘test’ AS id, geography::STGeomFromText(‘POLYGON((0 50, 2 50, 2
      54, 0 54, 0 50))’, 4326).STAsBinary() AS shape;” -overwrite -lco “SHPT=POLYGON”
      -a_srs “EPSG:4326” ogrinfo -al “C:\temp\sqlexport_test.shp”
      Usage: ogr2ogr [–help-general] [-skipfailures] [-append] [-update]
      [-select field_list] [-where restricted_where]
      [-progress] [-sql ] [-dialect dialect]
      [-preserve_fid] [-fid FID]
      [-spat xmin ymin xmax ymax]
      [-a_srs srs_def] [-t_srs srs_def] [-s_srs srs_def]
      [-f format_name] [-overwrite] [[-dsco NAME=VALUE] …]
      dst_datasource_name src_datasource_name
      [-lco NAME=VALUE] [-nln name] [-nlt type] [layer [layer …]]

      Advanced options :
      [-gt n]
      [-clipsrc [xmin ymin xmax ymax]|WKT|datasource|spat_extent]
      [-clipsrcsql sql_statement] [-clipsrclayer layer]
      [-clipsrcwhere expression]
      [-clipdst [xmin ymin xmax ymax]|WKT|datasource]
      [-clipdstsql sql_statement] [-clipdstlayer layer]
      [-clipdstwhere expression]
      [-wrapdateline]
      [[-simplify tolerance] | [-segmentize max_dist]]
      [-fieldTypeToString All|(type1[,type2]*)]
      [-splitlistfields] [-maxsubfields val]
      [-explodecollections] [-zfield field_name]

      Note: ogr2ogr –long-usage for full help.

      C:\temp>

      Thanks

  13. alastaira says:

    Do you have a server listening at localhost\sqlexpress? If not change the connection string.

  14. Alex Petrie says:

    Actually I did use a different connection string, which was valid…

  15. pmcxs says:

    Hi there,
    The flipped lat/lon is also happening to me with a geography (srid:4326) column, altough it works fine with the geometry type.

    Could this be related with the GDAL/OGR version? I’m currently using the latest one included with the OSGeo4W package. Alastair, do you know which version you’re using?

  16. Frank says:

    I am also getting rotated shapes with this command:

    ogr2ogr -f “ESRI Shapefile” test.shp “MSSQL:server=xxx;database=xxx;trust_connection=yes;” -sql “SELECT abbr, geog from map.USA” -overwrite -lco “SHPT=POLYGON” -a_srs “EPSG:4269”

    I should get a shapefile with the USA states in polygons. The shapes are recognizable, but the US looks like it’s hanging on a clothesline. Maine is still in the NE, but Florida is the NW corner, Washington in the SE, and CA in the SW. Not sure if that makes sense …

  17. Frank says:

    ogr2info -al z.shp starts with this:

    Layer name: z
    Geometry: Polygon
    Feature Count: 51
    Extent: (18.913703, -179.145119) – (71.379311, 179.776259)
    Layer SRS WKT:
    GEOGCS[“GCS_North_American_1983”,
    DATUM[“North_American_Datum_1983”,
    SPHEROID[“GRS_1980”,6378137,298.257222101]],
    PRIMEM[“Greenwich”,0],
    UNIT[“Degree”,0.017453292519943295]]
    abbr: String (2.0)

    Several of the states are MULTIPOLYGON collections, but most are POLYGON. An except below:

    OGRFeature(z):8
    abbr (String) = DC
    POLYGON ((38.933674001179902 -77.119488003915578,38.933299997821436 -77.119444003582231,38.921599 -77.110935,38.912514 -77.104499,38.912153 -77.103586,38.900487 -77.07403,38.899289 -77.070995,38.841979 -77.046308,38.841861 -77.046263,38.840913 -77.045908,38.813765 -77.035728,38.813516 -77.035545,38.80054 -77.026029,38.892234999366515 -76.910528999901018,38.968942 -77.007514,38.99451 -77.041018,38.933674001179902 -77.119488003915578))

    In SQL Server, this SELECT abbr, geog.STAsText() AS wkt FROM map.USA WHERE abbr = ‘DC’ yields this:

    abbr wkt
    DC POLYGON ((-77.119488003915578 38.9336740011799, -77.119444003582231 38.933299997821436, -77.110935 38.921599, -77.104499 38.912514, -77.103586 38.912153, -77.07403 38.900487, -77.070995 38.899289, -77.046308 38.841979, -77.046263 38.841861, -77.045908 38.840913, -77.035728 38.813765, -77.035545 38.813516, -77.026029 38.80054, -76.910528999901018 38.892234999366515, -77.007514 38.968942, -77.041018 38.99451, -77.119488003915578 38.9336740011799))

    So, the lat/lon is definitely reversed.

    Ideas?

  18. Frank says:

    One further note: I get the same results with both v1.7.3 and v1.9.1 of GDAL.

  19. Jon says:

    Hi Alastair,

    I’ve been reading your various posts on OGR2OGR and hopefully you can point me in the right direction with this!

    I’m trying to export from SQL Server to a Shapefile using FWTools. Unfortunately I keep getting the error message:
    “FAILURE: Unable to open datasource…”.

    My server settings are:
    Server – localhost (using SQL Server 2008 R2)
    DB – Geo
    Table – Postcode2000

    I’ve used the command you specified above as a starting point:
    ogr2ogr -f “ESRI Shapefile” “C:\test.shp”
    “MSSQL:server=localhost;database=Geo;tables=Postcode2000;trusted_connection=yes;”

    I’ve tried various different combinations of the command and have also tried passing the UID and PWD instead of using the Windows defaults. I have full admin permissions on the server so security shouldn’t be a problem.

    Any suggestions would be very welcome!!

  20. Richard says:

    Hi Alastair,

    Do you have experience going the other way? I’m trying to pump in JSON to a SQL Server Geometry Table.

    I’ve had success with getting the initial table populated but when I use the – append with no -lco options it raises
    below error…

    orginfo reports the correct SRID on the newly created table as EPSG:28355 as does Shape.STSrid
    adding -a_srs also does not help

    source JSON is here http://services.thelist.tas.gov.au/arcgis/rest/services/Public/CadastreParcels/MapServer/0/query?where=OBJECTID+%3E+0&text=&objectIds=&time=&geometry=&geometryType=esriGeometryEnvelope&inSR=&spatialRel=esriSpatialRelIntersects&relationParam=&outFields=*&returnGeometry=true&maxAllowableOffset=&geometryPrecision=&outSR=&returnIdsOnly=false&returnCountOnly=false&orderByFields=&groupByFieldsForStatistics=&outStatistics=&returnZ=false&returnM=false&gdbVersion=&returnDistinctValues=false&f=json

    ERROR 1: INSERT command for new feature failed. [Microsoft][ODBC SQL Server Driv
    er][SQL Server]A .NET Framework error occurred during execution of user-defined
    routine or aggregate “geometry”:
    System.FormatException: 24100: The spatial reference identifier (SRID) is not va
    lid. SRIDs must be between 0 and 999999.
    System.FormatException:
    at Microsoft.SqlServer.Types.GeometryValidator.ValidateSrid(Int32 srid)
    at Microsoft.SqlServer.Types.Validator.SetSrid(Int32 srid)
    at Microsoft.SqlServer.Types.ForwardingGeoDataSink.SetSrid(Int32 srid)

    ERROR 1: Terminating translation prematurely after failed

  21. tintu says:

    I am geeting error like this,not able to convert geometry table to shape file

    C:\Program Files (x86)\FWTools2.4.7>ogr2ogr -f “ESRI Shapefile” “C:\temp\sqlexpo
    rt_point.shp” “MSSQL:server=INDTVM0035D\MSSQLSERVER2008;database=test;trusted_co
    nnection=false;UID=sa;PWD=xxxx”-sql “SELECT shapeid, shapename, shapegeom.
    STAsBinary(), bufferedshape.ToString(), bufferedshapearea AS area FROM OGRExport
    TestTable WHERE shapegeom.STGeometryType() = ‘POINT'” -overwrite -lco “SHPT=POIN
    T” -a_srs “EPSG:4326”
    FAILURE:
    Unable to open datasource `MSSQL:server=INDTVM0035D\MSSQLSERVER2008;database=tes
    t;trusted_connection=false;UID=sa;PWD=xxxx-sql’ with the following drivers
    .
    -> ESRI Shapefile
    -> MapInfo File
    -> UK .NTF
    -> SDTS
    -> TIGER
    -> S57
    -> DGN
    -> VRT
    -> REC
    -> Memory
    -> BNA
    -> CSV
    -> NAS
    -> GML
    -> GPX
    -> KML
    -> GeoJSON
    -> Interlis 1
    -> Interlis 2
    -> GMT
    -> SQLite
    -> ODBC
    -> PGeo
    -> OGDI
    -> PostgreSQL
    -> MySQL
    -> XPlane
    -> AVCBin
    -> AVCE00
    -> DXF
    -> Geoconcept
    -> GeoRSS
    -> GPSTrackMaker
    -> VFK

  22. Mohan says:

    I am at the prompt: C:\Users\mkrishna\bin> and at the prompt, I enter: ogr2ogr –f “ESRI shapefile” “C:\temp\sqlexport.shp” “MSSQL:server=hbk-sql\mssqlserver2008; database=shapes; trusted_connection=yes;” -sql “Select Feeder_ID, Geom From Feeders”
    It says unable to open datasource ‘oESRI’ with the following drivers, and gives a list of drivers. Please advise what may be wrong. Thanks.

  23. Hi alastaira
    Thanks a lot for such a nice blog and it is exactly what I need to do. I have tried your methods step by step and i even got my shape files generated with some error as given in the beginning of your blog. I have been able to get around the errors the way you have described.

    Still there is something unexpected I have come across, which is as given below:
    COMMAND USED:
    C:\Program Files\QGISDufour>ogr2ogr -overwrite -f “ESRI Shapefile” “D:\Abhinav-Task\temp\sqlexport.shp” ”
    MSSQL:server=WIN12;database=MAPS;tables=dbo.OGRExportTestTable;UID=RD; PWD=test1
    ;”-sql “SELECT shapeid, shapename, shapegeom, bufferedshape.ToString(), bufferedshapearea area FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = ‘POINT’”

    ERROR MESSAGE:
    FAILURE: Couldn’t fetch requested layer ‘SELECT shapeid, shapename, shapegeom, bufferedshape.ToString(),
    bufferedshapearea area FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = ‘POINT”!

    I don’t know why this has come up and to check the sql query, I have even run it in SQL MANAGEMENT STUDIO itself and everything is fine. But still somehow it throws up the above error in command line of OSGEO4W.

    Will you please tell me how to get around this one??

  24. C:\Program Files\QGISDufour>ogr2ogr -overwrite -f “ESRI Shapefile” “D:\Abhinav-Task\temp\sqlexport.shp” “MSSQL:server=WIN-UD9U8OMK9BS;database=MAPSPOC_RD;tables=dbo.OGRExportTestTable;UID=POC_RD; PWD=Pass@word1;” -sql “SELECT shapeid, shapename, shapegeom, bufferedshape.ToString(), bufferedshapearea area FROM OGRExportTestTable WHERE shapegeom.STGeometryType()=’POINT’ ”

    The above that I have used does not miss any quotes. I guess it must be something else.

    ERROR MESSAGE:
    FAILURE: Couldn’t fetch requested layer ‘SELECT shapeid, shapename, shapegeom, bufferedshape.ToString(),
    bufferedshapearea area FROM OGRExportTestTable WHERE shapegeom.STGeometryType() = ‘POINT’ ‘!

    The error message is also giving all the quotes that I have used, then why is it still giving an error??

  25. Eric Kaufman says:

    Just to leave this here, there was an issue on ogr2ogr for a while related to reading binary; WKB was being read as varbinary. It’s (hopefully) fixed in the newest release. http://trac.osgeo.org/gdal/ticket/5498 . If you’re having the “Error 6” issue, try to check on that.

  26. Steve says:

    Hi Alastaira,

    Thank you for a great book on SQL Server Spatial, it really helped me get started with the GIS project at work!

    Now there’s one topic that was never really discussed in the book or anywhere else online. When we import shapefiles into SQL Server with geographic data type, SQL Server expects polygons to be re-oriented to the left-hand rule and I’ve applied that function and it’s all good. When it comes time to export those polygons into shapefiles or GeoJson, I seem to be getting those polygons tilted by 90 degrees and flipped. I tried looking to see if there’s a built-in function in SQL Server to reverse that but to no avail. Do you have any recommendations for this problem?

    Thank you very much!
    Steve

  27. StevieS says:

    Great blog!

    I’ve also had issues with transposed or flipped Lat/Long values my shapefile outputs when exporting from a SQL Server table which has a geography type.

    The query was something like this…
    ogr2ogr –config -f “ESRI Shapefile” “C:\OutputDir” “MSSQL:server=localhost;database=GM3DB;trusted_connection=yes;” -nln “outputShapefileName” -sql “SELECT id, geog FROM tbl_shapes” -overwrite -lco “SHPT=POLYGON” -a_srs “EPSG:4230”

    No matter what I tried I couldn’t seem to force the ogr2ogr command to export the GEOGRAPHY type correctly.

    My solution was to concede defeat and change my sql queries to always return GEOMETRY types rather than GEOGRAPHY, i.e.

    SELECT id, geometry::Point(geog.long, geog.lat), 4230) AS shape FROM tbl_shapes

    Not ideal but at least it’s working for me! Happy to hear any alternative suggestions🙂

  28. thisisbinod says:

    I came across this helpful post today and wanted to share my experience with the same rotated/transposed shape issue as described by other users. I noticed that if you use table or view instead of SQL then the shape file created is normal. For example
    This will work fine. (Notice tables in connection string)
    ogr2ogr -f “ESRI Shapefile” “c:\temp\test.shp” “MSSQL:server=AXIO-BA2013\BASQL;database=TEST1;tables=vw_testspatial;trusted_connection=yes;” -overwrite -a_srs “EPSG:4326”
    If you use -sql “SELECT * from testspatial” then this will create rotated shape file. As a temporary solution you can wrap your SQL query as a view and use it like a table instead.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s