locked
Lat/Long values truncated when queried from geography point RRS feed

  • Question

  • I have a table of airport information. It has a column for lat and long, and I added a Geography data type column to store the point data for these lat/longs in order to easier find distance between two airports. what is weird is that my long/lat data is has 6 digits after the decimal. when I query the long/lat back out of the geograpy column, the long and lat is truncated down to either 3 or 4 digits.

    Here is the  code I used to populate the geography column:

      update table1
           set GeogPoint=geography::Parse('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + CAST([Latitude] AS VARCHAR(20)) + ')')

    Note: I also tried it using the geography::stPointFromText method with the same results

    I query the data back out with this script:

    select GeogPoint.Lat,
           latitude,
           GeogPoint.Long,
           longitude,
           *
      from  table 1

     

    the GeoPoint.Lat column is amost always truncated to 4 digits after the decimal, and the Long is random 3 to 5 digits.  I would like to get the exact value back out that went in to the geography column. Is there a modification to my update or query that will do this?

    thanks!

     


    :


     

    Thursday, December 9, 2010 5:31 AM

Answers

  • Have you tried populating the geogColumn as follows:

    update table1
    set GeogPoint=geography::Point(Latitude, Longitude, 4326);
    
    Also, are you displaying the results in SQL Server Management Studio, or in another client app? Are you sure that the values aren't just being truncated in the display?


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Thursday, December 9, 2010 8:15 AM
    Answerer

All replies

  • Hello,

    I can't reproduce this behavior. If I run the query below, I get 6 decimals. What datatype are you using to store longitude & latitude?

    DECLARE @long decimal(18, 6)

    DECLARE @lat decimal(18, 6);

     

    SET @long = 1.234567;

    SET @lat = 1.234567;

     

    DECLARE @geo geography

     

    SET @geo = geography::Parse('POINT(' + CAST(@long AS VARCHAR(20)) + ' ' + CAST(@lat AS VARCHAR(20)) + ')')

     

    SELECT @geo.Lat, @geo.Long

     


    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    Thursday, December 9, 2010 7:37 AM
  • Have you tried populating the geogColumn as follows:

    update table1
    set GeogPoint=geography::Point(Latitude, Longitude, 4326);
    
    Also, are you displaying the results in SQL Server Management Studio, or in another client app? Are you sure that the values aren't just being truncated in the display?


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Thursday, December 9, 2010 8:15 AM
    Answerer
  • The CAST function implicitly truncates the value.  You want to use the STR function instead of CAST.  Try

    update table1
           set GeogPoint=geography::Parse('POINT(' + STR(Longitude, 20, 16) + ' ' +STR(Latitude, 20, 16)+ ')')

     

    Tuesday, November 15, 2011 7:39 PM