Geo SQL to find points near location

问题:

I using the below sql:

based on 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.

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

The table structure like this:

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 ***********

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?

Updated 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 

Thanks,

回答1:

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.

• 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.