What is the ideal data type to use when storing latitude / longitude in a MySQL database?
Bearing in mind that I'll be performing calculations on lat / long pairs, what datatype is best suited for use with a MySQL database?
Read more... Read less...
Google provides a start to finish PHP/MySQL solution for an example "Store Locator" application with Google Maps. In this example, they store the lat/lng values as "Float" with a length of "10,6"
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.
MySQL's Spatial Extensions are the best option because you have the full list of spatial operators and indices at your disposal. A spatial index will allow you to perform distance-based calculations very quickly. Please keep in mind that as of 6.0, the Spatial Extension is still incomplete. I am not putting down MySQL Spatial, only letting you know of the pitfalls before you get too far along on this.
If you are dealing strictly with points and only the DISTANCE function, this is fine. If you need to do any calculations with Polygons, Lines, or Buffered-Points, the spatial operators do not provide exact results unless you use the "relate" operator. See the warning at the top of 21.5.6. Relationships such as contains, within, or intersects are using the MBR, not the exact geometry shape (i.e. an Ellipse is treated like a Rectangle).
Also, the distances in MySQL Spatial are in the same units as your first geometry. This means if you're using Decimal Degrees, then your distance measurements are in Decimal Degrees. This will make it very difficult to get exact results as you get furthur from the equator.
When I did this for a navigation database built from ARINC424 I did a fair amount of testing and looking back at the code, I used a DECIMAL(18,12) (Actually a NUMERIC(18,12) because it was firebird).
Floats and doubles aren't as precise and may result in rounding errors which may be a very bad thing. I can't remember if I found any real data that had problems - but I'm fairly certain that the inability to store accurately in a float or a double could cause problems
The point is that when using degrees or radians we know the range of the values - and the fractional part needs the most digits.
Depends on the precision that you require.
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
- The most precise available option is
- The most common seen type used is
As of MySQL 5.7, consider using Spatial Data Types (SDT), specifically
POINT for storing a single coordinate. Prior to 5.7, SDT does not support indexes (with exception of 5.6 when table type is MyISAM).
- When using
POINTclass, the order of the arguments for storing coordinates must be
- There is a special syntax for creating a spatial index.
- The biggest benefit of using SDT is that you have access to Spatial Analyses Functions, e.g. calculating distance between two points (
ST_Distance) and determining whether one point is contained within another area (