• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

crow flies distance between two location

 
Habeeb Shaikh
Ranch Hand
Posts: 48
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi,
I want to calculate distance between 2 zipcodes using crow flies. let me know how i can achieve this. we can use any api like google,yahoo etc..
I really do not know exactly where to write.
 
Ulf Dittmer
Rancher
Posts: 42967
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The difficulty is that a zipcode is not a geometric point, but a geometric area. The distance could be just about zero (for two adjacent points), or several miles.
 
Campbell Ritchie
Sheriff
Posts: 48652
56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Have a look at this old thread, where somebody else had a related problem.
 
Habeeb Shaikh
Ranch Hand
Posts: 48
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for reply.
I have got latitude and longitude through our database.i am calculating distance using 2 ways
1. Spherical Law of Cosines
2. haversine formula.


In second type it is giving me proper answer. but it first type it is giving me result like 9170.1352. Although i am expecting is 377.84 miles. I do not understanding in which unit it is returning value. once i get know its unit then i can convert it in miles.

 
Winston Gutkowski
Bartender
Pie
Posts: 10277
60
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ulf Dittmer wrote:The distance could be just about zero (for two adjacent points), or several miles.

Several? I guess you've never been to the States . The whole of Montana only has 1,000. Alaska just 500.

Winston
 
Campbell Ritchie
Sheriff
Posts: 48652
56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Please show us the cosine algorithm. Are you using degrees or radians?
 
Habeeb Shaikh
Ranch Hand
Posts: 48
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi,
i have implement this formula.

acos(sin(lat1).sin(lat2)+cos(lat1).cos(lat2).cos(long2−long1)).R
 
Campbell Ritchie
Sheriff
Posts: 48652
56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That formula looks correct. I have got to go now, but please show us how you have implemented it, and somebody else will doubtless find you are using radians for part of it and degrees for the rest
 
Habeeb Shaikh
Ranch Hand
Posts: 48
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi we have implemented in that way
It's a query inwhich we a have passed zip1 and zip2 to get latitude and longitude..
Let me know in which unit its output.


sql_stmt = " SELECT 3959 * ACOS(( SIN((select z.latitude ",
" FROM zipdata_wrk z ",
" WHERE z.zipcode = '", zip1 CLIPPED, "') / 57.3) ",
" * SIN((select z.latitude",
" FROM zipdata_wrk z ",
" WHERE z.zipcode = '", zip2 CLIPPED,"') / 57.3) ) ",
" + (COS((select z.latitude ",
" FROM zipdata_wrk z ",
" WHERE z.zipcode = '", zip1 clipped, "' ) / 57.3) ",
" * COS((select z.latitude ",
" FROM zipdata_wrk z ",
" WHERE z.zipcode = '", zip2 clipped, "') / 57.3) ",
" * COS(((SELECT z.longitude from zipdata_wrk z ",
" WHERE z.zipcode = '", zip2 clipped, "' ) ",
" - (SELECT z.longitude ",
" FROM zipdata_wrk z ",
" WHERE z.zipcode = '", zip1 clipped, " ')) / 57.3))) ",
" FROM zipdata_wrk where zipcode = '", zip1, "' "
 
Campbell Ritchie
Sheriff
Posts: 48652
56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Where did you get 57.3 from? I made it 57.2957795130823208768. Why don’t you use that? Similarly, 3959 is only an approximate radius for the earth, and which varies with latitude. That radius is of course in statute miles.
 
Pat Farrell
Rancher
Posts: 4678
7
Linux Mac OS X VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Most folks calculate it as nautical miles, and then do a conversion. Nautical miles are nice and handy, since they directly relate to degrees, minutes and seconds of lat/long.

Here are some good sample locations to use for testing:


Its about 800 miles from the Whitehouse to Daytona International Speedway

 
Paul Clapham
Sheriff
Posts: 21002
31
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Habeeb Shaikh wrote:It's a query inwhich we a have passed zip1 and zip2 to get latitude and longitude..
Let me know in which unit its output.


So that means you're asking a question about your database, which you didn't tell us what that database is. Does its SIN() function require a number in degrees or radians? (The database's documentation should tell you that.) And are your database columns in those units?

And by the way you should seriously consider changing that query to use a PreparedStatement. If only so that then it wouldn't be totally unreadable because of all the quotes.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic