I've got some GEOGRAPHY data in my Sql Server 2008. I wish to export it as a shapefile (so I can import it into QGIS) and when I do, it's rotated 90 degrees to the left and then flipped 180 degrees to the right.

Command line

C:\>ogr2ogr -f "ESRI Shapefile" "C:\temp\test.shp" "MSSQL:server=dbserver; database=xwing; uid=xwing; pwd=dbpwd"
            -sql "SELECT OriginalBoundary FROM Geography Boundaries where LocationId = 18561"
            -lco "SHPT=POLYGON"

Boundary is Sql Server 2008

enter image description here

Boundary loaded into QGIS after using Ogr2Ogr

enter image description here

/me sad panda.

  • 4
    Hint: the polygon has not been rotated, it has been reflected by interchanging the coordinates. – whuber Mar 6 '12 at 0:02
  • @whuber Cheers. Er.. that makes no sense to me because I have no background in GIS and similar tech. I've tried googling for 'what is a reflected polygon' and similar keywords, but I'm coming up blank. Can you provide some more information/links about this concept, please? – Pure.Krome Mar 6 '12 at 0:04
  • 3
    MS SQL Server Spatial used to reverse their geography coordinates as (Y X), then they revised this to (X Y). I wonder if this is a relic of those beta days? What do you get from ogr2ogr --version? – Mike T Mar 6 '12 at 0:06
  • 3
    Pure: GIS software (and all software dealing with spherical coordinates) is schizophrenic, because nobody can agree on whether (x,y) is (latitude, longitude) or (longitude, latitude). Mixing up perfectly good coordinates in this way has the effect of reflecting the image. You can confirm this by drawing the coordinate graticule on your second image: I bet it looks like it goes from 41.35 to 41.39 east longitude and 73.56 to 73.59 degrees south latitude. When you see this phenomenon, you know you will have to persuade either the producer or consumer of your data to switch coordinates! – whuber Mar 6 '12 at 0:14
  • @MikeToews : GDAL 1.8.1, released 2011/07/09 – Pure.Krome Mar 6 '12 at 1:53

Because the coordinates are swapped, it's convenient firstly to write an OGR VRT file (e.g. swapped_boundaries.vrt) wrapping the datasource:

    <OGRVRTLayer name="swapped_boundaries">
        <SrcSql>SELECT OriginalBoundary FROM GeographyBoundaries WHERE LocationId = 18561</SrcSql>
        <GeometryField encoding="WKT" field="OriginalBoundary"/>

and then swap the coordinates passing through SpatiaLite (GDAL >= 1.10.0 compiled with SQLite and SpatiaLite):

ogr2ogr adjusted_boundaries.shp swapped_boundaries.vrt -dialect sqlite -sql "SELECT SwapCoords(geometry) FROM swapped_boundaries"

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.