enter postcode, see nearest sports halls

[jarvis]

Hi,

 

i've the following basic script which tells me the distance between 2 postcodes - which you need to enter the postcodes for. So one could be my house and one the nearest sports hall

 

All postcode info is stored in a db table

 

What I'd like to do is alter this for sports halls within the uk. When you enter your postcode, it returns the 5 nearest sports halls.

 

I've no idea where to start. My code is:

 

<?php 
require_once('../mysql_connect.php');// Connect to the db 

if (isset($_POST['submitted'])) { // Handle the form.

if (!empty($_POST['start'])) {
	$start = escape_data($_POST['start']);
} else {
	$start = FALSE;
	echo '<p>Please enter a valid postcode!</p>';
}	

if (!empty($_POST['finish'])) {
	$finish = escape_data($_POST['finish']);
} else {
	$finish = FALSE;
	echo '<p>Please enter a valid postcode!</p>';
}

#Convert the post code to upper case and trim the variable
$start = strtoupper(trim($start));
$finish = strtoupper(trim($finish));

#Remove any spaces
$start = str_replace(" ","",$start);
$finish = str_replace(" ","",$finish);

#Trim the last 3 characters off the end
$start = substr($start,0,strlen($start)-3);
$finish = substr($finish,0,strlen($finish)-3);

#query the db
$query_start = "SELECT latitude, longitude FROM postcodes WHERE postcode = '$start' LIMIT 1";

$result_start = mysql_query ($query_start);

$num = mysql_num_rows ($result_start); // How many users are there?

if ($num > 0) { // If it ran OK, display the records.

	while ($row = mysql_fetch_array ($result_start, MYSQL_ASSOC)) {
		#Assign variables
		$lat1 = $row["latitude"];
		echo $row["latitude"] .' - lat<br/>';
		$long1 = $row["longitude"];
		echo $row["longitude"] .' - lat<br/>';
	}

} else { 
	echo '<p>post code not found</p>';
}	

echo $query_start;
echo '<hr>';

$query_finish = "SELECT latitude, longitude FROM postcodes WHERE postcode = '$finish' LIMIT 1";

$result_finish = mysql_query ($query_finish);

$num = mysql_num_rows ($result_finish); // How many users are there?

if ($num > 0) { // If it ran OK, display the records.

	while ($row = mysql_fetch_array ($result_finish, MYSQL_ASSOC)) {
		#Assign variables
		$lat2 = $row["latitude"];
		echo $row["latitude"] .' - lat<br/>';
		$long2 = $row["longitude"];
		echo $row["longitude"] .' - lat<br/>';
	} 

} else { 
	echo '<p>post code not found</p>';
}	

echo $query_finish;	


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

	#$earth = 6371; #km change accordingly
	$earth = 3960; #miles

	#Point 1 cords
	$lat1 = deg2rad($lat1);
	$long1= deg2rad($long1);

	#Point 2 cords
	$lat2 = deg2rad($lat2);
	$long2= deg2rad($long2);

	#Haversine Formula
	$dlong=$long2-$long1;
	$dlat=$lat2-$lat1;

	$sinlat=sin($dlat/2);
	$sinlong=sin($dlong/2);

	$a=($sinlat*$sinlat)+cos($lat1)*cos($lat2)*($sinlong*$sinlong);

	$c=2*asin(min(1,sqrt($a)));

	$d=round($earth*$c);

	return $d;

}

	#Returns the distance in miles
$distance = getDistance($lat1, $long1, $lat2, $long2);


}
echo '<p>'.$distance.'</p>';
?>
<form method="post" action="index.php">
start: <input type="text" name="start" value="<?php if (isset($_POST['start'])) echo $_POST['start']; ?>" />
finish: <input type="text" name="finish" value="<?php if (isset($_POST['finish'])) echo $_POST['finish']; ?>" />
<input type="submit" name="submit" value="Go" />
<input type="hidden" name="submitted" value="TRUE" />
</form>

 

Any help is very much appreciated!

 

Thanks in advanced

[ignace]

SELECT latitude, longitude FROM postcodes WHERE postcode BETWEEN '$start' AND '$finish' LIMIT 5

 

Won't work?

[jarvis]

Thanks ignace. Would that work though? Is it really that damn simple?

[ignace]

Thanks ignace. Would that work though? Is it really that damn simple?

 

No that doesn't work, too bad You'll need an algorithm one like Dijkstra wrote.

[jarvis]

Thought as much. Is it rather complex to do what I'm after? Am still quite new to PHP

[ignace]

Is it rather complex to do what I'm after?

 

Depends on what you know about Graph Theory you can find an example at:

 

http://en.wikipedia.org/wiki/Dijkstra%27s_algorithm

 

On this page you will find under Pseudocode a shorter version which would solve what you are after.

[jarvis]

Yeah I found the reference on wikipedia - to be honest, it meant diddly to me and went over my head. Is there no other way?

[ignace]

Yeah I found the reference on wikipedia - to be honest, it meant diddly to me and went over my head. Is there no other way?

 

Sure I can think of atleast one other:

 

Store all postcodes in a table (or those that matter), store all sport halls (again those that matter). Give each sport hall it's postcode through a reference table (a table with the fields: postcodes_id and sporthalls_id) create a secondary reference table which tells which other postcodes is a neighbour of a specific postcode.

 

When you select the sport halls use it's postcode and all neighbouring postcodes and show these sport halls.

[ignace]

Something like (incomplete):

 

CREATE TABLE postcodes (
    postcodes_id SMALLINT NOT NULL, #range -32k to 32k
    PRIMARY KEY (postcodes_id)
);

CREATE TABLE sporthalls (
    sporthalls_id SMALLINT NOT NULL AUTO_INCREMENT, #range -32k to 32k
    sporthalls_postcodes_id SMALLINT,
    sporthalls_title VARCHAR(32),
    PRIMARY KEY (sporthalls_id)
);

CREATE TABLE postcodes_neighbours (
    postcodes_neighbours_postcode SMALLINT NOT NULL,
    postcodes_neighbours_neighbour SMALLINT NOT NULL,
    PRIMARY KEY (postcodes_neighbours_postcode, postcodes_neighbours_neighbour)
);

[ignace]

Query:

 

SELECT *
FROM sporthalls
WHERE sporthalls_postcodes_id = 3400
  OR sporthalls_postcodes_id IN (
    SELECT postcodes_neighbours_neighbour
    FROM postcodes_neighbours
    WHERE postcodes_neighbours_postcode = 3400
);

 

DB Structure:

 

CREATE TABLE IF NOT EXISTS `postcodes` (
  `postcodes_id` smallint(6) NOT NULL,
  PRIMARY KEY  (`postcodes_id`)
);

INSERT INTO `postcodes` (`postcodes_id`) VALUES
(3400),
(3401),
(3402),
(3403),
(3404),
(3500),
(3501),
(3502),
(3503),
(3504);

CREATE TABLE IF NOT EXISTS `postcodes_neighbours` (
  `postcodes_neighbours_postcode` smallint(6) NOT NULL,
  `postcodes_neighbours_neighbour` smallint(6) NOT NULL,
  PRIMARY KEY  (`postcodes_neighbours_postcode`,`postcodes_neighbours_neighbour`)
);

INSERT INTO `postcodes_neighbours` (`postcodes_neighbours_postcode`, `postcodes_neighbours_neighbour`) VALUES
(3400, 3401),
(3400, 3402),
(3400, 3403),
(3400, 3404),
(3500, 3501),
(3500, 3502),
(3500, 3503),
(3500, 3504);

CREATE TABLE IF NOT EXISTS `sporthalls` (
  `sporthalls_id` smallint(6) NOT NULL auto_increment,
  `sporthalls_postcodes_id` smallint(6) default NULL,
  `sporthalls_title` varchar(32) default NULL,
  PRIMARY KEY  (`sporthalls_id`)
);

INSERT INTO `sporthalls` (`sporthalls_id`, `sporthalls_postcodes_id`, `sporthalls_title`) VALUES
(1, 3400, '#1 Location 3400'),
(2, 3401, '#1 Location 3401'),
(3, 3402, '#1 Location 3402'),
(4, 3403, '#1 Location 3403'),
(5, 3404, '#1 Location 3404'),
(6, 3500, '#1 Location 3500'),
(7, 3501, '#1 Location 3501'),
(8, 3502, '#1 Location 3502'),
(9, 3503, '#1 Location 3503'),
(10, 3504, '#1 Location 3504');

 

Result:

 

sporthalls_id       sporthalls_postcodes_id          sporthalls_title
1 	                3400                                  #1 Location 3400
2                      3401                                  #1 Location 3401
3                      3402                                  #1 Location 3402
4                      3403                                  #1 Location 3403
5                      3404                                  #1 Location 3404

[jarvis]

Thanks ignace, however, this means I'd need to work out the distances from the locations. Hence just wanting to do it via postcode.

[ignace]

Thanks ignace, however, this means I'd need to work out the distances from the locations. Hence just wanting to do it via postcode.

 

How would you do it otherwise? Now you just keep a table that matches a postal code to its neighbouring postal codes if you don't then how would you know how far or how close postal code 3400 lies from 3500?

[markwillis82]

SELECT 
`table`.*,
ROUND( SQRT( POW((69.1 * ( 50.734501 - `table`.`lat`)), 2) + POW((53 * ( -2.98377 - `table`.`long`)), 2)), 1) AS `distance` 

FROM 
`table`

WHERE 
`table`.`lat`< (50.734501+0.12) AND 
`table`.`lat` > ( 50.734501-0.12) AND 
`table`.`long` < (-2.98377+0.12) AND 
`table`.`long` > (-2.98377-0.12) 

ORDER BY `distance` ASC

 

if you have the lat/long of the entered postcode, replace (50.734501, -2.98377) with your own. the 0.12 is 10 miles - so 5 miles is 0.6

 

The `distance` column is in miles.

 

Hope this helps

Mark Willis

[jarvis]

Thanks markwillis82, that seems like it may be along the right lines.

 

Am just trying to work out how my code can tie in with yours.

 

Thanks

[jarvis]

Ah I think I've sussed this,

echo $lat1;
echo $long1;
$query="
SELECT *,
ROUND( SQRT( POW((69.1 * ( $lat1 - latitude)), 2) + POW((53 * ( $long1 - longitude)), 2)), 1) AS `distance` 

FROM 
postcodes

WHERE 
latitude< ($lat1+0.12) AND 
latitude > ($lat1-0.12) AND 
longitude < ($long1+0.12) AND 
longitude > ($long1-0.12) 

ORDER BY `distance` ASC";
$result = mysql_query ($query);

$num = mysql_num_rows ($result); // How many users are there?

if ($num > 0) { // If it ran OK, display the records.

	while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) {
		#Assign variables
		$test = $row["distance"];
		echo $test.'</br/>';
	} 

} else { 
	echo '<p>post code not found</p>';
}

If I get the towns into the db, I guess I can return the town with the distance? At the mo it produces:

50.8640.5830.0

1.4

1.9

2.0

5.4

6.5

7.4

8.8

9.4