This week's giveaway is in the JDBC forum.
We're giving away four copies of Java Database Connections & Transactions (e-book only) and have Marco Behler on-line!
See this thread for details.
Win a copy of Java Database Connections & Transactions (e-book only) this week in the JDBC forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Devaka Cooray
  • Knute Snortum
  • Paul Clapham
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Bear Bibeault
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Ron McLeod
  • Piet Souris
  • Frits Walraven
Bartenders:
  • Ganesh Patekar
  • Tim Holloway
  • salvin francis

"Store Locator" sql results  RSS feed

 
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?
 
Marshal
Posts: 64494
225
  • 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?
 
author
Posts: 4107
24
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!!!
 
Marshal
Posts: 24498
55
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.
 
He was giving me directions and I was powerless to resist. I cannot resist this tiny ad:
how do I do my own kindle-like thing - without amazon
https://coderanch.com/t/711421/engineering/kindle-amazon
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!