Speculative thinking, say you have a list of 615 addresses. How do you find the nearest three locations to each address?

[RockandGrohl]

Let's say you have 1,400 postcodes that correspond to a point on a map. Using a location local(ish) to me, in the UK I have 615 locations across England. I want to find the nearest three for each of the 615 locations, in that same list.

So as an example we choose Gloucester. Geographically near Gloucester in my list of 615 addresses, we have Cheltenham, Stroud, Tewkesbury, and then further afield are Cirencester, Ross-on-Wye, Ledbury.

The closest three are Cheltenham, Stroud & Tewkesbury and I would like this to be reflected. The only information I have is postcode, so this would need to link in to mapping functionality somehow. I'm just wondering if anyone does anything similar and whether this technique is used in anyone's existing processes.

Thanks!

 

[RockandGrohl]

EDIT: Oh-ohoho, this is transforming into an SQL level question. I have the Lat/Long of each postcode on our database. In case anyone stumbles upont this, I think you may even be able to work this out using Excel, if I'm correct in assuming that the Haversine Formula is multiplying the Long/Lat values to find close matches.

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;


So as long as you convert acos, cos, radians into formula... I guess this can work. BRB, let me keep working on this.

 

[CsJHUN]

First of all, woow, nice.
I would get all 1400 (615) postal code UTM (in mm), i guess its available online somewhere. Then use a simple subtraction for N and E values from your location UTM coordinate. The smallest cumulative is closest, in straigth line.

yourlocation: YL
postalcodelocaton: PL

North: PLN-YLN = ND (north distance)
East: PLE-YLN = ED (east distance)
ND^2+ED^2= D^2 (distance)


Love to see your result!


ps.: my colleagaue said: use google darnit :D

 

[AlanY]

this work for a small sample of 20, should work for a larger date.
you can get the lat & longit here

Download UK Postcodes with Latitude and Longitude

Download a list of UK out code postcodes with their latitude and longitude coordinates

www.freemaptools.com

Book1
	A	B	C	D	E	F	G	H	I	J
1	id	postcode	latitude	longitude					latitude	longitude
2	1	AB10 1XG	57.14416516	-2.114847768	0.145679		Nearest to	AB12 5GL	57.08193792	-2.246567389
3	2	AB10 6RN	57.13787976	-2.121486688	0.137021		1	AB14 0TQ
4	3	AB10 7JB	57.12427377	-2.127189644	0.126662		2	AB15 8UF
5	4	AB11 5QN	57.14270109	-2.093295	0.164877		3	AB15 9SE
6	5	AB11 6UL	57.13754663	-2.112233	0.145389
7	6	AB11 8RQ	57.13597762	-2.072114784	0.182631
8	7	AB12 3FJ	57.0980029	-2.077438	0.169891
9	8	AB12 4NA	57.06427275	-2.130018015	0.117881
10	9	AB12 5GL	57.08193792	-2.246567389	0
11	10	AB12 9SP	57.14870708	-2.097806027	0.163058
12	11	AB14 0TQ	57.10155692	-2.268485752	0.029416
13	12	AB15 5HB	57.147428	-2.1472662	0.118952
14	13	AB15 6NA	57.151797	-2.185398	0.092855
15	15	AB15 8UF	57.15400596	-2.22440188	0.0754
16	16	AB15 9SE	57.11864762	-2.174250607	0.081101
17	17	AB16 5ST	57.16346588	-2.15933343	0.119401
18	18	AB16 6SZ	57.15875117	-2.165214861	0.111886
19	19	AB16 7NX	57.1684384	-2.161636	0.121226
20	20	AB21 0AL	57.26342	-2.158605	0.201676
Sheet1

Cell Formulas
Range	Formula
I2:J2	I2	=INDEX(C2:C20,MATCH($H2,$B$2:$B$20,0))
H3:H5	H3	=INDEX($B$2:$B$20,MATCH(ROUND(SMALL($E$2:$E$20,G3+1),6),$E$2:$E$20,0))
E2:E20	E2	=ROUND(((C2-$I$2)^2+(D2-$J$2)^2)^0.5,6)

 

[RockandGrohl]

Seems like it will work, but I have no idea how to get the closest three results for each line. So I have 615 lines and each has a Lat & Long value. So each line needs to check against 614 lines and find the 1st, 2nd and 3rd closest location and then display them.

=ACOS(COS(RADIANS(90-$E$2)) *COS(RADIANS(90-E3)) +SIN(RADIANS(90-$E$2)) *SIN(RADIANS(90-E3)) *COS(RADIANS($F$2-F3))) *3958.756


My cords are in column E and F, and placenames are in column C.

So I need to take the cords of the first row of data (2), then calculate the entire column and get the min 1, then the min 2 in the next cell and min 3 in the next cell.

Thinking this might evolve to VBA as I need to effectively calculate the whole set of data for each line and then grab the min.

 

[AlanY]

Seems like it will work, but I have no idea how to get the closest three results for each line. So I have 615 lines and each has a Lat & Long value. So each line needs to check against 614 lines and find the 1st, 2nd and 3rd closest location and then display them.

=ACOS(COS(RADIANS(90-$E$2)) *COS(RADIANS(90-E3)) +SIN(RADIANS(90-$E$2)) *SIN(RADIANS(90-E3)) *COS(RADIANS($F$2-F3))) *3958.756


My cords are in column E and F, and placenames are in column C.

So I need to take the cords of the first row of data (2), then calculate the entire column and get the min 1, then the min 2 in the next cell and min 3 in the next cell.

Thinking this might evolve to VBA as I need to effectively calculate the whole set of data for each line and then grab the min.

Column E calculated the distance from AB12 5GL in H2, simply by A^2 = B^2 + C^2.
H3 to H5 are the 3 closest

 

[CsJHUN]

Column E calculated the distance from AB12 5GL in H2, simply by A^2 = B^2 + C^2.
H3 to H5 are the 3 closest

He/she need a 2D matrix for all postal code. and tag(condition format) the 3 smallest of each row and colum
Maybe i confuse you. When i wrote yourlocation i meant for the actual/desired postalcodelocation. YL = PL1 and PL = PL2 -> PL1N-PL2N and PL1E-PL2E

 

[AlanY]

apology, the distance calculation is rubbish, but found this on other forum should do the job

UK Postcode Distance Calculator

I first put the attached file together some years ago, and I've mentioned it on one post here soon after I joined and have had a few PMs asking for a copy, so I thought I might as well submit it here. The file works out the distance in miles between 2 UK postcodes. On the Single sheet you can...

www.excelforum.com

 

[RockandGrohl]

this work for a small sample of 20, should work for a larger date.
you can get the lat & longit here

Download UK Postcodes with Latitude and Longitude

Download a list of UK out code postcodes with their latitude and longitude coordinates

www.freemaptools.com

Book1
	A	B	C	D	E	F	G	H	I	J
1	id	postcode	latitude	longitude					latitude	longitude
2	1	AB10 1XG	57.14416516	-2.114847768	0.145679		Nearest to	AB12 5GL	57.08193792	-2.246567389
3	2	AB10 6RN	57.13787976	-2.121486688	0.137021		1	AB14 0TQ
4	3	AB10 7JB	57.12427377	-2.127189644	0.126662		2	AB15 8UF
5	4	AB11 5QN	57.14270109	-2.093295	0.164877		3	AB15 9SE
6	5	AB11 6UL	57.13754663	-2.112233	0.145389
7	6	AB11 8RQ	57.13597762	-2.072114784	0.182631
8	7	AB12 3FJ	57.0980029	-2.077438	0.169891
9	8	AB12 4NA	57.06427275	-2.130018015	0.117881
10	9	AB12 5GL	57.08193792	-2.246567389	0
11	10	AB12 9SP	57.14870708	-2.097806027	0.163058
12	11	AB14 0TQ	57.10155692	-2.268485752	0.029416
13	12	AB15 5HB	57.147428	-2.1472662	0.118952
14	13	AB15 6NA	57.151797	-2.185398	0.092855
15	15	AB15 8UF	57.15400596	-2.22440188	0.0754
16	16	AB15 9SE	57.11864762	-2.174250607	0.081101
17	17	AB16 5ST	57.16346588	-2.15933343	0.119401
18	18	AB16 6SZ	57.15875117	-2.165214861	0.111886
19	19	AB16 7NX	57.1684384	-2.161636	0.121226
20	20	AB21 0AL	57.26342	-2.158605	0.201676
Sheet1

Cell Formulas
Range	Formula
I2:J2	I2	=INDEX(C2:C20,MATCH($H2,$B$2:$B$20,0))
H3:H5	H3	=INDEX($B$2:$B$20,MATCH(ROUND(SMALL($E$2:$E$20,G3+1),6),$E$2:$E$20,0))
E2:E20	E2	=ROUND(((C2-$I$2)^2+(D2-$J$2)^2)^0.5,6)

Hi bud,

so if I do this, but use a different way to calculate distance in miles, the formula doesn't work.

Instead of Distance being calculated like this:

=ROUND(((E2-$M$2)^2+(F2-$N$2)^2)^0.5,6)

I have it like this

=ACOS(COS(RADIANS(90-$E$2)) *COS(RADIANS(90-E2)) +SIN(RADIANS(90-$E$2)) *SIN(RADIANS(90-E2)) *COS(RADIANS($F$2-F2))) *3958.756

What I'm doing is dragging this formula down and it's comparing row 2 (the starting position) to the coordinates on that row.

Then there's the formula which determines the 1st, 2nd and 3rd set:

=INDEX($C$2:$C$1160,MATCH(ROUND(SMALL($H$2:$H$1160,I1+1),6),$H$2:$H$1160,0))

This returns "Mountain Ash" - which is expected as this appears to be the closest in the list. However! This is using your distance calculation formula in column H. If I swap it, to use my distance calculation formula in column G as below:

=INDEX($C$2:$C$1160,MATCH(ROUND(SMALL($G$2:$G$1160,I1+1),6),$G$2:$G$1160,0))

It then shows up as #N/A

There are numerical values only in both columns, however, yours looks like "3.87532" or "0.94214" etc, and mine is distance in miles and is always >0 (whether it's 310 or 0.06)

Really not sure why yours works but mine doesn't, on a different column.

 

[AlanY]

Hi bud,

so if I do this, but use a different way to calculate distance in miles, the formula doesn't work.

Instead of Distance being calculated like this:

=ROUND(((E2-$M$2)^2+(F2-$N$2)^2)^0.5,6)

I have it like this

=ACOS(COS(RADIANS(90-$E$2)) *COS(RADIANS(90-E2)) +SIN(RADIANS(90-$E$2)) *SIN(RADIANS(90-E2)) *COS(RADIANS($F$2-F2))) *3958.756

What I'm doing is dragging this formula down and it's comparing row 2 (the starting position) to the coordinates on that row.

Then there's the formula which determines the 1st, 2nd and 3rd set:

=INDEX($C$2:$C$1160,MATCH(ROUND(SMALL($H$2:$H$1160,I1+1),6),$H$2:$H$1160,0))

This returns "Mountain Ash" - which is expected as this appears to be the closest in the list. However! This is using your distance calculation formula in column H. If I swap it, to use my distance calculation formula in column G as below:

=INDEX($C$2:$C$1160,MATCH(ROUND(SMALL($G$2:$G$1160,I1+1),6),$G$2:$G$1160,0))

It then shows up as #N/A

There are numerical values only in both columns, however, yours looks like "3.87532" or "0.94214" etc, and mine is distance in miles and is always >0 (whether it's 310 or 0.06)

Really not sure why yours works but mine doesn't, on a different column.

it's most likely the rounding off errors, as you can see I rounded off Col. E and the 3 min distances to the same ROUND(...,6) so that they can be matched by the index/match function