locked
Using OGR2OGR to reproject within a SQL table RRS feed

  • Question

  • Hi guys,

    In the past (before OGR2OGR had a SQL Server Spatial driver), I've had some success with OGR2OGR, but I find it a bit tricky to use with the SQL Server spatial driver.

    I had a file, which contained points using the National Grid system (EPSG:27700?), so they're expressed as eastings / northings. I loaded the file into a table with a computed column (SourceGeometry) with the following formula :

    ([geometry]::STGeomFromText(((('POINT('+CONVERT([char](7),[Eastings],(0)))+' ')+CONVERT([char](7),[Northings],(0)))+')',(0)))

    and it all seems to work fine. Now however, I want to update a column in that table which will store the point as a WGS84 coordinate system (EPSG:4326?)

    So my Table conceptually looks like this:

    OSAPR (char), SourceGeometry (geometry), SourceGeography(geography)

    I've previously done this in a variety of really ugly ways using a combination of GridInQuest, Shape2SQL and loads of intermadiate text files. Now however, I feel that it must be possible to run an OGR2OGR command, selecting the geometry field, convert it to EPSG:4326 geography, and pump it back into the geography field in the table (or even another table). However, I'm really struggling go work out what teh syntax should be when SQL Server is the source, and the destination.

    Has anyone done this SQL --> OGR2OGR --> SQL method before, and can give me an example of the syntax? Many thanks.

    Sam

    Wednesday, November 7, 2012 12:20 PM

Answers

All replies

  • I've got a page on my blog that gives various examples of using SQL Server as both a source and destination of an OGR2OGR transformation - http://alastaira.wordpress.com/ogr2ogr-patterns-for-sql-server/

    I'm not sure about reprojecting/populating a column of the same table, but it should be relatively straightforward to populate a new table in the same database based on the patterns there. Let me know if you have any problems.


    twitter: @alastaira blog: http://alastaira.wordpress.com/ | Pro Spatial with SQL Server 2012

    • Marked as answer by Sam Loud Wednesday, November 7, 2012 1:58 PM
    • Unmarked as answer by Sam Loud Wednesday, November 7, 2012 1:58 PM
    • Marked as answer by Sam Loud Wednesday, November 7, 2012 1:58 PM
    • Unmarked as answer by Sam Loud Wednesday, November 7, 2012 1:58 PM
    • Marked as answer by Sam Loud Wednesday, November 7, 2012 2:36 PM
    Wednesday, November 7, 2012 1:44 PM
    Answerer
  • Excuse my pratting round marking / unmarking as answer - I'm not very good with computers. I haven't come across your 'patterns.' post before. Let me check it out and have an other run at it.

    As always, thanks for your valuable help.

    Sam

    Wednesday, November 7, 2012 2:00 PM
  • OK, that worked. The 'ogr2ogr patterns..' post was predictably excellent, and solved my problem in short order.

    In case anyone woudl find it easier to adapt the command I used, rather than starting from scratch, here it is:

    ogr2ogr

    -f "MSSQLSpatial" **what format are we writing the data to?

    "MSSQL:server=.\DENALI;database=spatial;trusted_connection=yes;"  **where is the destination database?

    "MSSQL:server=.\DENALI;database=Staging;trusted_connection=yes;"  **where are we getting the data from?

    -sql "SELECT TOP 100 OSAPR, LocationGeometry.STAsBinary()   FROM WRK_AddressPoint" **What SQL do we want to run to pull the data?

    -s_srs "EPSG:27700" **what is the source co-ordinate system?

    -t_srs "EPSG:4326" **what is the destination co-ordinate system?

    -overwrite **flatten the destination table and reload

    -lco "GEOM_TYPE=geography" ** destination field is geography, not geometry

    -lco "GEOM_NAME=LocationGeography" destination field is called 'LocationGeography'

    -nln "DEST" ** destination table is called 'DEST'

    Here it is uncommented:

    ogr2ogr -f "MSSQLSpatial" "MSSQL:server=.\DENALI;database=spatial;trusted_connection=yes;"  "MSSQL:server=.\DENALI;database=Staging;trusted_connection=yes;" -sql "SELECT TOP 100 OSAPR, LocationGeometry.STAsBinary()  FROM WRK_AddressPoint" -s_srs "EPSG:27700" -t_srs "EPSG:4326" -overwrite -lco "GEOM_TYPE=geography" -lco "GEOM_NAME=LocationGeography" -nln "DEST"

    Thanks for you help tanoshimi.


    • Edited by Sam Loud Wednesday, November 7, 2012 3:57 PM
    Wednesday, November 7, 2012 2:31 PM