mysql - Geo SQL to find points near location -
i using 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 ) ) ) distance stops having distance <100 limit 0 , 30
to find stops within radius of current lat , lon.
i using gtfs data , schema below. (the lat , lon , last 2 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 this:
as far can see, query should return results. doesn't. can tell me might missing?
*** update ***********
i noticed missing temp output table name, getting results. however, start @ 5000 distance location. 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 ) ) ) distance stops dest having distance <5100 order distance limit 10
thanks,
i suspect formula culprit. if took reliable source , made no errors in implementing, there can e.g. unit confusion.
- there's error:
abs
never needed, there's no such thing in the ‘haversine’ formula. in 2nd case, doesn't changecos
function, in 1st case, does.
Comments
Post a Comment