locked
Geography data type and SQL Server Management Studio RRS feed

  • Question

  • I am trying to set-up a spreadsheet that I can use to load data into SQL Server 2008.  I have spatial data that would I would like entered as 'POINT(-117.85, 34.2, 97)' or even geography::STPointFromText('POINT(-117.85,24.2,97)',4326) in the Excel spreasheet.  Then the data can be pasted into SQL Server Management Studio when doing a row edit.  This doesn't work! 

    I do want to keep this as simple as possible, since eventually other people are going to be entering the data (and hopefully loading it themselves).  I am wondering if I should just make lat, long, elevation columns and then convert to geography data type once it is loaded.  My only problem is if they start having polygon files, but I'm hoping then they would be loading a shape file.

     

    TIA!

    Thursday, November 4, 2010 7:44 PM

Answers

  • To start with, you've got the WKT syntax incorrect... coordinates need to be space separated, not comma.

    So, try storing your Excel data as:

    POINT(-117.85 24.2 97)

    (No point storing the "geography::STPointFromText" and the "4326" bit, assuming all your coordinates are going to be measured using WGS84)

    Import this column as an nvarchar(max) column into SQL Server. Then, you can SELECT it using

    SELECT geography::STPointFromText(NameofWKTcolumn, 4326)


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Proposed as answer by Olaf HelperMVP Friday, November 5, 2010 8:01 AM
    • Marked as answer by akamy Friday, November 5, 2010 5:27 PM
    Thursday, November 4, 2010 8:52 PM
    Answerer

All replies

  • To start with, you've got the WKT syntax incorrect... coordinates need to be space separated, not comma.

    So, try storing your Excel data as:

    POINT(-117.85 24.2 97)

    (No point storing the "geography::STPointFromText" and the "4326" bit, assuming all your coordinates are going to be measured using WGS84)

    Import this column as an nvarchar(max) column into SQL Server. Then, you can SELECT it using

    SELECT geography::STPointFromText(NameofWKTcolumn, 4326)


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Proposed as answer by Olaf HelperMVP Friday, November 5, 2010 8:01 AM
    • Marked as answer by akamy Friday, November 5, 2010 5:27 PM
    Thursday, November 4, 2010 8:52 PM
    Answerer
  • That's what I get for trying to remember the syntax and not checking it before posting, but your answer is great! 

     

    Thanks,

    Amy

     

    Thursday, November 4, 2010 9:19 PM
  • I did use tanoshimi's answer, but I'm not just dealing with POINT, but also POLYGON, LINESTRING, etc.

     

    So I made this modification:

     

    SELECT geography::STGeomFromText( NameofWKTcolumn , 4326)


    This is also the reason that I didn't have Lat,Long,Elev columns in the Excel spreadsheet and then use the geography data type when creating the view.

     

    Friday, November 5, 2010 5:27 PM