Data type for latitude and longitude

Asked

Viewed 10,914 times

11

I am working on a new project where I will need to save the coordinates (latitude and longitude) of an address in the database.

There is an ideal data type for this?

I searched and saw some decimal recommendations, but I’m wondering if it’s the best type to use.

mysql sql typing
  • 1

    When the database is not specific for geolocation, I use integers anyway. Actually, the Mysql integer range is sufficient for very good accuracy, and is more efficient than any other format (except if you use a specific table for geospatial formats, which is often exaggerated for applications that won’t calculate proximity based on geodesics)

  • 1

    In my case, it’s something simpler. I’m just going to calculate a radius and see if certain coordinates are within that radius. But it’s good to know there’s a specific guy to work with! :)

  • 1

    For measurements of a few kilometers away, you can use integers in a good case, because basic trigonometry solves and is much faster. For larger distances, the geospatial shape is necessary to compensate for the curvature of the earth.

  • 1

    Blz! thanks for the tip! ;)

  • 1

    Of course when I speak of using integers, it is storing the value multiplied by a high power of 10, simply to take the decimal part without losing. An INT uses 4 bytes, and goes from -2147483648 to 2147483647, that is, you can store with 7 precision boxes if you multiply by 10 7: 214.7483648 (whereas it only needs -180 to 180). If you need more than this has the BIGINT, but it is usually an exaggeration.

2 answers

7

I 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 which explains how Mysql works floating point type data.

The unit digit (a decimal degree) gives the position up to 111 kilometers (60 nautical miles, or 69 miles). It tells us in what country approximately we are.

To first decimal place goes up to 11.1 km: it differentiates the position of a big city from another big city next door.

To second decimal place goes up to 1.1 km: it separates one village from another.

To third decimal place up to 110m: it identifies an agricultural field huge or an institutional campus.

To fourth each decimal It goes up to 11m: it identifies a portion of land. It is comparable to the common precision of a GPS unit without any interference.

To fifth decimal place goes up to 1.1m: it differentiates a tree from other. The accuracy of this level in commercial GPS can only be achieved with differential correction.

To sixth decimal place 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 glacier and river movements. This can only be measured with very robust GPS.

To 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.

To eighth decimal place is up to 1.1 mm good for mapping plate movements and volcanic movements, fixed constantly running GPS base stations permanent may be able to achieve this level of accuracy.

Details

  • 2

    But in my case I have different sizes of latitude and longitude from yours. For example, in latitude one of the values is: -23.5598163 and for longitude: -46.60262230000001. Setting a size n houses will cause me problems with varying coordinate sizes?

  • Take a look at this link where it shows the accuracies related to latitude and longitude. This longitude of -46.60262230000001, does not mean so much considering that the sixth decimal place is worth up to 0.11 m.

  • Understood! Thank you! ;)

6


Far from using decimal or any floating point for spatial values.

Not only will you be using wrong types to record location values, but you will also experience great performance loss when doing geo location searches.

For a long time already it is implementation the types Spatials in 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;

  • how I would know only the latitude or longitude in this bank scheme using GEOMETRY?

Browser other questions tagged mysql sql typing

You are not signed in. Login or sign up in order to post.