Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
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
show 3 more comments

1 Answer 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"
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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