Geo SQL查找位置附近的点 [英] Geo SQL to find points near location

问题描述

我使用以下sql:

基于 http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL

SELECT * , 3956 *2 * ASIN( SQRT( POWER( SIN( (
- 36.8812573 - abs( stop_lat ) ) * pi( ) /180 /2 ) , 2 ) + COS( - 36.8812573 * pi( ) /180 ) * COS( abs( stop_lat ) * pi( ) /180 ) * POWER( SIN( ( 174.63832160000004 - stop_lon ) * pi( ) /180 /2 ) , 2 ) )
) AS distance
FROM stops
HAVING distance <100
LIMIT 0 , 30

查找在当前经纬度半径范围内的停靠点.

To find stops that are within a radius of my current lat and lon.

我正在使用GTFS数据，其架构如下. (纬度和经度以及最后两列)

I am using GTFS data and the schema is below. (The lat and lon and last two columns)

01  7106    210 Victoria St     -36.8481990     174.7544900
0002    7108    220 Victoria St     -36.8481710     174.7523800
0003    7110    36 College Hill     -36.8485220     174.7485400
0004    7112    68 College Hill     -36.8479760     174.7466700
0005    7114    2 Jervois Rd    -36.8471670     174.7437800
0006    7116    90 Jervois Rd   -36.8453760     174.7408300
0007    7118    160 Jervois Rd  -36.8453250     174.7373100
0008    7120    206 Jervois Rd  -36.8454990     174.7354500
0009    7122    270 Jervois Rd  -36.8474010     174.7321400
0012    7121    203 Jervois Rd  -36.8473770     174.7319200
0013    7119    165 Jervois Rd  -36.8454100     174.7348400

这样的表结构:

据我所知，查询应该返回一些结果.但是事实并非如此.谁能告诉我我可能会想念什么?

As far as I can see, the query should return some results. However it doesn't. Can anyone tell me what I might be missing?

***更新***********

*** Update ***********

我注意到我缺少临时输出表名称，所以现在得到了一些结果.但是，它们从距我的位置5000处开始.有什么想法吗?

I noticed that I was missing the temp output table name, so I am now getting some results. However, they start at 5000 distance from my location. Any ideas?

更新的SQL:

SELECT * , 3956 *2 * ASIN( SQRT( POWER( SIN( (
- 36.8812573 - abs( stop_lat ) ) * pi( ) /180 /2 ) , 2 ) + COS( - 36.8812573 * pi( ) /180 ) * COS( abs( stop_lat ) * pi( ) /180 ) * POWER( SIN( ( 174.63832160000004 - stop_lon ) * pi( ) /180 /2 ) , 2 ) )
) AS distance
FROM stops dest
HAVING distance <5100
ORDER BY distance
LIMIT 10 

谢谢

推荐答案

我怀疑公式是罪魁祸首.即使您是从可靠的来源获得的，并且在实施过程中没有出现任何错误，例如会造成单位混乱.

I suspect the formula is the culprit. Even if you took it from a reliable source and made no errors in implementing, there can e.g. be a unit confusion.

• 有一个错误:永远不需要abs，在.在第二种情况下，它不会更改任何内容，因为cos是偶函数，但是在第一种情况下，它却没有改变.

• There's an error: abs is never needed, there's no such thing in the ‘haversine’ formula. In the 2nd case, it doesn't change anything as cos is an even function, but in the 1st case, it does.

这篇关于Geo SQL查找位置附近的点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！