Find the nearest campground by postal code

Question

I have a database of Postal Codes in Canada and the scripting to do my searches. But for some reason I am not get the nearest campground based on a Postal Code search. I am doing something wrong with my SQL Query as when I am selecting the campgrounds it displays a business within range by not the nearest.

When I run the SQL Query to get Postal Codes within range order by distance I get this PHP

Array (
[N0C 1K0] => 0
[N0C 1G0] => 9.36
[N0C 1L0] => 9.36
[N0C 1E0] => 9.97
[N0H 2S0] => 12.95
[N0C 1H0] => 13.01
[N0G 1R0] => 15.04
[N0C 1B0] => 19.03
[N0G 2A0] => 19.07
[N0H 1C0] => 19.55
[N0G 1N0] => 20.24
[N0C 1J0] => 21.67
[N0G 2L1] => 25.39
[N0G 2L3] => 25.39
[N0G 2L0] => 25.39
[N0H 1R0] => 25.8 campground found
[N0C 1C0] => 25.83
[N0H 2V0] => 27.43
[N0G 1C0] => 29.1
 ....
[N0G 1A0] => 41.54
[L0N 1G0] => 41.86
[N0G 2J0] => 41.94
[N0G 2V0] => 42.21
[L0M 1P0] => 43.21
[N0H 2P0] => 43.76
[N0G 1Y0] => 44.27
[L0M 1L0] => 44.27
[N0G 2P0] => 44.28 campground found
[L0M 1G0] => 44.48
[N4L 1R4] => 44.49 campground found
)

I would like to get the campground details for 25.8, except I am getting the one for 44.28, help!

SELECT camp_id FROM campgrounds WHERE postal = '".addslashes($key)."' LIMIT 1;

Should I have an ORDER BY? - I did try this. But maybe the wrong order by in my query.

Answer 1

You might want to have a look at the discussion on this SO topic it starts from a dataset of longitudes and lattitudes - but the objective is still all about proximity.

HTH

C.