查询地理位置附近的物品？ [英] Querying for things near a geolocation?
问题描述
我有一个查询尝试在某个地理位置中查找某些东西，但是它带来的结果有点奇怪。
我有之前发布的这个帖子和社区帮我找到了我需要的公式：在MySQL中查询经度和纬度，但查询现在给出了非常古怪的结果。
我的数据包含旧金山市，该市的经济/经济指标 37.780182，-122.517349
查询：
SELECT
（弧度（37））
* cos（弧度（经纬度））$ b（3959 * acos（cos（弧度（37））
hike_title，
lat，
ng，
$ b $ cos（弧度（lng） - 弧度（37.780182））
+ sin（弧度（-122.517349））* sin（弧度（lat））
）
）距离
FROM
my_awesome_table
HAVING
distance< 10000
ORDER BY
距离
LIMIT
0，50
正如你所看到的 - 即使我制作了半径10000，它仍然没有找到太多，所以我想知道我的查询是否关闭。它带来的一些结果甚至有0,0经纬度，这是我的方式来表示该记录没有纬度/经度。
这里是公式应该是这样的：
SELECT
id，
（3959 * acos（cos （弧度（37））
* cos（弧度（lat））
* cos（弧度（lng） - 弧度（-122））
+ sin（弧度（37））* sin （弧度（lat））
）
）距离
FROM
标记
HAVING
距离< 25
ORDER BY
距离
LIMIT
0,20;
感谢您对这个痛苦的长问题感到抱歉！顺便说一句，我使用的是MySQL。
嗯，
我刚刚尝试了您提供的确切查询，并且它的准确率达到了50％。这是一个数据集示例：
lat lng
| 37.223465 | -122.090363 |
| 39.320980 | -111.093735 |
| 38.031715 | -84.495132 |
| 37.787144 | -122.493263 |
| 52.975361 | -1.458620 |
| 40.848557 | -111.906883 |
| 40.572498 | -111.859718 |
所以现在查询返回了几英里以外的所有物品，但是不包含那些物品100英里远。就像这个项目（37.787144，-122.493263），即使距离始发点只有大约50英里，也不会得到回报。你会碰巧知道为什么吗？
解决方案 我认为你把中心的纬度（37.780182）和经度（-122.517349）点，试试：
select
id，
hike_title，
lat，$ b $ （弧度（37））
* cos（弧度（lat））
* cos（弧度（lng） - 弧度（-122.517349））
+ sin（弧度（37.780182））* sin（弧度（lat））
）
）距离
FROM
my_awesome_table
HAVING
距离< 10000
ORDER BY
距离
LIMIT
0，50
I have a query that attempts to find things within a certain geolocation, but the results that it brings back are a little bit... strange.
I had previously posted this thread and the community helped me find a formula that I needed: Querying within longitude and latitude in MySQL but the query now gives really wacky results.
My data is surrounding the city of San Francisco which has the lat/lng 37.780182 , -122.517349
Query:
SELECT
id,
hike_title,
lat,
lng,
( 3959 * acos( cos( radians(37) )
* cos( radians( lat ) )
* cos( radians( lng ) - radians(37.780182) )
+ sin( radians(-122.517349) ) * sin( radians( lat ) )
)
) AS distance
FROM
my_awesome_table
HAVING
distance < 10000
ORDER BY
distance
LIMIT
0 , 50
So as you see - even if I make radius 10000 it still doesn't find much, so I wonder if my query is off. Some of the results it brings back even have 0,0 for lat/lng which is my way to denote that there are no lat/lng for that record.
Here is how the formula is supposed to look like:
SELECT
id,
( 3959 * acos( cos( radians(37) )
* cos( radians( lat ) )
* cos( radians( lng ) - radians(-122) )
+ sin( radians(37) ) * sin( radians( lat ) )
)
) AS distance
FROM
markers
HAVING
distance < 25
ORDER BY
distance
LIMIT
0 , 20;
Thank you and sorry for the painfully long question ! By the way, I am using MySQL.
Hmmm,
I just tried with the exact query you gave and it worked with 50% accuracy. Here is a sample of the data set:
lat lng
| 37.223465 | -122.090363 |
| 39.320980 | -111.093735 |
| 38.031715 | -84.495132 |
| 37.787144 | -122.493263 |
| 52.975361 | -1.458620 |
| 40.848557 | -111.906883 |
| 40.572498 | -111.859718 |
So now the query returned all the items that were many miles away, but not the items which were under 100 miles away. Like this item ( 37.787144 , -122.493263 ) was never getting returned even though it is just about 50 miles from the originating point. Would you happen to know why?
I think you reversed the latitude (37.780182) and longitude (-122.517349) of your central point, try:
select
id,
hike_title,
lat,
lng,
( 3959 * acos( cos( radians(37) )
* cos( radians( lat ) )
* cos( radians( lng ) - radians(-122.517349) )
+ sin( radians(37.780182) ) * sin( radians( lat ) )
)
) AS distance
FROM
my_awesome_table
HAVING
distance < 10000
ORDER BY
distance
LIMIT
0 , 50
这篇关于查询地理位置附近的物品？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
