Page 1 of 1

Find Closest Town from Postcode

Posted: Wed Jan 07, 2009 4:54 pm
by shaundunne
Im looking to find the closest UK towns - listed by distance when the user submits their postcode.

I have 3 MySql tables.

Table A includes:

Full postcode
Northing Londitude
Easting Londitude


Table B Includes:
TownID
Northing Londitude
Easting Latitude

Table C
Town ID
Town Name

I don't even know where to start with the mathmatics of this - been looking around but can't really find anything. Can anyone help out?

I need to List all of the towns within thirty miles.

Thanks

Re: Find Closest Town from Postcode

Posted: Wed Jan 07, 2009 5:11 pm
by Randwulf
Ok, well, first we need to figure how many miles one degree of latitude and one degree of longitude is. One degree of latitude is 69.04 if we can trust my source (probably want to confirm it). Longitude, unfortunately, varies, but not too erraticly. You probably want to narrow it down more than this, but this could get you started:

30 degrees North, (approximately Houston, Texas) a degree of longitude is 59.96 statute miles, 5274 feet per minute (almost equal to a statute mile), 88 feet per second.

35 degrees North, (approximately Albuquerque, New Mexico) a degree of longitude is 56.73 statute miles, 4992 feet per minute, 83.2 feet per second.

40 degrees North, (Kansas/Nebraska border), a degree of longitude is 53.06 statute miles, 4669 feet per minute, 77.8 feet per second.

45 degrees North, (Montana/Wyoming border), a degree of longitude is 49.00 statute miles, 4312 feet per minute, 71.87 feet per second.

49 degrees North (US/Canada national boundary), a degree of longitude is 45.40 statute miles, 3995 feet per minute, 66.59 feet per second.

50 degrees North (approximately Powell River, BC, Medicine Hat, Alberta, and Winnipeg, Manitoba), a minute of longitude is 44.55 statue miles, 3920 feet (1195 meters) per minute, 65.34 feet (19.9 meters) per second.

55 degrees North (approximately Ketchikan, Alaska and Dawson Creek, BC) a degree of longitude is 39.77 statute miles, 3500 feet (1066.8 meters) per minute, 58.33 (17.78 meters) per second.

Finally, 60 degrees North (southern border of the Northwest Territories), a degree of longitude is 34.67 statute miles, 3051 feet (930 meters) per minute, 50.85 feet (15.5 meters) per second.
Once you have those as close as you need, then you need to turn the longitude and latitude values into decimal numbers, rather than degrees and minutes (if that's how you have it). There are 60 minutes in a degree I believe, so 5 degrees and 30 minutes would be 5.5 degrees. You then need to subtract the longitude of the post code town from that of the other towns, and do the same thing with latitude. Then you need to multiply these numbers by the miles per degree of longitude and latitude.

Then it's just the Pythagorean Theorem. Take the square route of
(difference in longitude)^2 + (difference in latitude)^2
And that's the distance in miles between the two places. I hope that helps.

EDIT: That may have been kind of confusing? I'll give an example.

If the post code is at 30 degrees and 20 minutes longitude and 20 degrees latitude, and the town currently in question is at 30 degrees longitude and 50 degrees latitude, we'd do the following.

30.33333333333 - 30 = .333333333
20 - 50 = -30

.333333333333 * 59.96 (thats how many miles are in a longitudinal degree at that longitude) = 19.986
-30 * 69.04(same thing but for latitude) = -2071.2

Now for Pythag's. (19.986^2) + (-2071.2^2) = 4289869

The square route of 4289869 is the distance between the two places. (Windows calc = no sqrt :( )