7

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?

Nisse Engström
  • 4,738
  • 23
  • 27
  • 42
Genadinik
  • 18,153
  • 63
  • 185
  • 284
  • 3
    Why aren't you using MySQL's Spatial extensions which provide functions for this sort of stuff? http://dev.mysql.com/doc/refman/5.5/en/spatial-extensions.html – Jani Hartikainen Jan 15 '11 at 02:37
  • 2
    Just read through some of the topics there. Not sure I understand it too well yet, but possibly seems overkill for my needs. Does it make things much faster to query? Do you think it is much better solution than the one we are discussing in this thread? Thanks! – Genadinik Jan 15 '11 at 12:55

1 Answers1

12

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
Nisse Engström
  • 4,738
  • 23
  • 27
  • 42
daroczig
  • 28,004
  • 7
  • 90
  • 124