how to calculate distance between 2 postcodes.

jasonc · November 30, 2010

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?

<!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 = "AB12";
$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

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'),
(1, 'Lisa', '', 'Smith', 'Female', 'SM2 1EA'),
(1, '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');

fenway · December 3, 2010

TLDR -- what exactly are you trying to do?

Sign In

how to calculate distance between 2 postcodes.

Recommended Posts

jasonc

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information