Menu
Bearing in mind that I'll be performing calculations on lat / long pairs, what datatype is best suited for use with a MySQL database?
961
precision you should use DECIMAL . Latitudes range from -90 to +90 (degrees), so DECIMAL(10,8) is ok for that, but longitudes range from -180 to +180 (degrees) so you need DECIMAL(11,8) . The first number is the total number of digits stored, and the second is the number after the decimal point.
Storing Latitude & Longitude data as Floats or Decimal This is one of the most fundamental ways of storing geocoordinate data. Latitude & longitude values can be represented & stored in a SQL database using decimal points (Decimal degrees) rather than degrees (or Degrees Minutes Seconds).
Location data are information about the geographic positions of devices (such as smartphones or tablets) or structures (such as buildings, attractions). The geographic positions of location data are called coordinates, and they are commonly expressed in Latitude and Longitude format.
Longitude has the range [-180;180] so it should be stored in DECIMAL(9,6) .
Use MySQL's spatial extensions with GIS.
90
Basically it depends on the precision you need for your locations. Using DOUBLE you'll have a 3.5nm precision. DECIMAL(8,6)/(9,6) goes down to 16cm. FLOAT is 1.7m...
This very interesting table has a more complete list: http://mysql.rjweb.org/doc.php/latlng :
Datatype Bytes Resolution Deg*100 (SMALLINT) 4 1570 m 1.0 mi Cities DECIMAL(4,2)/(5,2) 5 1570 m 1.0 mi Cities SMALLINT scaled 4 682 m 0.4 mi Cities Deg*10000 (MEDIUMINT) 6 16 m 52 ft Houses/Businesses DECIMAL(6,4)/(7,4) 7 16 m 52 ft Houses/Businesses MEDIUMINT scaled 6 2.7 m 8.8 ft FLOAT 8 1.7 m 5.6 ft DECIMAL(8,6)/(9,6) 9 16cm 1/2 ft Friends in a mall Deg*10000000 (INT) 8 16mm 5/8 in Marbles DOUBLE 16 3.5nm ... Fleas on a dog Hope this helps.
29
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
