使用空间点类型在MySQL中存储Lat Lng值 [英] Storing Lat Lng values in MySQL using Spatial Point Type

问题描述

使用的技术:MySQL 5.1和PHP 5.3

Tech used: MySQL 5.1 and 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.

过去，我一直在使用DECIMAL并使用以下形式的PHP/MySQL选择:

In the past I have been using DECIMAL and using a PHP/MySQL select in the form:

SQRT(POW(69.1 * (fld_lat - ( $lat )), 2) + POW(69.1 * (($lon) - fld_lon) * COS(fld_lat / 57.3 ), 2 )) AS distance

查找最近的匹配地点.

开始阅读有关新技术的更多信息，我想知道是否应该使用Spatial Extensions. http://dev.mysql.com/doc/refman /5.1/zh-CN/geometry-property-functions.html

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

尽管信息在地面上非常薄，但是对如何存储数据存在疑问.现在我将使用POINT作为数据类型，而不是使用DECIMAL?

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?

此外，一旦存储为POINT，就可以很容易地从中获取Lat Lng值，以防万一我想在地图上绘制该Lat Lng值，或者我还应该另外将lat lng存储为DECIMALS吗?

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?

我知道我应该使用PostGIS，因为这里的大多数帖子都说我只是不想学习新的数据库！

I know I should prob use PostGIS as most posts on here say I just don't want to learn a new DB though!

关注

我一直在使用新的POINT类型.我已经可以使用以下方法添加Lat Lng值:

I have been playing with the new POINT type. I have been able to add Lat Lng values using the following:

INSERT INTO spatialTable (placeName, geoPoint) VALUES( "London School of Economics", GeomFromText( 'POINT(51.514 -0.1167)' ));

然后我可以使用以下方法从Db中获取纬度和经度值:

I can then get the Lat and Lng values back from the Db using:

SELECT X(geoPoint), Y(geoPoint) FROM spatialTable;

这一切看起来不错，但是距离的计算是我需要解决的问题.显然，MySQL有一个距离函数的占位符，但是暂时不会发布.在几篇文章中，我发现我需要执行以下操作，但是我认为我的代码略有错误:

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:

SELECT
 placeName,
 ROUND(GLength(
  LineStringFromWKB(
   LineString(
    geoPoint, 
    GeomFromText('POINT(52.5177, -0.0968)')
  )
  )
))
AS distance
FROM spatialTable
ORDER BY distance ASC;

在此示例中，geoPoint是使用上面的INSERT输入到数据库中的POINT.

In this example geoPoint is a POINT entered into the DB using the INSERT above.

GeomFromText('POINT(52.5177, -0.0968)'是我想计算距离的纬度Lng值.

GeomFromText('POINT(52.5177, -0.0968)' is a Lat Lng value I want to calculate a distance from.

更多后续跟踪

愚蠢的是，我只是没有真正考虑就把SQL的ROUND部分放进去了.把这个拿出来给我:

Rather stupidly I had just put in the ROUND part of the SQL without really thinking. Taking this out gives me:

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.

我想目前唯一需要回答的是关于我现在是否只是通过使用Spatial还是让自己适应未来的想法使自己的生活变得困难...

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

推荐答案

我认为您应该始终使用容易获得的最高级别的抽象.如果您的数据是地理空间的，则使用地理空间的对象.

I think you should always use the highest level abstraction easily available. If your data is geospatial, then use geospatial objects.

但是要小心. MySQL是最糟糕的地理空间数据库.可以点显示，但其所有多边形功能都被破坏了-他们将多边形更改为其边界矩形，然后对此进行了解答.

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.

打给我的最糟糕的例子是，如果您有一个代表日本的多边形，并且询问在日本有哪些地方，符拉迪沃斯托克就会进入列表！

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和PostGIS没有此问题.我希望MSSQL不会，并且任何使用JTS作为其引擎的Java数据库都不会.地理空间优势. MySQL Geospatial Bad.

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.

只需在此处阅读

Just read here How do you use MySQL spatial queries to find all records in X radius? that its fixed in 5.6.1.

Hoorah！

这篇关于使用空间点类型在MySQL中存储Lat Lng值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！