Data type for latitude and longitude

10

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.

    
asked by anonymous 10.10.2016 / 15:31

2 answers

6

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;
    
10.10.2016 / 17:37
4

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

10.10.2016 / 15:36