mysql查询中的距离计算 [英] distance calculations in mysql queries

问题描述

我必须查询成千上万个条目的数据库，并按距指定点的距离对其进行排序.

I have to query a database of thousands of entries and order this by the distance from a specified point.

问题是每个条目都有一个纬度和经度，因此我需要检索每个条目以计算其距离.对于大型数据库，我不想检索每一行，这可能需要一些时间.

The issue is that each entry has a latitude and longitude and I would need to retrieve each entry to calculate its distance. With a large database, I don't want to retrieve each row, this may take some time.

有什么方法可以将其构建到mysql查询中，以便我只需要检索最近的15个条目.

Is there any way to build this into the mysql query so that I only need to retrieve the nearest 15 entries.

例如

`SELECT events.id, caclDistance($latlng, events.location) AS distance FROM events ORDER BY distance LIMIT 0,15`

    function caclDistance($old, $new){
       //Calculates the distance between $old and $new
    }

推荐答案

选项1: 切换到支持GeoIP的数据库，对数据库进行计算.

Option 1: Do the calculation on the database by switching to a database that supports GeoIP.

选项2: 使用这样的存储过程对数据库进行计算:

Option 2: Do the calculation on the databaseusing a stored procedure like this:

CREATE FUNCTION calcDistance (latA double, lonA double, latB double, LonB double)
    RETURNS double DETERMINISTIC
BEGIN
    SET @RlatA = radians(latA);
    SET @RlonA = radians(lonA);
    SET @RlatB = radians(latB);
    SET @RlonB = radians(LonB);
    SET @deltaLat = @RlatA - @RlatB;
    SET @deltaLon = @RlonA - @RlonB;
    SET @d = SIN(@deltaLat/2) * SIN(@deltaLat/2) +
    COS(@RlatA) * COS(@RlatB) * SIN(@deltaLon/2)*SIN(@deltaLon/2);
    RETURN 2 * ASIN(SQRT(@d)) * 6371.01;
END//

如果数据库中有一个关于纬度和经度的索引，则可以通过计算PHP的初始边界框($ minLat，$ maxLat，$ minLong和$ maxLong)来减少需要计算的数量，并根据该行将行限制为条目的子集($ minLat和$ maxLat之间的纬度以及$ minLong和$ maxLong之间的经度).然后，MySQL只需要为该行子集执行距离计算.

If you have an index on latitude and longitude in your database, you can reduce the number of calculations that need to be calculated by working out an initial bounding box in PHP ($minLat, $maxLat, $minLong and $maxLong), and limiting the rows to a subset of your entries based on that (WHERE latitude BETWEEN $minLat AND $maxLat AND longitude BETWEEN $minLong AND $maxLong). Then MySQL only needs to execute the distance calculation for that subset of rows.

如果仅使用存储过程来计算距离，则SQL仍必须遍历数据库中的每条记录，并计算数据库中每条记录的距离，然后才能决定是否返回该行或丢弃它.

If you're simply using a stored procedure to calculate the distance) then SQL still has to look through every record in your database, and to calculate the distance for every record in your database before it can decide whether to return that row or discard it.

由于计算的执行速度相对较慢，因此最好减少需要计算的行的集合，从而消除明显落在所需距离之外的行，以便我们仅执行较少的行数需要昂贵的计算.

Because the calculation is relatively slow to execute, it would be better if you could reduce the set of rows that need to be calculated, eliminating rows that will clearly fall outside of the required distance, so that we're only executing the expensive calculation for a smaller number of rows.

如果您认为自己的工作基本上是在地图上画一个以初始点为中心并具有一定距离半径的圆；那么公式可以简单地识别出哪些行属于该圆...但是它仍然必须检查每一行.

If you consider that what you're doing is basically drawing a circle on a map, centred on your initial point, and with a radius of distance; then the formula simply identifies which rows fall within that circle... but it still has to checking every single row.

使用包围盒就像先在地图上绘制一个正方形，然后将左，右，上和下边缘与我们的中心点保持适当的距离.然后，将在该框中绘制我们的圆，使圆上的最北，最东，最南和最西点与框的边界接触.一些行将落在该框的外面，因此SQL甚至不必费心尝试计算这些行的距离.它仅计算落入边界框内的行的距离，以查看它们是否也落入圆内.

Using a bounding box is like drawing a square on the map first with the left, right, top and bottom edges at the appropriate distance from our centre point. Our circle will then be drawn within that box, with the Northmost, Eastmost, Southmost and Westmost points on the circle touching the borders of the box. Some rows will fall outside that box, so SQL doesn't even bother trying to calculate the distance for those rows. It only calculates the distance for those rows that fall within the bounding box to see if they fall within the circle as well.

在您的PHP中(假设您正在使用$变量名运行PHP)，我们可以使用一个非常简单的计算方法，根据我们的距离计算出最小和最大纬度和经度，然后在WHERE子句中设置这些值您的SQL语句.这实际上是我们的盒子，落在盒子外面的任何东西都会被自动丢弃，而无需实际计算其距离.

Within your PHP (guess you're running PHP from the $ variable name), we can use a very simple calculation that works out the minimum and maximum latitude and longitude based on our distance, then set those values in the WHERE clause of your SQL statement. This is effectively our box, and anything that falls outside of that is automatically discarded without any need to actually calculate its distance.

可移动键入网站，这对于打算用PHP进行任何GeoPositioning工作的任何人来说都是必不可少的阅读内容.

There's a good explanation of this (with PHP code) on the Movable Type website that should be essential reading for anybody planning to do any GeoPositioning work in PHP.

编辑 calcDistance存储过程中的值6371.01是乘数，用于为您提供以公里为单位的返回结果.如果您想得出英里，海里，米等的数值，请使用适当的替代乘数

EDIT The value 6371.01 in the calcDistance stored procedure is the multiplier to give you a returned result in kilometers. Use appropriate alternative multipliers if you want to result in miles, nautical miles, meters, whatever

这篇关于mysql查询中的距离计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！