locked
How To Get Lat-Long From Geography Data Type RRS feed

  • Question

  • I am new to SQL 2008 and it's new data types. I have need to store latitude and longitude for locations, which I have previously done using two fields of float data type. However, I'm designing the next version of our database and would like to convert over to the new geography data type.

    I have found an article here on how to convert and store the latitude and longitude data that I have as geography, but if I'm going to do this, I will need to know how to extract latitude and longitude from the geography data type value for future reference. How do I do this? For example, say I have a column called "GeoLoc" as a geography data type. The article above says I do this to put the convert and store the latitude and longitude in the field.

    UPDATE	Table1
    SET	[GeoLoc] = geography::Parse('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + 
                        CAST([Latitude] AS VARCHAR(20)) + ')')
    How do I then select it back out as a latitude and longitude? Something like this?

    Select	GeoLoc.Latitude,
    	GeoLoc.Longitude
    From	Table1

    Nathon Dalton .NET Software Developer
    Monday, December 14, 2009 7:47 PM

Answers

  • Something like this?
    Select
    	GeoLoc.Latitude,
    	GeoLoc.Longitude
    From
    	Table1
    

    Nathon Dalton .NET Software Developer

    Very close.... something like this:
    SELECT
      GeoLoc.Lat,
      GeoLoc.Long
    FROM
      Table1


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by Nathon Dalton Monday, December 14, 2009 9:13 PM
    Monday, December 14, 2009 8:47 PM
    Answerer

All replies

  • Something like this?
    Select
    	GeoLoc.Latitude,
    	GeoLoc.Longitude
    From
    	Table1
    

    Nathon Dalton .NET Software Developer

    Very close.... something like this:
    SELECT
      GeoLoc.Lat,
      GeoLoc.Long
    FROM
      Table1


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by Nathon Dalton Monday, December 14, 2009 9:13 PM
    Monday, December 14, 2009 8:47 PM
    Answerer
  • Oh, YOU ROCK!! Thanks, that's exactly what I needed! Hey, while I've got someone here who knows what they're doing. I understand the new geospatial references to be a combination of the following coordinates.

    Coordinates:
    XYZM

    Where
    X = Longitude
    Y = Latitude
    Z = Altitude
    M = Measure (?)

    What is Measure? Is that how large the radius (well, spherical radius) of the geospatial point is?
    Nathon Dalton .NET Software Developer
    Monday, December 14, 2009 9:17 PM
  • No problem :)

    If you're happy that X and Y represent the position of a point in two dimensional space, and the Z coordinate adds the ability to describe a point in three-dimensional space (i.e. altititude), then the M coordinate represents any "fourth dimension" that you want to associate with each point.

    You don't have to use it (you only need X/Y, or Lat/Lon), but if you do, some common applications are to store a time measure at which that coordinate point was recorded, or you could store the M coordinate of each point along a linestring to represent the distance of that point from the start of the linestring.

    You can store and retrieve Z and M values, but SQL Server doesn't (currently) do any operations involving them so you can use them to store any value you want that can be represented as a floating point number.
    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Monday, December 14, 2009 9:56 PM
    Answerer
  • Great! Thank you very much for that great explanation! That is exactly the information I was looking for. I appreciate your time and effort in answering my questions and hope you have a great holiday and new year!
    Nathon Dalton .NET Software Developer
    Monday, December 14, 2009 10:04 PM