Help with distance query

wmguk · August 4, 2009

Hey,

I'm writing a script that shows all properties within a radius of X miles from a postcode,

I have the UK postcodes with Latitude and Longitude refs in a DB and the properties have the postcode stored.

For example, If i enter NR19 and show properties within 5 miles.. I need to query the DB, check the postcode, check the distance and include or exclude...

This is what I have, but its not working correctly, its looking at the final results and then either showing all or none..

$wheresql = "WHERE (type = '". $style . "' AND style = '". $offered . "')";
$pricesql = "AND (price BETWEEN '". $min_price . "' AND '" .$max_price. "')";
$roomssql = "AND (rooms BETWEEN '". $min_beds . "' AND '" .$max_beds. "')";
	if ($type == "%") {
	$parkingsql = "";
	} else {
	$parkingsql = "AND parking = '" . $type . "'";
	}
$sqlbuilder = $wheresql . " ". $pricesql . " " . $roomssql . $parkingsql;
$ord = " ORDER BY price ASC";

$prop_sql1 = "SELECT * FROM property " . $sqlbuilder . $ord; 
    $prop_res1 = mysql_query( $prop_sql1 ) or die( "<strong>Query Error</strong>: " . mysql_error() . "<br><strong>Query</strong>: $prop_sql1 <br><br>" );
while($prop_row1 = mysql_fetch_array($prop_res1)) 
	   {
	   $jobpostcodesql = "SELECT x, y FROM postcodes WHERE postcode='".substr($prop_row1['zip'], 0, 4)."'";
	   $jobpostcoderesult = mysql_query($jobpostcodesql) or die(mysql_error());
	   $jobpostcode = mysql_fetch_array($jobpostcoderesult);
	   
	   $shortpostcodesql = "SELECT x, y FROM postcodes WHERE postcode='".substr($town, 0, 4)."'";
           $shortpostcoderesult = mysql_query($shortpostcodesql) or die(mysql_error());
           
	   if ($shortpostcode = mysql_fetch_array($shortpostcoderesult)) {
               $distance = number_format(pow( pow($shortpostcode['x'] - $jobpostcode['x'], 2) + pow($shortpostcode['y'] - $jobpostcode['y'], 2) ,0.5) *0.000621371192, 2);
           } else { 
	       $distance = '-'; 
	   }
}

$prop_sql = "SELECT * FROM property ".$sqlbuilder. " AND " .$radius. " >= " . $distance . $ord; 
    $prop_res = mysql_query( $prop_sql ) or die( "<strong>Query Error</strong>: " . mysql_error() . "<br><strong>Query</strong>: $prop_sql<br><br>" );
$counter = mysql_num_rows($prop_res);

any ideas?

wmguk · August 4, 2009

This must be a simple loop issue, but I dont know how to write the query so that I enter NR19 - and only allow 5 miles, then query the postcode DB, find my postcode, and then show any postcodes within 5 miles...

wmguk · August 5, 2009

does anyone have any ideas on this postcode radius check?

wmguk · August 5, 2009

?

Mark Baker · August 5, 2009

function calculateDistanceFromLatLong($point1,$point2,$uom='km') {
//	Use Haversine formula to calculate the great circle distance
//		between two points identified by longitude and latitude
switch (strtolower($uom)) {
	case 'km'	:
		$earthMeanRadius = 6371.009; // km
		break;
	case 'm'	:
		$earthMeanRadius = 6371.009 * 1000; // km
		break;
	case 'miles'	:
		$earthMeanRadius = 3958.761; // miles
		break;
	case 'yards'	:
	case 'yds'	:
		$earthMeanRadius = 3958.761 * 1760; // miles
		break;
	case 'feet'	:
	case 'ft'	:
		$earthMeanRadius = 3958.761 * 1760 * 3; // miles
		break;
	case 'nm'	:
		$earthMeanRadius = 3440.069; // miles
		break;
}
$deltaLatitude = deg2rad($point2['latitude'] - $point1['latitude']);
$deltaLongitude = deg2rad($point2['longitude'] - $point1['longitude']);
$a = sin($deltaLatitude / 2) * sin($deltaLatitude / 2) +
	 cos(deg2rad($point1['latitude'])) * cos(deg2rad($point2['latitude'])) *
	 sin($deltaLongitude / 2) * sin($deltaLongitude / 2);
$c = 2 * atan2(sqrt($a), sqrt(1-$a));
$distance = $earthMeanRadius * $c;
return $distance;
}   //  function calculateDistanceFromLatLong()

Read $shortpostcode['longitude'] and $shortpostcode['latitude'] for your start location
$localpostcodes = array()
foreach [sELECT postcode, longitude, latitude FROM postcodes] as checkPostCode
   if (calculateDistanceFromLatLong(array('longitude => '$shortpostcode['longitude'],
                                          'latitude' => $shortpostcode['latitude']),
                                    array('longitude => '$checkPostCode['longitude'],
                                          'latitude' => $checkPostCode['latitude']),
                                    'miles') <= 5) {
      $localpostcodes[] = checkPostCode['postcode']
   }
}
$localpostcodes = "'".explode("','",$localpostcodes)."'";
[sELECT * FROM properties WHERE postcode IN $localpostcodes]

wmguk · August 6, 2009

Hey thanks for that,

I've added it in, but I'm having no joy, also I notice its hard coding the 5 mile radius, but I need to offer that as a drop down.

This is my current code:

<?php
$offered= $_REQUEST['offered'];
$style= $_REQUEST['style'];
$town= $_REQUEST['town'];
$radius= $_REQUEST['radius'];
$type= $_REQUEST['type'];
$min_beds= $_REQUEST['min_beds'];
$max_beds= $_REQUEST['max_beds'];
$min_price= $_REQUEST['min_price'];
$max_price= $_REQUEST['max_price'];

if ($min_price == "0") {
$min_price_text = 'No Min';
} else {
$min_price_text = "£" . $_REQUEST['min_price'];
}

if ($max_price == "99999998") {
$max_price_text = 'No Max';
} else {
$max_price_text = "£" . $_REQUEST['max_price'];
}

if ($min_beds == "0") {
$min_beds_text = 'No Min';
} else {
$min_beds_text = $_REQUEST['min_beds'];
}

if ($max_beds == "7") {
$max_beds_text = 'No Max';
} else {
$max_beds_text = $_REQUEST['max_beds'];
}

if ($offered == 'L') {
$offertext = 'for rent';
} elseif ($offered == 'S') {
$offertext = 'for sale';
} elseif ($offered == 'A') {
$offertext = 'for auction';
} else {
$offertext = 'for either rent, sale or auction';
}

if ($radius == '1') {
$radius_text = 'within 1 mile';
} elseif ($radius == '2') {
$radius_text = 'within 2 miles';
} elseif ($radius == '3') {
$radius_text = 'within 3 miles';
} elseif ($radius == '5') {
$radius_text = 'within 5 miles';
} elseif ($radius == '10') {
$radius_text = 'within 10 miles';
} elseif ($radius == '15') {
$radius_text = 'within 15 miles';
} elseif ($radius == '20') {
$radius_text = 'within 20 miles';
} elseif ($radius == '30') {
$radius_text = 'within 30 miles';
} elseif ($radius == '40') {
$radius_text = 'within 40 miles';
} else {
$radius_text = 'Exactly in';
}

if ($style == "R") {
$styletext = 'A residential';
} elseif ($style == "C") {
$styletext = 'A commercial';
}

if ($type == "H") {
$building = 'house';
} elseif ($type == "B") {
$building = 'bungalow';
} elseif ($type == "F") {
$building = 'flat';
} else {
$building = 'property';
}

include "includes/dbconn.php";

if (!$myconn)  
{  
die( 'Could not connect: ' . mysql_error() );  
}
    
$sql = "SELECT * FROM cms WHERE cms_id = '2'"; 
$result = mysql_query( $sql ) or die( "<strong>Query Error</strong>: " . mysql_error() . "<br><strong>Query</strong>: $sql<br><br>" ); 
    while($row = mysql_fetch_array($result)) 
{ 
$title = $row['page_title'];
$keywords = $row['page_keywords'];
$description = $row['page_desc'];
$content = $row['cms_desc']; 
    }

//SHOW ALL PROPERTY
    $sql1 = "SELECT * FROM property WHERE flag = 'y' AND feature = 'n' limit 8"; 
$result1 = mysql_query( $sql1 ) or die( "<strong>Query Error</strong>: " . mysql_error() . "<br><strong>Query</strong>: $sql1<br><br>" );
    
$wheresql = "WHERE (type = '". $style . "' AND style = '". $offered . "')";
$pricesql = "AND (price BETWEEN '". $min_price . "' AND '" .$max_price. "')";
$roomssql = "AND (rooms BETWEEN '". $min_beds . "' AND '" .$max_beds. "')";
	if ($type == "%") {
	$parkingsql = "";
	} else {
	$parkingsql = "AND parking = '" . $type . "'";
	}
$sqlbuilder = $wheresql . " ". $pricesql . " " . $roomssql . $parkingsql;
$ord = " ORDER BY price ASC";

$prop_sql1 = "SELECT * FROM property " . $sqlbuilder . $ord; 
    $prop_res1 = mysql_query( $prop_sql1 ) or die( "<strong>Query Error</strong>: " . mysql_error() . "<br><strong>Query</strong>: $prop_sql1 <br><br>" );
while($prop_row1 = mysql_fetch_array($prop_res1)) 
	   {
	   $postcodesql = "SELECT x, y FROM postcodes WHERE postcode='".substr($prop_row1['zip'], 0, 4)."'";
	   $postcoderesult = mysql_query($postcodesql) or die(mysql_error());
	   $postcode = mysql_fetch_array($postcoderesult);
	   
	   $shortpostcodesql = "SELECT x, y FROM postcodes WHERE postcode='".substr($town, 0, 4)."'";
           $shortpostcoderesult = mysql_query($shortpostcodesql) or die(mysql_error());
           
	   if ($shortpostcode = mysql_fetch_array($shortpostcoderesult)) {
               $distance = number_format(pow( pow($shortpostcode['x'] - $postcode['x'], 2) + pow($shortpostcode['y'] - $postcode['y'], 2) ,0.5) *0.000621371192, 2);
           } else { 
	       $distance = '-'; 
	   }

//NEW
Read $shortpostcode['longitude'] and $shortpostcode['latitude'] for your start location
$localpostcodes = array()
foreach [sELECT postcode, longitude, latitude FROM postcodes] as checkPostCode
   if (calculateDistanceFromLatLong(array('longitude => '$shortpostcode['longitude'],
                                          'latitude' => $shortpostcode['latitude']),
                                    array('longitude => '$checkPostCode['longitude'],
                                          'latitude' => $checkPostCode['latitude']),
                                    'miles') <= 5) {
      $localpostcodes[] = checkPostCode['postcode']
   }
}
$localpostcodes = "'".explode("','",$localpostcodes)."'";
$radius = "postcode IN $localpostcodes";
//END NEW

$prop_sql = "SELECT * FROM property ". $sqlbuilder. " AND " . $radius . $ord; 
    $prop_res = mysql_query( $prop_sql ) or die( "<strong>Query Error</strong>: " . mysql_error() . "<br><strong>Query</strong>: $prop_sql<br><br>" );
$counter = mysql_num_rows($prop_res);
}
?>

That is all outside the HTML on the page...

Then I have

<body><?
function calculateDistanceFromLatLong($point1,$point2,$uom='km') {
//	Use Haversine formula to calculate the great circle distance
//		between two points identified by longitude and latitude
switch (strtolower($uom)) {
	case 'km'	:
		$earthMeanRadius = 6371.009; // km
		break;
	case 'm'	:
		$earthMeanRadius = 6371.009 * 1000; // km
		break;
	case 'miles'	:
		$earthMeanRadius = 3958.761; // miles
		break;
	case 'yards'	:
	case 'yds'	:
		$earthMeanRadius = 3958.761 * 1760; // miles
		break;
	case 'feet'	:
	case 'ft'	:
		$earthMeanRadius = 3958.761 * 1760 * 3; // miles
		break;
	case 'nm'	:
		$earthMeanRadius = 3440.069; // miles
		break;
}
$deltaLatitude = deg2rad($point2['latitude'] - $point1['latitude']);
$deltaLongitude = deg2rad($point2['longitude'] - $point1['longitude']);
$a = sin($deltaLatitude / 2) * sin($deltaLatitude / 2) +
	 cos(deg2rad($point1['latitude'])) * cos(deg2rad($point2['latitude'])) *
	 sin($deltaLongitude / 2) * sin($deltaLongitude / 2);
$c = 2 * atan2(sqrt($a), sqrt(1-$a));
$distance = $earthMeanRadius * $c;
return $distance;
}   //  function calculateDistanceFromLatLong()
?>

Inside the body

then the results are shown:

<?
	   while($prop_row = mysql_fetch_array($prop_res)) 
	   {
		?>
                </p> 
                <table width="973" border="0" cellspacing="0" cellpadding="0">
                <tr>
                  <td height="25" colspan="2" bgcolor="#2B4EA2"> <a href="details.php?id=<?php echo $prop_row['pid']; ?>"><span class="proheader"><? echo substr($prop_row['property_title'], 0, 140); ?>...</span></a></td>
                  </tr>
                <tr>
                  <td><a href="details.php?id=<?php echo $prop_row['pid']; ?>" title="<?php echo $prop_row['property_title']; ?>" alt="<?php echo $prop_row['property_title']; ?>"><img src="./items/<?php echo $prop_row['photo1']; ?>" height="90" hspace="0" vspace="5" border="1" class="border" /></a></td>
                  <td align="left" valign="top"><table width="850" border="0" cellpadding="3" cellspacing="0" class="main">
                    
                    <tr>
                      <td width="200"><strong>Ref:</strong> <? echo $prop_row['owner_name']; ?></td>
                      <td width="150"><strong>Bedrooms:</strong> <? echo $prop_row['rooms']; ?></td>
                      <td width="482" align="right"><strong>Price:</strong> £<? echo $prop_row['price']; ?></td>
                    </tr>
                    <tr>
                      <td colspan="3"><? echo nl2br(substr($prop_row['accom'], 0, 260)); ?>...</td>
                    </tr>
                      
                  </table></td>
                  </tr>
                  <tr>
                    <td height="5" colspan="2" bgcolor="#2B4EA2"><img src="images/admin/spacer.gif" width="1" height="1" /></td>
                  </tr>
                </table>                <? } ?>

Does this look right?

Mark Baker · August 6, 2009

Does this look right?

Not really.

The calculateDistanceFromLatLong() function that I provided is valid PHP.

But the example of how you could use that function that I gave is pseudocode. You can't simply embed that in your PHP code and expect it to work. It's a structured description of how to execute the function, but you need to write the actual PHP code based on that description.

wmguk · August 6, 2009

oh ok, I'm sorry it looked like it should droip straight in...

so

Read $shortpostcode['longitude'] and $shortpostcode['latitude'] for your start location
$localpostcodes = array()
foreach [sELECT postcode, longitude, latitude FROM postcodes] as checkPostCode
   if (calculateDistanceFromLatLong(array('longitude => '$shortpostcode['longitude'],
                                          'latitude' => $shortpostcode['latitude']),
                                    array('longitude => '$checkPostCode['longitude'],
                                          'latitude' => $checkPostCode['latitude']),
                                    'miles') <= 5) {
      $localpostcodes[] = checkPostCode['postcode']
   }
}
$localpostcodes = "'".explode("','",$localpostcodes)."'";
$radius = "postcode IN $localpostcodes";

I'm just slightly out of my depth, and I'm not for one second asking you to write the whole code, however could you tell me where i need to focus as the code above looks like it should work?

Mark Baker · August 6, 2009

I'm just slightly out of my depth, and I'm not for one second asking you to write the whole code, however could you tell me where i need to focus as the code above looks like it should work?

Read $shortpostcode['longitude'] and $shortpostcode['latitude'] for your start location

You're already doing that here

$shortpostcodesql = "SELECT x, y FROM postcodes WHERE postcode='".substr($town, 0, 4)."'";
$shortpostcoderesult = mysql_query($shortpostcodesql) or die(mysql_error());

What I don't know is which value (x or y) is longitude and which is latitude. I can guess, but only you know that for certain

foreach [sELECT postcode, longitude, latitude FROM postcodes] as checkPostCode

You need to executes a SQL query 'SELECT postcode, longitude, latitude FROM postcodes' from your database. Again, x and y rather than longitude and latitude, but you need to execute the query then loop through the results.

   if (calculateDistanceFromLatLong(array('longitude => '$shortpostcode['longitude'],
                                          'latitude' => $shortpostcode['latitude']),
                                    array('longitude => '$checkPostCode['longitude'],
                                          'latitude' => $checkPostCode['latitude']),
                                    'miles') <= 5) {

Substitute in the correct values from $shortpostcode, i.e. x or y for the latitude and longitude, and replace

<= 5

with

<= $radius

$localpostcodes[] = checkPostCode['postcode']

I've no idea what checkPostCode is or does.

$radius = "postcode IN $localpostcodes";

The list of "IN" values in a SQL query needs to be wrapped in brackets

$radius = "postcode IN ($localpostcodes)";

... but why overwrite the value of $radius, rather than use a new variable like $radius_in_postcodes

Sign In

Help with distance query

Recommended Posts

wmguk

Link to comment

Share on other sites

wmguk

Link to comment

Share on other sites

wmguk

Link to comment

Share on other sites

wmguk

Link to comment

Share on other sites

Mark Baker

Link to comment

Share on other sites

wmguk

Link to comment

Share on other sites

Mark Baker

Link to comment

Share on other sites

wmguk

Link to comment

Share on other sites

Mark Baker

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information