5

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"
            -overwrite
            -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.

8
  • 4
    Hint: the polygon has not been rotated, it has been reflected by interchanging the coordinates.
    – whuber
    Commented Mar 6, 2012 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
    Commented Mar 6, 2012 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
    Commented Mar 6, 2012 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
    Commented Mar 6, 2012 at 0:14
  • @MikeToews : GDAL 1.8.1, released 2011/07/09
    – Pure.Krome
    Commented Mar 6, 2012 at 1:53

1 Answer 1

1

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

<OGRVRTDataSource>
    <OGRVRTLayer name="swapped_boundaries">
        <SrcDataSource>MYSQL:xwing,user=uuu,password=ppp,host=localhost,port=3306,tables=GeographyBoundaries</SrcDataSource>
        <SrcSql>SELECT OriginalBoundary FROM GeographyBoundaries WHERE LocationId = 18561</SrcSql>
        <GeometryType>wkbPolygon</GeometryType>
        <GeometryField encoding="WKT" field="OriginalBoundary"/>
    </OGRVRTLayer>
</OGRVRTDataSource>

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 and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.