使用空间点类型在 MySQL 中存储 Lat Lng 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4995428/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Storing Lat Lng values in MySQL using Spatial Point Type
提问by bateman_ap
Tech used: MySQL 5.1 and PHP 5.3
使用的技术：MySQL 5.1 和 PHP 5.3
I am just designing a new database for a site I am writing. I am looking at the best way of now storing Lat and Lng values.
我只是在为我正在编写的网站设计一个新数据库。我正在寻找现在存储 Lat 和 Lng 值的最佳方式。
In the past I have been using DECIMAL and using a PHP/MySQL select in the form:
过去我一直使用 DECIMAL 并使用以下形式的 PHP/MySQL 选择：
SQRT(POW(69.1 * (fld_lat - ( $lat )), 2) + POW(69.1 * (($lon) - fld_lon) * COS(fld_lat / 57.3 ), 2 )) AS distance
to find nearest matching places.
找到最近的匹配地点。
Starting to read up more on new technologies I am wondering if I should use Spatial Extensions. http://dev.mysql.com/doc/refman/5.1/en/geometry-property-functions.html
开始阅读有关新技术的更多信息，我想知道是否应该使用 Spatial Extensions。http://dev.mysql.com/doc/refman/5.1/en/geometry-property-functions.html
Information is quite thin on the ground though and had a question on how to store the data. Instead of using DECIMAL, would I now use POINT as a Datatype?
但是，地面上的信息非常稀少，并且对如何存储数据存在疑问。我现在不使用 DECIMAL，而是使用 POINT 作为数据类型吗？
Also, once stored as a POINT is it easy just to get the Lat Lng values from it in case I want to plot it on a map or should I additionally store the lat lngs as DECIMALS again as well?
此外，一旦存储为 POINT 是否很容易从中获取 Lat Lng 值，以防万一我想将其绘制在地图上，或者我是否应该另外将 lat lngs 再次存储为 DECIMALS？
I know I should prob use PostGIS as most posts on here say I just don't want to learn a new DB though!
我知道我应该使用 PostGIS，因为这里的大多数帖子都说我只是不想学习新的数据库！
Follow up
跟进
I have been playing with the new POINT type. I have been able to add Lat Lng values using the following:
我一直在玩新的 POINT 类型。我已经能够使用以下方法添加 Lat Lng 值：
INSERT INTO spatialTable (placeName, geoPoint) VALUES( "London School of Economics", GeomFromText( 'POINT(51.514 -0.1167)' ));
I can then get the Lat and Lng values back from the Db using:
然后我可以使用以下方法从 Db 中获取 Lat 和 Lng 值：
SELECT X(geoPoint), Y(geoPoint) FROM spatialTable;
This all looks good, however the calculation for distance is the bit I need to solve. Apparently MySQL has a place-holder for a distance function but won't be released for a while. In a few posts I have found I need to do something like the below, however I think my code is slightly wrong:
这一切看起来都不错，但是距离的计算是我需要解决的问题。显然 MySQL 有一个距离函数的占位符，但暂时不会发布。在一些帖子中，我发现我需要做类似下面的事情，但是我认为我的代码有点错误：
SELECT
placeName,
ROUND(GLength(
LineStringFromWKB(
LineString(
geoPoint,
GeomFromText('POINT(52.5177, -0.0968)')
)
)
))
AS distance
FROM spatialTable
ORDER BY distance ASC;
In this example geoPoint is a POINT entered into the DB using the INSERT above.
在这个例子中，geoPoint 是使用上面的 INSERT 输入到数据库中的一个点。
GeomFromText('POINT(52.5177, -0.0968)'is a Lat Lng value I want to calculate a distance from.
GeomFromText('POINT(52.5177, -0.0968)'是我想要计算距离的 Lat Lng 值。
More Follow-up
更多跟进
Rather stupidly I had just put in the ROUND part of the SQL without really thinking. Taking this out gives me:
相当愚蠢的是，我只是在没有真正思考的情况下放入了 SQL 的 ROUND 部分。把这个拿出来给我：
SELECT
placeName,
(GLength(
LineStringFromWKB(
LineString(
geoPoint,
GeomFromText('POINT(51.5177 -0.0968)')
)
)
))
AS distance
FROM spatialTable
ORDER BY distance ASC
Which seems to give me the correct distances I need.
这似乎给了我我需要的正确距离。
I suppose the only thing currently that needs answering is any thoughts on whether I am just making life difficult for myself by using Spatial now or future-proofing myself...
我想目前唯一需要回答的问题是我是否只是通过现在使用 Spatial 使自己生活变得困难，还是让自己适应未来……
采纳答案by Julian
I think you should always use the highest level abstraction easily available. If your data is geospatial, then use geospatial objects.
我认为您应该始终使用容易获得的最高级别的抽象。如果您的数据是地理空间数据，则使用地理空间对象。
But be careful. Mysql is the worst geospatial database there is. Its OK for points but all its polygon functions are completely broken - they change the polygon to its bounding rectangle and then do the answer on that.
不过要小心。Mysql 是最糟糕的地理空间数据库。它可以用于点，但它的所有多边形函数都被完全破坏了 - 他们将多边形更改为其边界矩形，然后对其进行回答。
The worst example that hit me is that if you have a polygon representing Japan and you ask what places are in Japan, Vladivostok gets into the list!
打我的最糟糕的例子是，如果你有一个代表日本的多边形，你问日本有哪些地方，符拉迪沃斯托克就会进入列表！
Oracle and PostGIS don't have this problem. I expect MSSQL doesn't and any Java database using JTS as its engine doesn't. Geospatial Good. MySQL Geospatial Bad.
Oracle 和 PostGIS 没有这个问题。我希望 MSSQL 不会，任何使用 JTS 作为引擎的 Java 数据库都不会。地理空间好。MySQL 地理空间错误。
Just read here How do you use MySQL spatial queries to find all records in X radius?that its fixed in 5.6.1.
刚读到这里你如何使用 MySQL 空间查询来查找 X 半径内的所有记录？它在 5.6.1 中修复。
Hoorah!
万岁！
回答by Vajk Hermecz
If you can affor placing some extra code into your backend, use Geohash.
如果您愿意在后端放置一些额外的代码，请使用 Geohash。
It encodes a coordinate into a string in a way that prefixes denote a broader area. The longer your string is, the more precision you have.
它以前缀表示更广泛区域的方式将坐标编码为字符串。字符串越长，精度越高。
And it has bindings for many languages.
它具有多种语言的绑定。
http://en.wikipedia.org/wiki/Geohashhttps://www.elastic.co/guide/en/elasticsearch/guide/current/geohashes.html
http://en.wikipedia.org/wiki/Geohash https://www.elastic.co/guide/en/elasticsearch/guide/current/geohashes.html
回答by ashwoods
Mysql GIS yagni:
Mysql GIS yagni：
If you have no experience with GIS, learning spatial extensions is practically like learning a new database, plus a little math, and a lot of acronyms. Maps, projections, srids, formats... Do you have to learn all that to calculate distances between points given a certain lat/long: probably not, will you be integrating 3rd party GIS data or working with anything more complex than points, what coordinate system will you be using?
如果您没有 GIS 经验，学习空间扩展实际上就像学习一个新的数据库，加上一点数学知识和许多首字母缩略词。地图、投影、网格、格式……您是否必须学习所有这些来计算给定特定纬度/经度的点之间的距离：可能不会，您是要集成第 3 方 GIS 数据还是使用比点更复杂的任何东西，什么？你会使用坐标系吗？
Going back to yagni: do things as simple as posible, in this case implement your code in php or with simple SQL. Once you reach a barrier and decide you need spatial, read up on GIS system, coordinate systems, projects, and conventions.
回到 yagni：做尽可能简单的事情，在这种情况下，用 php 或简单的 SQL 实现你的代码。一旦到达障碍并决定需要空间，请阅读 GIS 系统、坐标系统、项目和约定。
By then, you will probably want PostGIS.
到那时，您可能会想要 PostGIS。
回答by aredridel
It's a good thing, because then you get to use spatial indexes on your queries. Limit to a bounding box, for example, to limit how many rows to compare against.
这是一件好事，因为这样您就可以在查询中使用空间索引。例如，限制为边界框以限制要比较的行数。
