locked
Can't Format POINT Text correctly RRS feed

  • Question

  • Dear sirs,

    I'm receiving GPS data from a tracker, and need to convert Lat,Lng strings to a geography type. 

    Data received:

    $GPRMC,203332.00,A,4738.33798,N,12221.78057,W,1.051,22.32,040811,,,A*46

    So basically I've got 4738.33798N, 12221.78057W, how do I best convert this for a insert statement:

    This fails with a PrasePointText message:
    insert into Measurement values(geography::STPointFromText('POINT(12221.78057W 4738.33798N)',4326));

    Regtards, Duncan

    Monday, November 12, 2012 12:34 PM

Answers

  • Ah, ok - so they are WGS84 coordinates, it's just that they're formatted as degrees and decimal minutes, rather than decimal degrees (i.e. think of a coordinate being stated as 1¾ rather than 1.75).

    12129.4432E means a longitude of 121 degrees, 29.4432 minutes. To convert this to decimal degrees as expected by SQL Server, divide the minutes by 60 and add on to the degrees, to get  121.49072.

    Do the same with the latitude coordinate of 2502.9589 to get 25.049315.

    So, your code should be:

    INSERT into Measurement values(geography::Point(25.049315,121.49072,4326));

    which is a point near Zhōngxing bridge in Taiwan. Does that sound right?


    twitter: @alastaira blog: http://alastaira.wordpress.com/ | Pro Spatial with SQL Server 2012

    • Proposed as answer by Naomi N Tuesday, November 13, 2012 6:57 PM
    • Marked as answer by Shulei Chen Monday, November 19, 2012 9:18 AM
    Tuesday, November 13, 2012 7:44 AM
    Answerer

All replies

  • Those coordinates aren't WGS84 coordinates, so you don't want to use SRID 4326. Have you got your GPS unit set to UTM or some other coordinate reference system?

    Once you know what the correct SRID is, you can insert the values using the Point() method of the geometry datatype - no need to construct WKT. Just pass the x value as one parameter, and the y value as the other. "W" values are negative, "E" are positive. Same goes for "S" negative and "N" positive.

    So, for the values given, you'd use:

    INSERT into Measurement values(geometry::Point(-12221.78057, 4738.33798, 0));


    twitter: @alastaira blog: http://alastaira.wordpress.com/ | Pro Spatial with SQL Server 2012

    • Proposed as answer by Naomi N Tuesday, November 13, 2012 6:56 PM
    Monday, November 12, 2012 2:42 PM
    Answerer
  • Thanks Tanoshimi,

    Newbe at this, and understand that srid set's the coordinate system, no problem with changing to negative values, but find it hard to understand what SRID to use. My GPS tracker's usermanual spesifies:

    All recorded files are sorted by GMT+0 time (see attached screen shot). The data is
    in NMEA-0183 format and includes GPRMC and GPGGA. Below are examples of
    recorded data.
    $GPRMC,174806.000,A,2502.9589,N,12129.4432,E,17.8,21.3,150808,,,A*68
    $GPGGA,174806.000,2502.9589,N,12129.4432,E,1,04,6.3,93.9,M,26.0,M,0000*66

    So basically need to find the coresponding SRID for the NMEA standard

    Duncan

    Monday, November 12, 2012 5:12 PM
  • Ah, ok - so they are WGS84 coordinates, it's just that they're formatted as degrees and decimal minutes, rather than decimal degrees (i.e. think of a coordinate being stated as 1¾ rather than 1.75).

    12129.4432E means a longitude of 121 degrees, 29.4432 minutes. To convert this to decimal degrees as expected by SQL Server, divide the minutes by 60 and add on to the degrees, to get  121.49072.

    Do the same with the latitude coordinate of 2502.9589 to get 25.049315.

    So, your code should be:

    INSERT into Measurement values(geography::Point(25.049315,121.49072,4326));

    which is a point near Zhōngxing bridge in Taiwan. Does that sound right?


    twitter: @alastaira blog: http://alastaira.wordpress.com/ | Pro Spatial with SQL Server 2012

    • Proposed as answer by Naomi N Tuesday, November 13, 2012 6:57 PM
    • Marked as answer by Shulei Chen Monday, November 19, 2012 9:18 AM
    Tuesday, November 13, 2012 7:44 AM
    Answerer