MySql or PHP function to convert longitude and latitude to decimal degrees.

Question

Hello,

I have spent sometime researching the forum and other places to resolve this but yielded nothing and I have decided to ask.

I have log files containing NMEA sentences. I have stored this sentences which are in hundreds of thousands in mysql database. I have stored the longitudes and latitudes as double datatype. I want to convert these longitudes and latitudes which are currently in this format:

6013.45368, 2445.28396 

to the decimal degree format which is:

60,155095, 24,74456

I would appreciate if someone could help with this. Many thanks.

I came up with an sql select statement using the floor and round funtions as follows:

insert into converted_long_lat (new_latitude, new_longitude) select round ((FLOOR(latitude / 100) + (latitude - 100 * FLOOR(latitude / 100)) / 60), 5), round((FLOOR(longitude / 100) + (longitude - 100 * FLOOR(longitude / 100)) / 60),5) FROM    `rmc_raw_data`: 

The result is ok but the latitude, even before applying the round function returns a rounded interger like 24 leaving out the decimal part. How can I preserve the decimal part?

Answer 1

Are your conversions above correct?

NMEA Sentences have lat and long given as degrees and decimal minutes so 6013.45368 is 60 deg 13.45368 mins, which I make 60+(13.45368/60)=60.22428 deg and 2445.28396 becomes 24.75473 deg.

http://www.gpsinformation.org/dale/nmea.htm#position

http://home.online.no/~sigurdhu/Deg_formats.htm

From Degrees decimal-minutes (D m) to decimal-degrees (d)

Answer 2

I used the following to resolve it:

insert into converted_long_lat (new_latitude, new_longitude) SELECT ROUND((FLOOR(latitude / 100) + (latitude - 100 * FLOOR(latitude / 100)) / 60), 5), ROUND((FLOOR(longitude / 100) + (longitude - 100 * FLOOR(longitude / 100)) / 60),5) FROM rmc_raw_data`

The other issue of having the latitude returning an integer was due to it's column being set to an int type in the database. Hope someone else finds this solution useful.