How many significant digits should I store in my database for a GPS coordinate?_问答_开发者

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 s开发者_高级运维tore 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.

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)

It depends on the accuracy of the survey you are conducting. In general:

The 6 decimal places represent accuracy for ~ 10 cm
The 7 decimal places for ~ 1 cm
The 13 decimal places - accuracy for an atom structure...

Therefore, unless very precise studies i.e. for tectonic plates movements, the use of 6 digits should be enough.

Here is the copy of the full answer from whuber (GIS stack overflow):

Accuracy is the tendency of your measurements to agree with the true values. Precision is the degree to which your measurements pin down an actual value. The question is about an interplay of accuracy and precision.

As a general principle, you don't need much more precision in recording your measurements than there is accuracy built into them. Using too much precision can mislead people into believing the accuracy is greater than it really is.

Generally, when you degrade precision--that is, use fewer decimal places--you can lose some accuracy. But how much? It's good to know that the meter was originally defined (by the French, around the time of their revolution when they were throwing out the old systems and zealously replacing them by new ones) so that ten million of them would take you from the equator to a pole. That's 90 degrees, so one degree of latitude covers about 10^7/90 = 111,111 meters. ("About," because the meter's length has changed a little bit in the meantime. But that doesn't matter.) Furthermore, a degree of longitude (east-west) is about the same or less in length than a degree of latitude, because the circles of latitude shrink down to the earth's axis as we move from the equator towards either pole. Therefore, it's always safe to figure that the sixth decimal place in one decimal degree has 111,111/10^6 = about 1/9 meter = about 4 inches of precision.

Accordingly, if your accuracy needs are, say, give or take 10 meters, than 1/9 meter is nothing: you lose essentially no accuracy by using six decimal places. If your accuracy need is sub-centimeter, then you need at least seven and probably eight decimal places, but more will do you little good.

Thirteen decimal places will pin down the location to 111,111/10^13 = about 1 angstrom, around half the thickness of a small atom.

Using these ideas we can construct a table of what each digit in a decimal degree signifies:

The sign tells us whether we are north or south, east or west on the globe.
A nonzero hundreds digit tells us we're using longitude, not latitude!
The tens digit gives a position to about 1,000 kilometers. It gives us useful information about what continent or ocean we are on.
The units digit (one decimal degree) gives a position up to 111 kilometers (60 nautical miles, about 69 miles). It can tell us roughly what large state or country we are in.
The first decimal place is worth up to 11.1 km: it can distinguish the position of one large city from a neighboring large city.
The second decimal place is worth up to 1.1 km: it can separate one village from the next.
The third decimal place is worth up to 110 m: it can identify a large agricultural field or institutional campus.
The fourth decimal place is worth up to 11 m: it can identify a parcel of land. It is comparable to the typical accuracy of an uncorrected GPS unit with no interference.
The fifth decimal place is worth up to 1.1 m: it distinguish trees from each other. Accuracy to this level with commercial GPS units can only be achieved with differential correction.
The sixth decimal place is worth up to 0.11 m: you can use this for laying out structures in detail, for designing landscapes, building roads. It should be more than good enough for tracking movements of glaciers and rivers. This can be achieved by taking painstaking measures with GPS, such as differentially corrected GPS.
The seventh decimal place is worth up to 11 mm: this is good for much surveying and is near the limit of what GPS-based techniques can achieve.
The eighth decimal place is worth up to 1.1 mm: this is good for charting motions of tectonic plates and movements of volcanoes. Permanent, corrected, constantly-running GPS base stations might be able to achieve this level of accuracy.
The ninth decimal place is worth up to 110 microns: we are getting into the range of microscopy. For almost any conceivable application with earth positions, this is overkill and will be more precise than the accuracy of any surveying device.
Ten or more decimal places indicates a computer or calculator was used and that no attention was paid to the fact that the extra decimals are useless. Be careful, because unless you are the one reading these numbers off the device, this can indicate low quality processing!

I've always worked with six digits after the decimal. I used to do GIS work under a military contract and this was sufficient.

Keep in mind that it's easier to reduce data than it is to increase data. Usually, increasing data accuracy isn't even possible short of remeasuring. And remeasuring comes at a cost. Knowing nothing else of your situation or the industry, I would say capture as much data/specificity as possible.

The data that you actually use can be culled from this set. If you end up needing a higher degree of specificity, you can always recalculate without remeasuring.

Also, I'm not sure that indexing raw data is the best thing to do since it isn't a discrete set of elements. Creating a table of less accurate/smaller data points would make the indexes much smaller.

if this is for real estate do you really have so many houses that 2 bytes saved per row is going to be that noticeable? I'd keep as much precision as possible unless there was a good reason not to.

That depends on how precise you want your locatability to be. Obviously the larger the more precise, and the smaller the more broad your results will be. I'd suggest keeping your values larger, as it isn't really much data anyway.

The regular GGA sentance in a NMEA Lat/lon output is only 3decimal places roughly 10m resolution at the equator. Some brands add a custom extra digit to give 1m.

4 digit deg.mm mm/1000 is also common.

If you are using high end precision RTK-GPS you might need more places to get mm precision

You can also try storing (and/or working with) your coordinates in different units. One project I worked on, all of our coordinates were in milliarcseconds stored as longs (maybe ints, it's been a couple years). This was done partially for speed and for storage space (this was an embedded system). But the same logic could apply here.

If all coordinates are in a specific area, fix some central point (i.e. average current points and round to get a number you can say out loud) and then store coordinates as relative to this point. That way you can probably skip the first 2-4 most significant digits which makes for big savings. But remember to handle this data only via a Class or VIEW that gives back true WGS84 coords.