• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

"Store Locator" sql results

 
Joshua Elkino
Ranch Hand
Posts: 91
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm following this guide
http://code.google.com/support/bin/answer.py?answer=87134&topic=11364
to help me create store locator functionality, but changing the code to java. I have not actually gotten to the part that i will need to change as it took me some time to automate the geocoder funcionality in js and then pass to the db.

To test the Haversine sql code, i just tried it in the mysql command line window but i'm not getting any results.

Here is what i'm getting.

mysql> SELECT id, ( 3959 * acos( cos( radians(40) ) * cos( radians( lat ) ) * co
s( radians( lng ) - radians(-73) ) + sin( radians(40) ) * sin( radians( lat ) )
) ) AS distance FROM venueent HAVING distance < 25 ORDER BY distance LIMIT 0,20;

Empty set (0.00 sec)

mysql> select id, lat, lng from venueent;
+----+----------+----------+
| id | lat | lng |
+----+----------+----------+
| 1 | NULL | NULL |
| 2 | NULL | NULL |
| 3 | NULL | NULL |
| 4 | NULL | NULL |
| 5 | NULL | NULL |
| 6 | NULL | NULL |
| 7 | -73.9876 | -73.9876 |
| 8 | 40.7554 | -73.9876 |
+----+----------+----------+
8 rows in set (0.00 sec)

I know my data is very incomplete, but this should return atleast 2 values. Instead i get 0.
Why?
 
Campbell Ritchie
Sheriff
Posts: 51461
87
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Please explain the formula and algorithm you are using to work out the distance. It looks complicated; why are you calculating 3959 * acos(cos(radians(40)) rather than 3959 * 40 and where does 3959 come from?
 
Scott Selikoff
author
Saloon Keeper
Posts: 4033
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What happens if you remove "HAVING distance < 25" form the query? My guess is that for whatever reason your calculation is returning distances > 25.
 
Joshua Elkino
Ranch Hand
Posts: 91
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Scott, that fixed the problem.

Campbell - see the link in my initial post for the formula.
 
Joshua Elkino
Ranch Hand
Posts: 91
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
PS here is a tip for all.. long/lat coordinates are freakin sensitive! only a tenth of a point will miss an island like manhatten. This was screwing with my results as my initial location (rounded to the nearest degree) turned out to be in the atlantic ocean!!!
 
Paul Clapham
Sheriff
Posts: 21583
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Ilya Elkind:
(rounded to the nearest degree)
Well, yeah. A degree of latitude is over 100 kilometers. From Manhattan that would take you past Connecticut, into Massachusetts, and maybe even up into Vermont.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic