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

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 2

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.