How many significant digits should I store in my database for a GPS coordinate?

teddyk picture teddyk · Dec 22, 2009 · Viewed 21.6k times · Source

I have in my MySQL database both longitude and latitude coordinates (GPS data).

It's currently stored as:

column     type
------------------------
geolat     decimal(10,6)
geolng     decimal(10,6)

Question: Do I really need a data type as large as decimal(10,6) to properly store coordinate data?

Since I have a combined index on the longitude and latitude, this index size is huge. If I can make it smaller without compromising anything, that would be great.

Answer

Cade Roux picture Cade Roux · Dec 22, 2009

WGS84 datum are usually given as coordinates in a fully decimal notation, usually with 5 decimal places, so for latitude (-90 to +90) you could use decimal(7, 5) (-90.00000 to 90.00000), for longitude you could use decimal(8, 5) (-180.00000 to 180.00000).

.00001 gives an precision of around a meter at the equator

The DECIMAL/NUMERIC data type is a fixed precision scaled integer and both positive and negative parts of the range are always available - they do not affect the precision or scale (there is obviously storage required for it, but you don't get a choice about that for DECIMAL)