I'm working on a new project where I need to save the latitude and longitude coordinates of an address in the database.
Is there an ideal data type for this?
I searched and saw some recommendations for using decimal, but I'm in doubt if it's the best type to use.
Far from using decimal or any floating point for spatial values.
Not only will you be using wrong types to write location values, but you will also experience great loss of performance when performing geo-location searches.
For a long time, the Spatials types are already implemented in the databases, be it MySQL, SQL Server, Oracle, etc.
See here about how to use spatial data in MySQL
CREATE TABLE localizacao (coordenadas GEOMETRY);
INSERT INTO localizacao (coordenadas) VALUES (ST_GeomFromText('POINT(40.71727401 -74.00898606)'));
SELECT ST_AsText(coordenadas) coordenadas FROM localizacao;
I usually use it this way and it always worked:
lat DECIMAL(10, 8) NOT NULL
lng DECIMAL(11, 8) NOT NULL
Considering:
Latitude 40.71727401
Longitude -74.00898606
Read this article that explains how MySQL works data from floating point type.
The unit digit (a decimal degree) gives the position up to 111 kilometers (60 nautical miles, or 69 miles). He tells us in what country we are about.
The first decimal place goes up to 11.1 km: it differentiates the position of a large city of another neighboring big city.
The second decimal place goes up to 1.1 km: it separates one village from another.
The third decimal place goes up to 110m: it identifies an agricultural field or an institutional campus.
The fourth every decimal goes up to 11m: it identifies a portion of land. It is comparable to the common accuracy of a GPS unit with no interference.
The fifth decimal place goes up to 1.1m: it differentiates a tree from another. The accuracy of this level in commercial GPS can only be achieved with differential correction.
A sixth decimal place holds up to 0.11 m: you can see the structure in details, to design landscapes or build streets. It should be more than enough to monitor the movement of glaciers and rivers. This can only be measured with very robust GPS.
The seventh decimal place is worth up to 11 mm: this is good for a lot survey and is close to the limit than GPS-based techniques can reach.
The Eighth decimal place is up to 1.1 mm: this is good for mapping movements of the plates and movements of volcanoes, Fixed constantly running GPS base stations permanent can be able to achieve this level of accuracy.
Details