I have in my MySQL database both longitude and latitude coordinates (GPS data).
我的MySQL数据库中有经度和纬度坐标(GPS数据)。
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 store coordinate data?
问:我真的需要一个像十进制(10,6)这样大的数据类型来正确存储坐标数据吗?
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.
由于我在经度和纬度上有一个组合索引，所以这个索引大小很大。如果我能使它变小而不妥协，那就太好了。
8 个解决方案
#1
34
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).
WGS84数据通常以一个完全十进制记数形式给出，通常有5位小数，所以对于纬度(-90到+90)，你可以使用十进制(7,5)(-90.00000到90.00000)，对于经度，你可以使用十进制(8,5)(-180.00000到180.00000)。
.00001 gives an precision of around a meter at the equator
.00001在赤道处精确到一米左右
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)
十进制/数字数据类型是一个固定的精度缩放的整数，范围的正负部分都是可用的——它们不会影响精度或刻度(显然需要存储，但是您无法选择十进制)
#2
4
I've always worked with six digits after the decimal. I used to do GIS work under a military contract and this was sufficient.
我总是用小数点后的六位数。我以前在军事合同下做GIS工作，这就足够了。
#3
3
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.
另外，我不确定索引原始数据是最好的方法，因为它不是一组离散的元素。创建一个不那么精确/更小的数据点的表将使索引更小。
#4
1
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.
如果这是针对房地产，你真的有那么多房子每一行节省2字节会很明显吗?我将尽可能保持精确，除非有很好的理由不这样做。
#5
0
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.
这取决于你想要你的位置有多精确。显然，越大越精确，你的结果就越宽泛。我建议你保持你的值更大一些，因为这并不是真正的数据。
#6
0
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.
在NMEA Lat/lon输出中，常规的GGA语句在赤道处的分辨率只有10米左右。一些品牌增加了一个自定义的额外数字以提供100万。
4 digit deg.mm mm/1000 is also common.
4位数字。mm/1000也很常见。
If you are using high end precision RTK-GPS you might need more places to get mm precision
如果您正在使用高精度RTK-GPS，您可能需要更多的位置来获得mm的精度
#7
0
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.
您还可以尝试在不同的单元中存储(和/或使用)您的坐标。我参与的一个项目，我们所有的坐标都是以毫秒为单位存储的(可能是ints，已经有几年了)。这部分是为了速度和存储空间(这是一个嵌入式系统)。但同样的逻辑也适用于这里。
#8
0
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.
如果所有的坐标都在一个特定的区域，修正一些中心点(例如，平均电流点和圆，得到一个你可以大声说出来的数字)，然后将坐标存储在这个点上。这样你就可以跳过前2-4个最重要的数字，这可以节省大笔开支。但是请记住，只能通过返回真正的WGS84 coords的类或视图来处理这些数据。
#1
34
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).
WGS84数据通常以一个完全十进制记数形式给出，通常有5位小数，所以对于纬度(-90到+90)，你可以使用十进制(7,5)(-90.00000到90.00000)，对于经度，你可以使用十进制(8,5)(-180.00000到180.00000)。
.00001 gives an precision of around a meter at the equator
.00001在赤道处精确到一米左右
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)
十进制/数字数据类型是一个固定的精度缩放的整数，范围的正负部分都是可用的——它们不会影响精度或刻度(显然需要存储，但是您无法选择十进制)
#2
4
I've always worked with six digits after the decimal. I used to do GIS work under a military contract and this was sufficient.
我总是用小数点后的六位数。我以前在军事合同下做GIS工作，这就足够了。
#3
3
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.
另外，我不确定索引原始数据是最好的方法，因为它不是一组离散的元素。创建一个不那么精确/更小的数据点的表将使索引更小。
#4
1
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.
如果这是针对房地产，你真的有那么多房子每一行节省2字节会很明显吗?我将尽可能保持精确，除非有很好的理由不这样做。
#5
0
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.
这取决于你想要你的位置有多精确。显然，越大越精确，你的结果就越宽泛。我建议你保持你的值更大一些，因为这并不是真正的数据。
#6
0
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.
在NMEA Lat/lon输出中，常规的GGA语句在赤道处的分辨率只有10米左右。一些品牌增加了一个自定义的额外数字以提供100万。
4 digit deg.mm mm/1000 is also common.
4位数字。mm/1000也很常见。
If you are using high end precision RTK-GPS you might need more places to get mm precision
如果您正在使用高精度RTK-GPS，您可能需要更多的位置来获得mm的精度
#7
0
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.
您还可以尝试在不同的单元中存储(和/或使用)您的坐标。我参与的一个项目，我们所有的坐标都是以毫秒为单位存储的(可能是ints，已经有几年了)。这部分是为了速度和存储空间(这是一个嵌入式系统)。但同样的逻辑也适用于这里。
#8
0
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.
如果所有的坐标都在一个特定的区域，修正一些中心点(例如，平均电流点和圆，得到一个你可以大声说出来的数字)，然后将坐标存储在这个点上。这样你就可以跳过前2-4个最重要的数字，这可以节省大笔开支。但是请记住，只能通过返回真正的WGS84 coords的类或视图来处理这些数据。