Get closest PostCodes from Database - Just need closest function please

[scotchegg78]

Hi chaps

 

I have a DB up, and have table fields PostCode, Area, Lat and Long.

 

Lat and Long been the key latitutes and Longtitudes. I have the function to to get the distance

 

function getDistance($lat1, $long1, $lat2, $long2);

 

But I want an effecient way of getting the closets postcode from my DB without having to run through the entire db each time comparing against every postcode. As i see it I need to know the biggest distance from 1 post code to its closet neighbour as a radious, then when i search a postcode i could just seelct all that have Lat and Longs within range  and then return the shortest?

 

Make sense?sound right? Any ideas on how to get tackle this, my heads spinning today

 

 

[GingerRobot]

For a rough estimate of distance and to find the closest match to a given post code, you should be able to do:

 

SELECT SQRT(pow(longitude-(SELECT longitude FROM yourtable WHERE postcode='XXXX XXX'),2)+pow(latitude-(SELECT latitude FROM yourtable WHERE postcode='XXXX XXX'),2)) as distance FROM yourtable ORDER BY distance DESC LIMIT 1

 

For more accurate results, see here for the required formulae.

[scotchegg78]

Hi Thanks alot for the SQL code.

 

This is what I have done so far, but its not using your code to improve performance yet..

 

$firstpc = strtoupper(preg_replace("/[^a-zA-Z0-9]/","", $_POST[first]));

$query = 'SELECT `Lat`, `Long` FROM `ukpostcodes` WHERE `PostCode`="'.$firstpc.'";';
$result = mysql_query($query);
$first = mysql_fetch_row($result);

$pcqry = "SELECT postcode, regionID FROM cr_office";
$result = mysql_query($pcqry);

$distance = 1000;

while ($officepc = mysql_fetch_assoc($result)){

	$opc = (explode(" ",$officepc['postcode']));
	$officeqry = 'SELECT `Lat`, `Long` FROM `ukpostcodes` WHERE PostCode="'.$opc[0].'";';
	$result2 = mysql_query($officeqry);
	$second = mysql_fetch_row($result2);

	$newdistance = getDistance($first[0], $first[1], $second[0], $second[1]);
	echo $opc[0] ." ". $newdistance ."<br>";

	if($newdistance < $distance)
	{	$distance = $newdistance;
			$bestregion =$officepc['regionID'];
	}

}

$emailsql = "SELECT regionEmailAdmin FROM cr_region WHERE regionID ='".$bestregion."'";

$result = mysql_query($emailsql);
$row = mysql_fetch_row($result);
$email = $row[0];

echo "email address : " . $email;	

 

my heads spinning, I will take a look at this later and see if i can work out how to use your sql to improve things.

I trust it will likely replace the need for the while loop?

[rarebit]

Here's what I got the other day...

[scotchegg78]

thanks i will look at it, however rather than just pick 50 miles radius at random, would it not be better to have worked out already the limit based on the max possible distance from any given postcode to a destination? Why have 50 miles if it could only be 22 for example?

[BlueSkyIS]

so you're thinking a database consisting of the distance from every post code to every possible destination? i guess this might be feasible if you've got few destinations.

[scotchegg78]

hi blue sky,

 

no not quite, all i need to know is one distance, the biggest distance between two area offices, half this and then i have a raduis that should always return at least one result no?

 

I should also point out, i have two tables, the area offices with their address and postcodes, and a seperate post code reference table with postcodes and lat and longs.

 

i am wondering if its better to store the office long and lat in the office table when an entry is made, rather than use the reference table each time and perfoms some sort of where on the conditons and also a join? with me?

[scotchegg78]

This may make it all a little clearer...

 

Tables

 

cr_office( regionID, postcode)

cr_region(regionID,regionEmail)

ukpostcodes(postcode,Lat,Long)

 

I want to get the region email the office falls into based on the closes office from the users postcode.

 

1)So I am thinking I need to get the user postcode and get its Lat and Long from the ukpostcodes table.

2)Then compare users Lat and Long against the Lat and Long of the cr_office entries, using the ukpostcode as a refence table for all postcodes in the cr_office table?

3) would it also be better to search only for entries within the radius of the biggest distance appart from offices?

 

4?) I also wonder if its better as i say t store the lat and longs in the office on edit or insert of rows, to save referncing the postcode table each time?