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:

lat 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?


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.

  • +1 for a good answer! This is what I would have said and done. – OptimizePrime May 30 '11 at 17:37
  • Thanks for the answer - I've read through the MySQL Spatial Extensions documentation, but how do you start to use them? I just have a default MySQL DB on GoDaddy; I don't know where I should start. – djq May 31 '11 at 15:34
  • 1
    @celenius maisonbisson.com/blog/post/12147/… – geographika May 31 '11 at 15:46
  • 1
    And answer updated with a link to more links – geographika May 31 '11 at 16:00
  • Why 4 integer digits? – Alix Axel May 9 '13 at 9:57

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.

  • Note: For those who are new and reading the above, MySql 5.7+ now works with Spatial Indexes. – HopeKing May 1 '18 at 10:41
  • but it is still slow – Ian Turton May 1 '18 at 10:42
  • Could you please help with resources that show how slow ? I am considering using it on a project with less than a million records. Thanks. – HopeKing May 1 '18 at 10:51
  • personal experience no public data. Too many issues with invalid geometries etc breaking things – Ian Turton May 1 '18 at 10:52
  • Thanks. My need is just distance between points based on database records (and no complex requirements like Polygon) -hence will probably be safe to use mysql for now. – HopeKing May 1 '18 at 10:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.