Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 13 down vote accepted

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.

share|improve this answer
    
+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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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