• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

"Store Locator" sql results

 
Ranch Hand
Posts: 91
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Marshal
Posts: 79179
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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!!!
 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic