Most efficient way for storing geolocations in a database.

Question

I know postgres has a datatype for storing geographical coordinates. But I'm looking for a RDBMS agnostic solution. Currently I'm using Decimal(25,20) in MySQL. I may be using this data to lookup these locations based on a given distance from a given location later. Which would be the best approach to store this kind of data?

Answer 1

The standard is here. Although it's too much for a simple use case such as yours, it may give you some ideas about why it may actually be best to go and use some OGC compliant packages many databases have nowadays, even MySQL.

Failing that, and assuming you will implement the algorithms for distance calculations, any floating point number that has the precision you need will work.

Answer 2

Another good technique is to multiply the values by a constant and store them as integer values. Using integers only can also help speed up calculations.

Unless you are in serious need of precision you should really only need to store 5+ values after the decimal point.

This Latitude Longitude Data Storage Specification gives a chart that shows precision vs decimal places.

# decmal places, example, precision
5    51.22135    ± 0.8 m
6   50.895132  ± 0.08 m

7 would work out to be 8mm, or about 0.314 inches.

Answer 3

Following @vfilby's answer, why not store the two halves of the number as two separate int types?

Answer 4

AFAIK, MS SQL Server 2008 has support for GeoLocations as a DataType. I know you are using MySQL, but just thought I'd mention it on this question.

Answer 5

I've always just used 4-byte floating point columns to save latitude and longitude, because the error in precision is much less than the accuracy of the devices we use. This may not be the case with your application, but if it is you can't get much more RDBMS-agnostic than a float.

Answer 6

vfilby's answer is probably the best one, however many RDBMS have better support for indexing character fields than indexing dense integer (or floating point) fields.

For this reason alone, I might recommend transforming the data first: If you want to find values "near" another value, you'll additionally want a function that preserves this- perhaps by converting to base36 and _-padding to the decimal point, but if you just need an exact match, nearly any fast hashing function will do.

Again: If you don't have a lot of data, or you're not using an RDBMS like this, do what vfilby suggested.