Postcode Radius Search

Question

Ok, so I was asked to create a postcode search script. I'm still quite new to the more advanced php/mysql. All i'm needing is help simplifying this script. The Postcode database is the entire UK postcode sql database 1.3m rows. So when I'm using the script to get all postcodes within a radius it takes a long time. Is there a way to simplify it. Possibly even use Java/Ajax?

<?php

$postcode = str_replace( '+', '%20', $postcode );
$postcode = str_replace( '!"£$^&*()?<>', '', $postcode );

include ('config.php');

$sqlstring = "SELECT * FROM postcodelatlng WHERE postcode LIKE '".$postcode." %' ";
$result = mysql_query($sqlstring);   

$row = mysql_fetch_assoc($result);

$lng = $row["longitude"] / 180 * M_PI;
$lat = $row["latitude"] / 180 * M_PI;

mysql_free_result($result);    

$sqlstring2 = "SELECT DISTINCT 
  postcodelatlng.postcode,
  (
    6367.41 * SQRT(
      2 * (
        1- COS(RADIANS(postcodelatlng.latitude)) * COS(".$lat.") * (
          SIN(RADIANS(postcodelatlng.longitude)) * SIN(".$lng.") + COS(RADIANS(postcodelatlng.longitude)) * COS(".$lng.")
        ) - SIN(RADIANS(postcodelatlng.latitude)) * SIN(".$lat.")
      )
    )
  ) AS Distance 
FROM
  postcodelatlng AS postcodelatlng 
HAVING Distance <= '".$radius."'
ORDER BY Distance ";



$result1 = mysql_query($sqlstring2) or die('query failed: ' . mysql_error());
    while($row = mysql_fetch_array($result1)){
        $searchlets = "SELECT * FROM property_details WHERE postcode = '".$row['postcode']."' ";
                $getresult = mysql_query($searchlets);
                    while($row2 = mysql_fetch_array($getresult)) {

                        echo ' Output here if Just Postcode was used ';

   echo '<hr/>';
                    }


    }

// This seraches if only city name was used
$searchlets2 = "SELECT * FROM property_details WHERE street = '".$postcode."' ";
                $getresult2 = mysql_query($searchlets2);
                    while($row3 = mysql_fetch_array($getresult2)) {
                        echo ' Output Here If just city name was searched ';

   echo '<hr/>';
                    }

mysql_close($con); 

?>

Java/Ajax?!? Sure you don't mean javascript? And that's not necessarily inherently faster, the time taken is that expensive haversine formula on every single row — Mark Baker, May 31 '16 at 20:06
Precalculate the latitude/longitde for all your postcodes, and store that in the database; index those columns in your database; use a bounding box for the search, reducing the number of records that you need to execute the expensive haversine calculation for — Mark Baker, May 31 '16 at 20:07
@EdHeal - Believe me, I'm very aware how UK postcodes work, and how they relate to geospatial indexes. I'm also aware that OS provide a single centrepoint lat/long for a postcode in their postcode database, so the precise shape is less significant — Mark Baker, May 31 '16 at 20:11
If you're on MySQL 5.7 you might want to use the new spatial functions to find points (on a sphere the radius of the earth): http://dev.mysql.com/doc/refman/5.7/en/spatial-convenience-functions.html#function_st-distance-sphere — Ray, May 31 '16 at 20:11
[How to create a bounding box](http://stackoverflow.com/questions/5236921/geo-search-distance-in-php-mysql-performance/5238667#5238667) — Mark Baker, May 31 '16 at 20:12
@MarkBaker - Using bounding boxes is not the total solution, It can enable one to be able to select a subset of postcodes to consider that are potentially within the arrangement — Ed Heal, May 31 '16 at 20:16
@EdHeal - I believe that was my point about bounding boxes, and the point of the answer that I linked — Mark Baker, May 31 '16 at 20:42

score 2 · Answer 1 · edited May 23 '17 at 11:52

2

You could try a bounding box to filter out the results. Then you could use the harvesine formula for better accuracy. Or you could try a quadkey, a.k.a space-filling-curve like. Basically it's a spatial index and reduces the dimension. It's a LSH algorithm. Read my answer in this question:Geo-Search (Distance) in PHP/MySQL (Performance)

edited May 23 '17 at 11:52

Community

1
1

answered May 31 '16 at 20:07

Micromega

12,486
7
35
72

Postcode Radius Search

1 Answers1