由于地理位置接近度公式(商店定位器)而导致缺少结果 [英] Missing results due to geo proximity formula (store locator)
问题描述
好的-我已经不停地努力了大约3个月，因为我已经用尽了我遇到的每个地理邻近度公式，而且我还无法接近我想出的正确结果是时候寻求帮助了.
OK - I've been wrestling with this for about 3 months on and off and since I've exhausted every geo proximity formula out there that I've come across and I'm no closer to getting the right results I figured it time to ask for some help.
目标
我正在设置商店定位器的相当基本的实现.用户输入他们的邮政编码，并从预定义的搜索半径列表中进行选择. gmaps API会为此地址生成纬度/经度坐标，并将其传递给php脚本.在此脚本中，将针对mysql数据库表(以下结构)查询用户坐标
I'm setting up a fairly basic implementation of a store locator. The user enters their postcode and selects from a predefined list of search radii. The gmaps API generates lat/long coordinates for this address and passes them to a php script. In this script the user coords are queried against a mysql database table (structure below)
post_id int(11)
post_type varchar(20)
lat float(10,6)
lng float(10,6)
此查询的结果(帖子ID)输入到wordpress查询中，该查询生成包含地图标记数据的XML. (wordpress查询使用post__in和posts_per_page -1显示查询生成的所有ID的信息
The results of this query (post ids) are entered into a wordpress query which generates the XML that contains the map marker data. (the wordpress query uses post__in and posts_per_page -1 to display info for all ID generated by the query
问题
简而言之，我遇到的Haversine公式的每个实现似乎都会导致缺少标记-特别是任何与用户非常接近的标记都输入了坐标(不确定，但我认为它在500m范围内).这是一个很大的问题，好像用户输入了邮政编码，并且附近有一家商店不会显示.
In a nutshell, every implementation of the Haversine formula I've come across seems to result in missing markers - specifically any markers that are very close to the users entered coordinates (don't know precisely but I think it's within about 500m). This is a big problem as if the user enters their postcode and there is a store very close to their location it won't show up.
我已经尝试了8种不同的论坛排列，这些排列是我从各种教程中挖出的，结果都是一样的.下面是我当前在网站上使用的公式，该公式提供了所有标记，除了那些非常接近用户输入位置的标记:
I've tried about 8 different permutations of the forumla that I've dug up from various tutorials with the same results. Below is the formula that I'm currently using on the site which provides all markers except for the those very close to the users entered position:
$center_lat = $_GET["lat"];
$center_lng = $_GET["lng"];
$radius = $_GET["radius"];
// Calculate square radius search
$lat1 = (float) $center_lat - ( (int) $radius / 69 );
$lat2 = (float) $center_lat + ( (int) $radius / 69 );
$lng1 = (float) $center_lng - (int) $radius / abs( cos( deg2rad( (float) $center_lat ) ) * 69 );
$lng2 = (float) $center_lng + (int) $radius / abs( cos( deg2rad( (float) $center_lat ) ) * 69 );
$sqlsquareradius = "
SELECT
post_id, lat, lng
FROM
wp_geodatastore
WHERE
lat BETWEEN ".$lat1." AND ".$lat2."
AND
lng BETWEEN ".$lng1." AND ".$lng2."
"; // End $sqlsquareradius
// Create sql for circle radius check
$sqlcircleradius = "
SELECT
t.post_id,
3956 * 2 * ASIN(
SQRT(
POWER(
SIN(
( ".(float) $center_lat." - abs(t.lat) ) * pi() / 180 / 2
), 2
) + COS(
".(float) $center_lat." * pi() / 180
) * COS(
abs(t.lat) * pi() / 180
) * POWER(
SIN(
( ".(float) $center_lng." - t.lng ) * pi() / 180 / 2
), 2
)
)
) AS distance
FROM
(".$sqlsquareradius.") AS t
HAVING
distance <= ".(int) $radius."
ORDER BY distance
"; // End $sqlcircleradius
$result = mysql_query($sqlcircleradius);
$row = mysql_fetch_array( $result );
while($row = mysql_fetch_array( $result )) {
// the contents of each row
$post_ids[] = $row['post_id'];
}
我尝试过1个由Mike Pelley建议的公式:地理位置SQL查询未找到确切位置
There was 1 formula that I tried that was suggested by Mike Pelley here: Geolocation SQL query not finding exact location
此公式似乎显示出与用户输入的位置非常接近的标记，但是错过了应该在给定半径内显示的其他标记.为了清除任何混乱，这是我使用的代码:
This formula seemed to show markers that were very close to the users entered location but missed out others that should have been displayed within the given radius. To clear up any confusion this is the code I used:
$center_lat = $_GET["lat"];
$center_lng = $_GET["lng"];
$radius = $_GET["radius"];
$sql = "
SELECT post_id, lat, lng,
truncate((degrees(acos( sin(radians(lat))
* sin(radians(".$center_lat."))
+ cos(radians(lat))
* cos(radians(".$center_lat."))
* cos(radians(".$center_lng." - lng) ) ) )
* 69.09*1.6),1) as distance
FROM wp_geodatastore HAVING distance <= ".$radius." ORDER BY distance desc
"; // End $sqlcircleradius
$result = mysql_query($sql);
$row = mysql_fetch_array( $result );
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row
$post_ids[] = $row['post_id'];
}
请求
基本上，我想知道为什么这些代码块都没有显示正确的标记.如果有人可以提出对代码的任何改进建议，或者可以指出一些我可能会错过的资源，那就太好了
Basically I would like to know why neither of these blocks of code are displaying the correct markers. If anyone can suggest any improvements to the code or can point me towards some resource that I might have missed that would be great
EDIT
EDIT
以为我的假名答案是可行的，但事实证明仍然存在问题.我现在最终选择了一种非常不同的方法，并且我使用的是一个很好的jquery商店定位器，可以在以下位置找到它:
Thought my psudeo answer was working but as it turns out that was still having problems. I've ended up going for a very different tack now and I'm using a very good jquery store locator which can be found here: http://www.bjornblog.com/web/jquery-store-locator-plugin
不会为每个项目工作，但是对于我的需求来说，它是完美的(并且可以工作！)
Won't work for every project out there but for my needs it's perfect (and works!)
推荐答案
从侧面考虑，我针对缺失标记的问题提出了一种"解决方案.我最初发布的两个方程式给出了正确的结果，但每个方程式都错过了靠近目标或位于搜索半径边缘的标记
Thinking a little laterally I've come up with a 'sort of' solution to the problem of the missing markers. The two equations I posted originally gave the correct results but each missed out either markers close to the target or on the edges of the search radius
这不是很优雅，但我认为运行两个方程并生成2个数组，然后将它们组合起来(删除所有重复项)，将为我提供所有要查找的标记.这确实有效(显然对性能有影响，但它不是高流量的应用程序)，所以我暂时将使用它，但是如果有人可以使用，我仍在寻求更实用的解决方案！
It's not very elegant but I figured that running both equations and producing 2 arrays which I then combined (removing any duplicates) would give me all the markers I'm looking for. This does work (obviously a performance hit but it's not a high traffic application) so I'll work with this for the time being but I'm still after a more practical solution if anyone has one!
这篇关于由于地理位置接近度公式(商店定位器)而导致缺少结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
