none
SQL Server 2008 - Get Geography From Record

    Question


  • Hello,

    I am new to using the geography types in SQL Server 2008. I have a table in my database called "Location". "Location" has the following columns and types:

     

    Location
    --------
    City nvarchar (256)
    State nvarchar (256)
    PostalCode nvarchar(25)
    Latitude decimal(9, 6)
    Longitude decimal (9, 6)
    

     

    Each Location is related to a Store record in my database. I am trying to find the stores within a 10 mile radius or postal code or city/state that a user enters. To accomplish this, I know that I need to rely on geographies. At this time I have:

     

    DECLARE @startingPoint geography;
    SET @startingPoint=
     geography::STGeomFromText('POINT(-122.34900 47.65100)', 4326);
    
    
    

     

    That gives me the starting point from a hard-coded textual value. However, I do not know how to convert a lat/long from my Location table into a geography instance.

    How do I convert a lat/long in my database to a geography instance so I can continue to work on my query?

    Thank you!

    Wednesday, June 02, 2010 6:06 PM

Answers

  • First you need to add a new column to your table to store the geography data:

    ALTER TABLE Location
    ADD GeogColumn geography
    GO

    Then you need to populate the GeogColumn with points representing the values in the existing Latitude and Longitude columns:

    UPDATE Location
    SET GeogColumn = geography::Point(Latitude, Longitude, 4326);

    This assumes that your lat/long values are defined using the EPSG:4326 spatial reference system (which, if you're not sure, they probably are!)

     

     



    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by Alan Z Thursday, June 03, 2010 2:51 PM
    Thursday, June 03, 2010 10:28 AM

All replies

  • First you need to add a new column to your table to store the geography data:

    ALTER TABLE Location
    ADD GeogColumn geography
    GO

    Then you need to populate the GeogColumn with points representing the values in the existing Latitude and Longitude columns:

    UPDATE Location
    SET GeogColumn = geography::Point(Latitude, Longitude, 4326);

    This assumes that your lat/long values are defined using the EPSG:4326 spatial reference system (which, if you're not sure, they probably are!)

     

     



    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by Alan Z Thursday, June 03, 2010 2:51 PM
    Thursday, June 03, 2010 10:28 AM
  • Thank you very much for your help. This was very beneficial!
    Thursday, June 03, 2010 2:52 PM