I need to calculate in Km distance from ZipCode>ZipCode or lat and long...?
Possible in vb6?
Tks.
calculate distance in KM base zip code and latitude and long
-
- PlatinumLounger
- Posts: 4362
- Joined: 26 Apr 2010, 17:36
-
- Administrator
- Posts: 78530
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: calculate distance in KM base zip code and latitude and
Italian zip codes? US zip codes? ...
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 689
- Joined: 27 Jan 2010, 16:45
- Location: Ottawa, Ontario, Canada
-
- PlatinumLounger
- Posts: 4362
- Joined: 26 Apr 2010, 17:36
Re: calculate distance in KM base zip code and latitude and
Sorry... in Italian zip code Naturally.HansV wrote:Italian zip codes? US zip codes? ...
-
- Administrator
- Posts: 78530
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: calculate distance in KM base zip code and latitude and
You can download a zip file containing a csv file with a list of postal codes with their longitude and latitude from Geocoding Databases for Europe. The one you want is "Download European Cities and Postcodes EU Standard (zipped folder, 3638.5K)", and the file is european_postcodes_eu_standard.csv.
You can then use the formulas from the web page mentioned by Don Wells to calculate distances.
You can then use the formulas from the web page mentioned by Don Wells to calculate distances.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4362
- Joined: 26 Apr 2010, 17:36
Re: calculate distance in KM base zip code and latitude and
Wow! Tkx for tath. But i nee in vba not in formula sheet.HansV wrote:You can download a zip file containing a csv file with a list of postal codes with their longitude and latitude from Geocoding Databases for Europe. The one you want is "Download European Cities and Postcodes EU Standard (zipped folder, 3638.5K)", and the file is european_postcodes_eu_standard.csv.
You can then use the formulas from the web page mentioned by Don Wells to calculate distances.
In other case i dont see a a formula. I have resd with carefull....
-
- Administrator
- Posts: 78530
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: calculate distance in KM base zip code and latitude and
The article contains (among others) the mathematical formulas that you need. You'll have to "translate" them to VB6 code yourself.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 689
- Joined: 27 Jan 2010, 16:45
- Location: Ottawa, Ontario, Canada
Re: calculate distance in KM base zip code and latitude and
Apologies if this should be in a different forum.
In the attached workbook, I have attempted to implement the following formula from the page referenced in my previous post using the coordinates from that page.
In the attached workbook, I have attempted to implement the following formula from the page referenced in my previous post using the coordinates from that page.
In lieu of automating the N/S & E/W considerations, I have forced a negative value in cells B13:B14. My workbook returns a distance of 19046.23 km. where the expected result is 968.9 km. Can someone advise me on my error?Distance
This uses the ‘haversine’ formula to calculate the great-circle distance between two points – that is, the shortest distance over the earth’s surface – giving an ‘as-the-crow-flies’ distance between the points (ignoring any hills, of course!).
Haversine
formula: a = sin²(Δφ/2) + cos(φ1).cos(φ2).sin²(Δλ/2)
c = 2.atan2(√a, √(1−a))
d = R.c
where φ is latitude, λ is longitude, R is earth’s radius (mean radius = 6,371km)
note that angles need to be in radians to pass to trig functions!
You do not have the required permissions to view the files attached to this post.
Regards
Don
Don
-
- PlatinumLounger
- Posts: 4362
- Joined: 26 Apr 2010, 17:36
Re: calculate distance in KM base zip code and latitude and
ok tkx.Don Wells wrote:Apologies if this should be in a different forum.
In the attached workbook, I have attempted to implement the following formula from the page referenced in my previous post using the coordinates from that page.
In lieu of automating the N/S & E/W considerations, I have forced a negative value in cells B13:B14. My workbook returns a distance of 19046.23 km. where the expected result is 968.9 km. Can someone advise me on my error?Distance
This uses the ‘haversine’ formula to calculate the great-circle distance between two points – that is, the shortest distance over the earth’s surface – giving an ‘as-the-crow-flies’ distance between the points (ignoring any hills, of course!).
Haversine
formula: a = sin²(Δφ/2) + cos(φ1).cos(φ2).sin²(Δλ/2)
c = 2.atan2(√a, √(1−a))
d = R.c
where φ is latitude, λ is longitude, R is earth’s radius (mean radius = 6,371km)
note that angles need to be in radians to pass to trig functions!
but thered result is in miles, meters, km... or?
-
- Administrator
- Posts: 78530
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: calculate distance in KM base zip code and latitude and
I don't know why the formula returns an incorrect result, but please note that it's possible to use the standard formula under Spherical Law of Cosines.
For Excel it becomes =ACOS(SIN(lat1)*SIN(lat2)+COS(lat1)*COS(lat2)*COS(lon2-lon1))*6371
Where lat1, lon1 are the coordinates of the first point and lat2, lon2 the coordinates of the second point in radians.
See attached version.
Sal, since the formula uses the radius of the Earth in kilometers, the result is in kilometers.
For Excel it becomes =ACOS(SIN(lat1)*SIN(lat2)+COS(lat1)*COS(lat2)*COS(lon2-lon1))*6371
Where lat1, lon1 are the coordinates of the first point and lat2, lon2 the coordinates of the second point in radians.
See attached version.
Sal, since the formula uses the radius of the Earth in kilometers, the result is in kilometers.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 689
- Joined: 27 Jan 2010, 16:45
- Location: Ottawa, Ontario, Canada
Re: calculate distance in KM base zip code and latitude and
Thank you Hans
My knowledge of Trigonometry is limited to secondary school planar trig studied some fifty years ago and seldom exercised since. However in the meantime I have learned to be wary of copying other people's work without fully understanding it; leading to a philosophy of "test, test, test".
In this instance it has introduced me to Atan2 which I am now working to fully comprehend; and identified an inconsistency between Excel and other languages as I have posted in the Excel thread ATAN2 -- A Cautionary Tale
Interchanging the arguments in the ATAN2 function caused the spreadsheet to return the correct distance.
My knowledge of Trigonometry is limited to secondary school planar trig studied some fifty years ago and seldom exercised since. However in the meantime I have learned to be wary of copying other people's work without fully understanding it; leading to a philosophy of "test, test, test".
In this instance it has introduced me to Atan2 which I am now working to fully comprehend; and identified an inconsistency between Excel and other languages as I have posted in the Excel thread ATAN2 -- A Cautionary Tale
Interchanging the arguments in the ATAN2 function caused the spreadsheet to return the correct distance.
Regards
Don
Don
-
- Administrator
- Posts: 78530
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: calculate distance in KM base zip code and latitude and
Thanks - I admit I didn't bother looking up how ATAN2 works.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 689
- Joined: 27 Jan 2010, 16:45
- Location: Ottawa, Ontario, Canada
Re: calculate distance in KM base zip code and latitude and
In this instance the result is in km. This is determined by the term 'R' the mean radius of the earth. To obtain the distance in another unit of measure you need to convert the radius of 6371 km to the desired unit.sal21 wrote: but thered result is in miles, meters, km... or?
But read through all posts in this thread before finalizing your approach.
Regards
Don
Don