locked
Choosing data type for GPS data RRS feed

  • Question

  • Hi,

    I have a table that receives GPS data regularly. Simply it has X and Y columns and I chose float data type for these columns. When creating linestring from points of this table, truncation occurs when casting to varchar. But when I cast them first to decimal, then to varchar, truncation does not occur. Is it recommended to change my X and Y column to decimal for table in order to skip casting to decimal first?

    I ask this because in all samples I have seen float type is used for GPS data.

    Thanks in advance,

    Leila

     

    Wednesday, February 2, 2011 8:57 PM

Answers

  • Either one will do, its just that the spatial data types (the subject of this list) and most devices (like you've mentioned for GPS devices) use float. The usage of float does cause some issues with rounding, however. Here's what SQL Server books online says about using float vs decimal http://msdn.microsoft.com/en-us/library/ms187912.aspx.

    Hope this helps, Cheers, Bob

    • Proposed as answer by WeiLin Qiao Monday, February 7, 2011 5:42 AM
    • Marked as answer by Leila S Monday, February 7, 2011 5:03 PM
    Saturday, February 5, 2011 5:33 PM

All replies

  • Could you post a small example of the code you're using that produces the truncation problem?

    Cheers, Bob Beauchemin, SQLskills

    Thursday, February 3, 2011 7:30 PM
  • Hi Bob, Glad to see you here! declare @t table(x float, y float) insert @t values(20.3454643309,20.4645436456) select CAST(x as varchar(100)),CAST(CAST(x as decimal(20,15)) as varchar(100)) from @t Thanks
    Friday, February 4, 2011 8:32 AM
  • oops! CR-LF removed from my code!
    Friday, February 4, 2011 8:54 AM
  • Hi Leilas,

    Float is used in spatial data in general because that's the data type that the spatial specs use. In SQL Server, you can use CAST or CONVERT to convert between data types. CONVERT gives you three styles of conversion, CAST uses the default style. For conversion from FLOAT with the precision that you want, use CONVERT(varchar(100), x, 2) where 2 is the style that specifies 16 digits of precision (there's a choice of 6, 8, or 16 digits). Using CAST gives you the default style, which amounts to 6 digits of precision, and produces the truncation effect you're seeing. See http://msdn.microsoft.com/en-us/library/ms187928.aspx in the float and real styles section.

    Cheers, Bob

    Saturday, February 5, 2011 12:40 AM
  • Thanks indeed,

    So you prefer to use float as column type instead of decimal?

    Saturday, February 5, 2011 4:28 AM
  • Either one will do, its just that the spatial data types (the subject of this list) and most devices (like you've mentioned for GPS devices) use float. The usage of float does cause some issues with rounding, however. Here's what SQL Server books online says about using float vs decimal http://msdn.microsoft.com/en-us/library/ms187912.aspx.

    Hope this helps, Cheers, Bob

    • Proposed as answer by WeiLin Qiao Monday, February 7, 2011 5:42 AM
    • Marked as answer by Leila S Monday, February 7, 2011 5:03 PM
    Saturday, February 5, 2011 5:33 PM
  • Note that, assuming you're creating a geography point for each of your GPS readings, you don't also need to keep numeric columns to record the separate lat/long coordinate values - you can retrieve these from each point instance using:

    SELECT
     geogCol.Lat,
     geogCol.Long
    FROM GPSLogTable
    


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Sunday, February 6, 2011 11:44 AM
    Answerer