使用空间分析函数和数据类型在MySQL中按距离排序

I'm building a php web app with Laravel 5.5 and I need to display a list of places (eg. stores) sorted by their distance from a user-specified location. The places will be stored in a MySQL database and should be retrieved as Eloquent ORM model instances.

Doing some research I found many posts and questions on this topic (presenting different solutions), but, having very little experience with databases and geolocation/geospatial analysis, they mostly confused me, and I'd like to know what approach to follow and what are the best practices in this case.

Most answers I read suggest using the haversine formula or the spherical law of cosines in the SQL query, which would look something like (example taken from this answer):

$sf = 3.14159 / 180; // scaling factor
$sql = "SELECT * FROM table 
    WHERE lon BETWEEN '$minLon' AND '$maxLon' 
      AND lat BETWEEN '$minLat' AND '$maxLat'
    ORDER BY ACOS(SIN(lat*$sf)*SIN($lat*$sf) + COS(lat*$sf)*COS($lat*$sf)*COS((lon-$lon)*$sf))";

This post points out the fact that, over short distances, assuming the Earth flat and computing a simple euclidean distance is a good approximation and is faster than using the haversine formula.
Since I only need to sort places within a single city at a time, this seems to be a good solution.

However, most of these posts and SO answers are a few years old and I was wondering if there is now (MySQL 5.7) a better solution.

For example, none of those post use any of MySQL “Spatial Analysis Functions”, like ST_Distance_Sphere and ST_Distance which seem to be exactly for that purpose.
Is there any reason (eg. performance, precision) not to use these functions instead of writing the formula in the query? (I don't know which algorithm is internally used for these functions)

I also don't know how I should store the coordinates of each place. Most of the examples I've seen assume the coordinates to be stored in separate lat, lon columns as doubles or as FLOAT(10,6) (as in this example by google), but also MySQL POINT data type seems appropriate for storing geographic coordinates.
What are the pros and cons of these two approaches?

How can indexes be used to speed up these kind of queries? For example I've read about “spatial indexes”, but I think they can only be used for limiting the results with something like MBRContains(), not to actually order the results by distance.

So, how should I store the coordinates of places and how should I query them to be ordered by distance?

duanshai4484
duanshai4484 出于兴趣,你有多少分?你只需要最近的n吗?根据我的经验,这种应用的方法很大程度上取决于这些问题。这有很多索引和缓存技术。我在5ms内有60000多个属性搜索,但这些不是纯粹的MySQL解决方案-所以我真的不能帮到这里:)
2 年多之前 回复
douqiangchuai7674
douqiangchuai7674 如果可以避免,请不要滚动自己的GIS功能,使用空间扩展。其中许多问题早于该扩展变得流行和/或存在。如果您正在进行大量的GIS类型的工作,您可能会发现MySQL有点弱,Postgres与PostGIS的工作要好得多。
2 年多之前 回复

3个回答

I use a table that has lat & long associate with zip codes that I found. I use the haversine formula to find all zipcodes within a certain range. I then use that list of zip codes that are returned from that query and find all business with those zip codes. Maybe that solution will work for you. It was pretty easy to implement. This also eliminates you having to know the lat and long for the each business as long as you know the zip code.

douhan9748
douhan9748 我一定忽略了这一部分。 在这种情况下,他只需要按城市查询商店,可能会显着缩小结果,然后从那里运行hasrsine公式。 他必须知道每个企业的纬度和长期。
2 年多之前 回复
dst8922
dst8922 这可能是一个很好的解决方案,但OP表示“因为我一次只需要对一个城市内的地点进行排序”,所以在我看来,邮政编码对我来说不是一个很好的帮助
2 年多之前 回复



除ST_Distance_Sphere外,5.7不会为表带来任何额外的内容。 (已经实施了SPATIAL。)</ p>

对于“数千”积分,您拥有的代码可能是最好的。 包括</ p>

  INDEX(lat,lng),
INDEX(lng,lat)
</ code> </ pre>

我不会 担心地球的曲率,除非你伸展数千英里(公里)。 即使这样,代码和该函数也应该足够好。</ p>

不要使用 FLOAT(m,n)</ code>,只使用 FLOAT </ code> 。 下面的链接给出了 FLOAT </ code>和其他表示的可用精度。</ p>

如果你有这么多点,你就无法完全缓存表及其索引( 数百万点,你可以使用 this </ em> , 使用一些技巧来避免像上述解决方案那样冗长的扫描。 由于 PARTITION </ code>限制,lat / lng表示为缩放整数。 (但这很容易在输入/输出中进行转换。)地球的曲率,极点和日期线都处理完毕。</ p>
</ div>

展开原文

原文

Other than the ST_Distance_Sphere, 5.7 does not bring anything extra to the table. (SPATIAL was already implemented.)

For 'thousands' of points, the code you have is probably the best. Include

INDEX(lat, lng),
INDEX(lng, lat)

And I would not worry about the curvature of the earth unless you are stretching thousands of miles (kms). Even then the code and that function should be good enough.

Do not use FLOAT(m,n), use only FLOAT. The link below gives the precision available to FLOAT and other representations.

If you have so many points that you can't cache the table and its indexes entirely (many millions of points), you could use this , which uses a couple of tricks to avoid lengthy scans like the above solution. Because of PARTITION limitations, lat/lng are represented as scaled integers. (But that is easy enough to convert in the input/output.) The earth's curvature, poles, and dateline are all handled.

dsh1956
dsh1956 谢谢里克的回答。 我根据我的工作解决方案发布了一个答案,我在发布此奖励后发现了:)
2 年多之前 回复
duanqie8549
duanqie8549 我没有提出为数千点做这件事。 在一个典型的“商店发现者”应用程序中寻找25英里内的位置,您可以使用GCDist来获取25英里内的位置(可能不是很多),然后计算结果的行驶距离,抛弃任何> 25英里,并对剩余部分进行排序。 大多数类型的应用程序都为您提供某种地图显示,因此您可能已经完成了一半的工作。
2 年多之前 回复
duanlei20082008
duanlei20082008 - 您可以通过Google的某些API获得“驾驶距离”。 但我怀疑它不能很好地扩展,至少今年不会。 接下来,您将需要“驾驶时间”。 谷歌比我的汽车导航系统做得更好 - 因为处理当前的交通状况。
2 年多之前 回复
dongluo3962
dongluo3962 我认为GCDist是正确的答案,所以投票给Rick。 另一个相关问题是GC距离与行驶距离。 例如,纽约州诺斯波特和康涅狄格州达里恩相距约13英里,但被长岛海峡隔开,行驶距离为66英里,包括延伸至皇后区和布朗克斯区。 它可能对您的要求有些过分,但使用GC距离作为屏幕,然后使用此类地图工具将距离转换为行驶距离并重新排序将获得最佳效果。
2 年多之前 回复



使用 ST_DISTANCE_SPHERE </ code>或 MBRContains </ code>获取界限内点或点之间的距离 - 比使用不能使用索引的Haversine公式快得多,并且不是为查询距离而构建的,因为MySql对范围查询很慢。 请参阅 mysql文档。</ a> </ p>

Haversine公式可能适用于小型应用程序,大多数旧答案都引用该解决方案,因为旧版本的MySql innodb没有空间索引。 </ p>

这样做的广泛方法如下 - 以下是我在Java中的工作代码 - 希望您可以根据需要为PHP定制</ p>


  1. 首先将传入数据保存为数据库中的点</ strong>(请注意,坐标公式使用经度,纬度约定)</ p>

    < pre> GeometryFactory factory = new GeometryFactory();
    Point point = factory.createPoint(new Coordinate(officeDto.getLongitude(),officeDto.getLatitude())); // IMP:Longitude,Latitude
    officeDb。 setLocation(point);
    </ code> </ pre> </ li>

  2. 使用mysql中的以下内容创建空间索引</ strong> </ p>


    在办公室(位置)创建空间索引位置; </ p>
    </ blockquote> </ li>
    </ ol>

    您可能会收到错误 “SPATIAL索引的所有部分必须为NOT NULL”</ em>。 这是因为只有当字段为非空时才能创建空间索引 - 在这种情况下,将字段转换为非空</ p>


    1. 最后,从代码中调用自定义函数ST_DISTANCE_SPHERE,如下所示。 </ p>

        SELECT st_distance_sphere(office.getLocation,project.getLocation)
      作为距离FROM ....
      </ code> </ pre> </ li>
      </ ol>
  3. 注意:office.getLocation和project.getLocation都返回POINT类型。 本机SQL方法如下所示</ p>

      ST_Distance_Sphere(g1,g2 [,radius])
    </ code> </ pre>

    返回球体上两点和/或多点之间的最小球面距离,以米为单位,如果任何几何参数为NULL或为空,则返回NULL。</ p>
    </ div>

展开原文

原文

Use ST_DISTANCE_SPHERE or MBRContains to get distance between points or points within a bound - much faster than doing Haversine formula which can't use indices and is not built for querying distances and because MySql is slow with range queries. Refer mysql documentation.

Haversine formula is probably good for small applications and most of the older answer refer to that solution because older versions of MySql innodb did not have spatial indexes.

The broad method of doing it is as follows - the below is from my working code in Java - hope you can tailor it for PHP as per your needs

  1. First save the incoming data as a Point in database (Do note that the coordinate formula uses longitude, latitude convention)

        GeometryFactory factory = new GeometryFactory();
        Point point = factory.createPoint(new Coordinate(officeDto.getLongitude(), officeDto.getLatitude()));//IMP:Longitude,Latitude
        officeDb.setLocation(point);
    
  2. Create Spatial Indexes using the following in mysql

    CREATE SPATIAL INDEX location ON office (location);

You might get the error "All parts of a SPATIAL index must be NOT NULL". That is because spatial indexes can only be created if the field is NOT NULL - in such a case convert the field to non-null

  1. Finally, call the custom function ST_DISTANCE_SPHERE from your code as follows.

    SELECT st_distance_sphere( office.getLocation ,  project.getLocation) 
     as distance FROM ....
    

Note: office.getLocation and project.getLocation both return POINT types. Native SQL method is as below from documentation

ST_Distance_Sphere(g1, g2 [, radius]) 

which returns the mimimum spherical distance between two points and/or multipoints on a sphere, in meters, or NULL if any geometry argument is NULL or empty.

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐