Data types for storing lng/lat in MySQL

Question

I'm storing a large list of lnt/lat points in a MySQL database. At the moment these are projected points for the UK in M, but in the long term I would like to make sure that I can store the coordinates of points for all over the world. What datatype should I use?

I started using decimal(18,12), but was unsure whether this precision is needed or whether I could just use a float. I included my code, in case there is anything else I should be considering:

CREATE TABLE UKTest
(
lat FLOAT,
lng FLOAT
)

I'm quite new to SQL so I'm unsure whether the data type is significant when running a query. I assumed that the overall memory would vary based on the variable type. Is there a benefit of using a spatial database over a basic MySQL database for this type of work?

Answer 1

This question was also asked on StackOverlow.

The top answer suggests the MySQL Spatial Extensions. There are a load of links on working with these extensions here.

If you don't want to use spatial types and you are getting values from a GPS unit, or geocoding service then you can match your decimal precision to the data source. A general rule of thumb is to store data to an accuracy of two places greater than you will be displaying it in an application.

In a code example from Google displaying points on a map, they state:

When you create the MySQL table, you want to pay particular attention to the lat and lng attributes. With the current zoom capabilities of Google Maps, you should only need 6 digits of precision after the decimal.

To keep the storage space required for our table at a minimum, you can specify that the lat and lng attributes are floats of size (10,6). That will let the fields store 6 digits after the decimal, plus up to 4 digits before the decimal, e.g. -123.456789 degrees

I wouldn't worry about performance differences between numeric types. Decent indices will have a far greater effect.

Answer 2

Unless you are tied to MySQL for some other reason you should really consider using a spatially enabled database like postgis which has a Point (and Line, Polygon etc) object to handle these details for you. You also get projection support for when you make that change to the whole world.

2019: For people like me who also do not read comments - MySQL supports spatial data types, supposedly slower, without evidence.