mysql - 如何将地理位置查询与其他条件结合起来

Question

我有这两个问题:

SELECT
          (ACOS(least(1,COS(0.4878295615756141)*COS(-1.4391492410217162)*COS(RADIANS(places.lat))*COS(RADIANS(places.lng))+
          COS(0.4878295615756141)*SIN(-1.4391492410217162)*COS(RADIANS(places.lat))*SIN(RADIANS(places.lng))+
          SIN(0.4878295615756141)*SIN(RADIANS(places.lat))))*3963.1899999999996)
          AS distance, places.* 
FROM `places`  
WHERE ((
          (ACOS(least(1,COS(0.4878295615756141)*COS(-1.4391492410217162)*COS(RADIANS(places.lat))*COS(RADIANS(places.lng))+
          COS(0.4878295615756141)*SIN(-1.4391492410217162)*COS(RADIANS(places.lat))*SIN(RADIANS(places.lng))+
          SIN(0.4878295615756141)*SIN(RADIANS(places.lat))))*3963.1899999999996)
          <= 200.0))

和

SELECT `companies`.* 
FROM `companies` 
INNER JOIN `service_areas` ON `service_areas`.`company_id` = `companies`.`id` 
WHERE `companies`.`id` IN (1, 3, 6, ...) AND `service_areas`.`state_name` = 'CA'

它是这样工作的:第一个查询查找指定半径内的地点。第二个查询查找拥有第一个查询中找到的地点的所有公司。

第二个查询中的部分 - (1, 3, 6, ...) - 在 ruby​​ 中，我从地方获取所有 company_id 并将它们放入到第二个查询(company_id 是 places 表的一个属性)。

我试图将这两个查询合并为一个查询，因为我想按 distance 对公司进行排序(如果离给定点最近的地方属于“公司 A”，那么该公司将在输出中排在第一位)并且作为查询的结果，我正在尝试接收:

• 在给定半径内有地点的公司
• 属于公司的地方，这些地方也在指定的半径内。

这似乎有点超出我的能力范围，我正在尝试将这两个查询合并为一个，因为对于两个查询，我必须使用 Ruby 进行一些操作(以过滤位置)并且这些操作最后来自60-90 秒...

提前感谢大家的宝贵时间。

编辑:我稍微修改了查询，如下所示:

SELECT places.*, companies.*,
       69.0 * HAVERSINE(places.lat, places.lng, 27.950575,-82.45717) AS distance
FROM places 
JOIN companies ON companies.id = places.company_id
JOIN service_areas ON service_areas.company_id = companies.id  
WHERE places.lat BETWEEN 27.950575 - (200.0 / 69.0)
  AND 27.950575 + (200.0 / 69.0)    
  AND places.lng BETWEEN -82.45717 - (200.0 / (69.0 * COS(RADIANS(27.950575))))
  AND -82.45717 + (200.0 / (69.0 * COS(RADIANS(27.950575))))     
  AND companies.id = places.company_id 
AND service_areas.state_name = 'CA'   
ORDER BY distance

我还在 places.lat 和 places.lng 列上添加了索引。当我在 MySQL 控制台中运行这个查询时，我得到了 586 个结果；查询持续了 1 分 22 秒，当我第二次运行时 30 秒，第三次尝试时 18 秒。

我只是分析收到的结果以验证是否有我需要的。

编辑 2:

当我深入查看获取的结果时，我发现查询加载了 companies，但始终没有加载 places。我以为特定搜索没有地点，所以我更改了城市等，但查询仍然没有返回任何地点。

所以我尝试单独运行查询，如下所示:

SELECT places.*,
  69.0 * HAVERSINE(places.lat,places.lng, 27.950575,-82.45717) AS distance                                          
FROM places 
WHERE places.lat 
  BETWEEN 27.950575 - (200 / 69.0)
    AND 27.950575 + (200 / 69.0)
    AND places.lng
  BETWEEN -82.45717 - (200 / (69.0 * COS(RADIANS(27.950575))))
    AND -82.45717 + (200 / (69.0 * COS(RADIANS(27.950575))))

并且此查询返回 6,600 个位置，查询持续了 30 秒。我试图改变“大”查询中 JOIN 的顺序，希望这可能会导致没有 places 被获取，但它没有帮助, 仍然没有 places 加载。我想知道是什么导致了这个问题。

编辑 3:

甚至尝试这样做(省略 service_areas 表上的 WHERE 以调试它并找出为什么永远不会返回任何 places 通过查询):

SELECT places.*,
  69.0 * HAVERSINE(places.lat,places.lng, 27.950575,-82.45717) AS distance                                          
FROM places 
JOIN companies ON places.company_id = companies.id
WHERE places.lat 
  BETWEEN 27.950575 - (200 / 69.0)
    AND 27.950575 + (200 / 69.0)
    AND places.lng
  BETWEEN -82.45717 - (200 / (69.0 * COS(RADIANS(27.950575))))
    AND -82.45717 + (200 / (69.0 * COS(RADIANS(27.950575))))

结果是超过 5,000 家未过滤的公司，但仍然没有位置。

谢谢

Answer 1

看来你有两个问题。

1. 使此查询高效。
2. 使用距离计算在您的 places 表中查找内容并将其与其他表中的内容相关联。

看起来您在(以度为单位)27.950575、-82.45717 的特定位置使用常量值。如果那是美国佛罗里达州坦帕市中心 Zack St 上的某个位置，我猜对了您常量的含义。我们将这些值称为 latpoint 和 lonpoint。

另一个常量 3963.19 告诉我们您正在以英里为单位工作。每度数有 69 英里。

为了在不被大量数学淹没的情况下开始解决这个问题，让我们假设存在一个名为

的存储函数

 HAVERSINE(lat1,long1, lat2,long2)

可以在这里找到这样的函数:http://www.plumislandmedia.net/mysql/stored-function-haversine-distance-computation/

这样我们就可以构建可读性强的代码，让自己相信我们拥有正确的代码。

您的第一个查询可以使用一些不错的 WHERE 子句进行优化:

     places.lat BETWEEN latpoint - (200.0 / 69.0)
                    AND latpoint + (200.0 / 69.0)
 AND places.lon BETWEEN lonpoint - (200.0 / (69.0 * COS(RADIANS(latpoint))))
                    AND lonpoint + (200.0 / (69.0 * COS(RADIANS(latpoint)))) 

这些条款在您的起点周围划出了一个 200 英里的边界框。他们可以非常有效地在您的表中使用 (lat, lon) 上的索引。

因此，这将是您修改后的距离计算查询。

SELECT places.*,
       69.0 * HAVERSINE(places.lat,places.lon, latpoint,lonpoint) AS distance
  FROM places
 WHERE places.lat BETWEEN latpoint - (200.0 / 69.0)
                      AND latpoint + (200.0 / 69.0)
   AND places.lon BETWEEN lonpoint - (200.0 / (69.0 * COS(RADIANS(latpoint))))
                      AND lonpoint + (200.0 / (69.0 * COS(RADIANS(latpoint)))) 
  ORDER BY distance
  LIMIT 50

因为 WHERE 子句消除了 places 表中的许多行，这将节省大量时间。请参阅此以获得更完整的解释:http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

现在我们已经准备好了高效查询的框架，我需要做一个假设。就是这样:您可以执行此 JOIN。

 ... places
JOIN companies ON companies.id = places.company_id

因此，将这些内容添加到您的查询中变得非常容易。 编辑有关companies 和places 表如何关联的信息。

SELECT places.*, companies.*,
       69.0 * HAVERSINE(places.lat,places.lon, latpoint,lonpoint) AS distance
  FROM places
  JOIN companies ON companies.id = places.company_id
  JOIN service_areas ON companies.id = service_areas.company_id
 WHERE places.lat BETWEEN latpoint - (200.0 / 69.0)
                      AND latpoint + (200.0 / 69.0)
   AND places.lon BETWEEN lonpoint - (200.0 / (69.0 * COS(RADIANS(latpoint))))
                      AND lonpoint + (200.0 / (69.0 * COS(RADIANS(latpoint)))) 
   AND  companies.id IN (1, 3, 6, ...) AND service_areas.state_name = 'CA'
  ORDER BY distance
  LIMIT 50

这将找到服务区域在加利福尼亚的所有公司，这些公司的服务区域在您的 latpoint,lonpoint 位置(恰好在在佛罗里达州)200 英里范围内)。

places 的复合索引:(company_id, lat, lon) 可能会提高此查询的性能。

如果您使用距离标准以避免混淆，您可能希望省略 state_name 标准。