how to calculate distance between 2 postcodes.

jasonpc1 · 11-30-2010, 09:06 PM

1) Project Details: (be as specific as possible):

I have the following code below which works but I would like it done using one query, or two at best to prevent the current code from freezing out the server.

below is my testing script i have been working on but as you can see this will take a lot of resources on the server if i was to search say over 5000 members ?!

i would like some help in finding a way to do a single query and not grabbing each users details and checking each and everyone of them one by one for the matches.

is this possible?

As for costs, please PM me, if your quote is unrealistic you will not get a look in.

I have already paid £40 for this one page! but I know that once I go live and get members it will cause problems with the server.

Code:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<?
  function distance($pcodeA,$pcodeB) {
    $db_code1 = explode(' ',$pcodeA); $db_code2 = explode(' ',$pcodeB);

    $postcode1 = mysql_fetch_assoc(mysql_query("SELECT * FROM `postcodes` WHERE postcode='$db_code1[0]' LIMIT 1"));
    $postcode2 = mysql_fetch_assoc(mysql_query("SELECT * FROM `postcodes` WHERE postcode='$db_code2[0]' LIMIT 1"));

    $p1_gridN = $postcode1['grid_n']; $p1_gridE = $postcode1['grid_e'];
    $p2_gridN = $postcode2['grid_n']; $p2_gridE = $postcode2['grid_e'];

    $distance_n=$p1_gridN-$p2_gridN; $distance_e=$p1_gridE-$p2_gridE;
    $hypot=sqrt(($distance_n*$distance_n)+($distance_e*$distance_e));

    $distance = array("".round($hypot)."","".round(0.62*($hypot/1000),1)."");
    return $distance;
  }

include('connection.php');

// details of the search to do.
$searchGender = "Male";
$searchPostcode = "SM1";
$searchDistance = "2000";  // 5 miles

// get all users details.
  $getUsers = mysql_query("SELECT * FROM user_accounts WHERE gender='$searchGender' ORDER BY account_id ASC");
  $number = 0;
  // check if a user is within the 'searchDistance' of the '$user['postcode']', if so display their details.
  while($user = mysql_fetch_assoc($getUsers)) {
   $distance = distance($searchPostcode,$user['postcode']);	//	say $user['postcode'] = "AB12"
   echo("" . $user['postcode'] . "  " . $searchDistance . " miles of their postcode.<br>");

		 if($distance[1] < $searchDistance && $distance[1] > 0 && $distance[0] > 0) { $number++;
		   echo 'Fullname: ',$user['firstname'],' ',$user['middlename'],' ',$user['lastname'],' ',$user['gender'],"\n";
		   echo '<div id="distanceDiv'.$user['account_id'].'">'.$distance[1].":\n".
				'</div><br>'."\n\n";
		 }
  }
?>
</body>
</html>

an extract of my mysql's

Code:

CREATE TABLE IF NOT EXISTS `user_accounts` (
  `account_id` int(100) NOT NULL auto_increment,
  `firstname` varchar(50) NOT NULL,
  `middlename` varchar(50) NOT NULL,
  `lastname` varchar(50) NOT NULL,
  `gender` varchar(6) NOT NULL,
  `postcode` varchar(10) NOT NULL,
  PRIMARY KEY  (`account_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

INSERT INTO `user_accounts` (`account_id`, `firstname`, `middlename`, `lastname`, `gender`, `postcode`) VALUES
(1, 'Mark', '', 'Smith', 'Male', 'SM1 1EA'),
(2, 'Lisa', '', 'Smith', 'Female', 'SM2 1EA'),
(3, 'Simon', '', 'Smith', 'Male', 'SM3 1EA');




CREATE TABLE IF NOT EXISTS `postcodes` (
  `postcode_id` int(11) NOT NULL auto_increment,
  `postcode` tinytext NOT NULL,
  `grid_n` varchar(15) NOT NULL default '',
  `grid_e` varchar(15) NOT NULL default '',
  `latitude` varchar(15) NOT NULL default '',
  `longitude` varchar(15) NOT NULL default '',
  PRIMARY KEY  (`postcode_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2823 ;


INSERT INTO `postcodes` (`postcode_id`, `postcode`, `grid_n`, `grid_e`, `latitude`, `longitude`) VALUES 
(2295, 'SM1', '525900', '164600', '51.366', '-0.191'),
(2296, 'SM2', '525600', '163100', '51.352', '-0.197'),
(2297, 'SM3', '524400', '165000', '51.37', '-0.213'),
(2298, 'SM4', '525300', '167500', '51.392', '-0.199'),
(2299, 'SM5', '527600', '164800', '51.368', '-0.166'),
(2300, 'SM6', '529300', '164000', '51.36', '-0.143'),
(2301, 'SM7', '525400', '159800', '51.323', '-0.2');

note: there are somewhere like 3000 postcodes in the original mysql file i got from google searching. thats why i supplied just ashort version.

slappyjaw · 12-27-2010, 05:20 AM

I can do this for you, it seems like a pretty easy fix. If i can have a better look at it i can do it for a reasonable price. Please email me at me@slappyjaw.com

jasonpc1 · 12-27-2010, 10:48 AM

project already completed