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
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)
– Bacco
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! :)
– alan
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.
– Bacco
Blz! thanks for the tip! ;)
– alan
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
to2147483647
, 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.– Bacco