# find the closest match to given latitude / longitude from data stored in 2 columns

1. ## find the closest match to given latitude / longitude from data stored in 2 columns

Greetings,

I've run into a wall with this one, so I had to ask for help

I have 2 different sets of data: temperature data recorded at meteo-stations defined by latitude / longitude, and major world cities also defined by latitude / longitude.

After the user chooses the station's coordinates, temperature data is pulled out from the first data set; this works!
However, I would like to be able to provide information about the closest city to the chosen meteo-station from the second data set. This doesn't work, and I don't have a clue how to go about it.

Any good Samaritans out there? Please?
File attached with sample data sets.

Many thanks,
Florin

2. ## Re: find the closest match to given latitude / longitude from data stored in 2 columns

Suggest you use the Haversine formula to calculate the length of the arcs of the great circle defined by the two positions and then find the minimum.

Have a look at http://www.movable-type.co.uk/scripts/latlong.html

3. ## Re: find the closest match to given latitude / longitude from data stored in 2 columns

Hi Martin,

I knew about Haversine or spherical cos law, but do you suggest doing it about 1000 times for all cities in the data set?
Isn't it going to be slow?

Florin

4. ## Re: find the closest match to given latitude / longitude from data stored in 2 columns

How else would you find the minimum distance?

5. ## Re: find the closest match to given latitude / longitude from data stored in 2 columns

Matching the closest pair of coordinates from the cities data set?

6. ## Re: find the closest match to given latitude / longitude from data stored in 2 columns

see attachment and run macro "AllStations"

8. ## Re: find the closest match to given latitude / longitude from data stored in 2 columns

Thanks bsalv, I'll check it out and let you know how it works.

9. ## Re: find the closest match to given latitude / longitude from data stored in 2 columns

Nothing, this was just a debug output for one of my attempts.

10. ## Re: find the closest match to given latitude / longitude from data stored in 2 columns

Hi Gerard, thanks for your try but your solution matches only the closest latitude while ignoring the longitude; it means you get the wrong city.

11. ## Re: find the closest match to given latitude / longitude from data stored in 2 columns

Nothing, this was just a debug output for one of my attempts.
Did you try to run that macro ?
It 'll take probably 15 minutes to finish but afterwarts you get the last 4 columns in "Stations Data".
You can see the progress of the macro in the statusbar.

12. ## Re: find the closest match to given latitude / longitude from data stored in 2 columns

This would just be a one-time exercise, would it not? Just calculate the nearest city to each of the stations.

13. ## Re: find the closest match to given latitude / longitude from data stored in 2 columns

sorry for the error in the macro,
see the result in attachment, sheet "Stations_Data"

14. ## Re: find the closest match to given latitude / longitude from data stored in 2 columns

Hi bsalv,
I've tried to run your macro for all my 5700 stations but got the run-time error '9' subscript out of range after 747 stations at the line >> If MinCity = "" Then MinCity = c(i1, 3).
Any suggestions?

15. ## Re: find the closest match to given latitude / longitude from data stored in 2 columns

Here are the nearest cities pre-calculated.

16. ## Re: find the closest match to given latitude / longitude from data stored in 2 columns

that was the error in the previous macro in #6, that i1 must be i2, now see #13 or shg.
``Please Login or Register  to view this content.``

17. ## Re: find the closest match to given latitude / longitude from data stored in 2 columns

Greetings,

Let me summarise and give some thanks where due, before closing this thread.
What I had in mind was a run-time solution for finding the closest city similar with Gerard's attempt, i.e. using match, index, offset, etc.; that's why I initially questioned mrice's proposal for computing the distance.
What mrice, shg and bsalv proposed was a data set revamp, a new data set containing all info including the stations-cities closest match. On a balance I accept that this is the right solution and thank you all for helping. Reputation added.
Have a good day.

18. ## Re: find the closest match to given latitude / longitude from data stored in 2 columns

Hi shg, special thanks due to you for your very neat and fast distance calculation code.

19. ## Re: find the closest match to given latitude / longitude from data stored in 2 columns

Oops! Sorry to pester shg, but I'm getting the wrong results when running your code for all my stations; see example in the attached jpg, the highlighted station is very close to Melbourne, Australia.

20. ## Re: find the closest match to given latitude / longitude from data stored in 2 columns

Maybe, but Melbourne is not in the list of cities. Nor is any other city in Australia.

21. ## Re: find the closest match to given latitude / longitude from data stored in 2 columns

Time to wash my face; got egg all over it!
Forgotten to add my full list o cities - in your file was only a sample. Apology!

22. ## Re: find the closest match to given latitude / longitude from data stored in 2 columns

Originally Posted by florin_excel
Time to wash my face; got egg all over it!
Forgotten to add my full list o cities - in your file was only a sample. Apology!
Wondering why it returns the last data sets?

There are currently 1 users browsing this thread. (0 members and 1 guests)