我有一个"geo_locations"表,看起来像这样:
country | city | postalCode | latitude | longitude | metroCode | areaCode
-------------------------------------------------------------------------
US | Chadler | 85226 | 33.2769 | -111.9444 | 753 | 480
more records...
和一个"users"表,看起来像这样:
user_id | sex | dob | country | region | city | zip | latitude | longitude | email | username | password
--------------------------------------------------------------------------------------------------------------------------------------
1 | m | 1987-05-14 | US | NY | Flushing | 11398 | 40.7723 | -73.8722 | foo@bar.com | HiBye99 | 54524sAS%ASa2?&^312
more records...
我的应用程序要求我向其他用户推荐用户,用于聚会,交朋友等。我必须向内部的其他用户推荐一个用户他们的地区。例如,向纽约的人推荐来自北京的人是没有意义的。
要实现这一点,我使用以下sql:
SELECT postalCode, latitude, longitude, ACOS(SIN($lat) * SIN(RADIANS(latitude)) + COS($lat) * COS(RADIANS(latitude)) * COS(RADIANS(longitude) - $lon)) * $radius AS D
FROM (
SELECT postalCode, latitude, longitude
FROM geo_locations
WHERE latitude > $min_lat AND latitude < $max_lat AND longitude > $min_lon AND longitude < $max_lon
) AS FirstCut
WHERE ACOS(SIN($lat) * SIN(RADIANS(latitude)) + COS($lat) * COS(RADIANS(latitude)) * COS(RADIANS(longitude) - $lon)) * $radius < $rad
ORDER BY D
在此之前,对$lat、$lon、$rad、$radius、$min_lat、$max_lat、$min_lon和$max_lon变量进行了一些奇特的计算。完整代码可在这里,我从
中获取了代码http://www.movable-type.co.uk/scripts/latlong-db.html
无论如何,这个sql在我的代码中返回的是靠近用户区域的所有"邮政编码"的集合。然后,我使用这些邮政编码构建另一个sql查询通常看起来像这样疯狂(所以不让我提交问题,因为它太大了):
http://jsfiddle.net/dgmHa/在users表上运行之后,我得到了我的推荐用户列表。
问题:
现在您可以看到,我在users表上也有纬度和经度列。因此,实际上不需要从"geo_locations"表中选择任何内容。如何更改查询,以便在一次查询中直接从"users"表中获取所有推荐用户?
我做这整件事的方式是一个性能噩梦,有没有更好的方法去做整件事?
要回答第一个问题,您应该能够将查询编写为:
SELECT user_id,
zip,
latitude,
longitude,
(ACOS(SIN($lat) * SIN(RADIANS(latitude)) + COS($lat) * COS(RADIANS(latitude)) * COS(RADIANS(longitude) - $lon)) * $radius) AS SearchRadius
FROM users
WHERE (latitude > $min_lat AND latitude < $max_lat)
AND (longitude > $min_lon AND longitude < $max_lon)
AND (ACOS(SIN($lat) * SIN(RADIANS(latitude)) + COS($lat) * COS(RADIANS(latitude)) * COS(RADIANS(longitude) - $lon)) * $radius) < $rad
ORDER BY SearchRadius
。您可以有效地忽略geo_locations表,只从用户中选择所有相同的列。
至于第二部分,非常诚实地说,决定这一点的最好方法是做一些测试。用大量信息填充users表,并测量查询所需的时间。然后重复记录的数量并重新测试。这样,您就可以看到更多数据对查询的影响。
替代方法包括仅从数据库中选择原始数据并在代码中进行计算。同样,您必须进行测试以了解性能的优点/缺点。