GeoCoding - Find the closest point

**ZiadzExcel** · 01-13-2015, 06:28 PM

Hello All,

As the title states, I have 2 lists with several points, list 1 has ~200k coordinates (Lat/Long), and list 2 has about 2,400 coordinates. The idea is to step through each of the 200k coordinates and find which of the 2,400 coordinates is closest.

Currently I've been trying it in a spreadsheet, but since I'm using 32bit it goes kaput on me.

Any ideas?

The formula for calculating distance between 2 GPS points is as follows:

Please Login or Register  to view this content.

Taken from here: http://www.ehow.com/how_7695232_conv...ates-feet.html

Attached is a small sample of how I've been doing it the brute force method.

**Kaper** · 01-14-2015, 08:09 AM

I think, well, I'm almost sure, that the results are wrong. Why? because the formula as I read the link provided works on degrees, while excel in trigonometrical functions expects radians.

Anyway as your workbook would be overloaded with formulas (not strange, because 2.4K x 200K is ca 0.5G of complicated formulas), and I do not think 64bits and large ram would make situation much better.

The macro below has not been optimized yet (still a lot of potential for improvement, especially in reading and writing to columns B:E) but shall do something reasonable for you.
so insert a part (say just 100 as a starting run) of your 200K list1 points in columns B:C (starting row 5) and all list 2 points in columns N:O (also starting row 5)
and try:

Please Login or Register  to view this content.

The calculated distance is in miles, but of course you can multiply it by 5280 to obtain result in feet.

You can use attached file, but please before testing on larger sets of data clear content (formulas !!!) of columns F:L.

If the time is acceptable (shall be few seconds maximum), then try larger sub-set of list1 (say 10000). The time shall be roughly lineary dependant on list1 length (so 100 times longer then previous run - few, may be 10 minutes).

If it is so - you can try a full run - shall take some 20 times longer - say 3 hrs. Or do it part by part (safer way tahn potentially 3hrs run which breaks after 2.5 hr

).

So try and let us know how it worked (hopefully) for you.