Long/Lat Distance Query Entire Database

Question

I found this question on Stack - Measuring the distance between two coordinates in PHP

The answer seems to be perfect for me in many ways but I'm having one problem. I'm in the middle of designing a CRM and I want there to be a postcode radius search option. My problem is how do I pass what could be 1,000's of records against this query and display the results in HTML?

This is not a simple question - but if you, instead of using a radius, simply created a square with sides of length 2r, and your postcode in the center, you could calculate the upper and lower bounds for long/lat and then find the records that fall between the long/lat bounds - it would still be an expensive query. Depending on the specific DB you are using you could optimize. — Gratus D., Oct 17 '17 at 16:55
Have a look at the [MySQL Spatial Extensions](https://dev.mysql.com/doc/refman/5.6/en/spatial-extensions.html) but keep in mind MySQL is not the ideal database for this sort of work. In practice Postgres + [PostGIS](http://www.postgis.net) is often significantly better. — tadman, Oct 17 '17 at 17:09
Thank you both really appreciate your help. I've found a query that I'll post above that does this within 2 seconds which is fine for us but I understand not ideal for others. — James, Oct 17 '17 at 17:11
Provided it as an answer with a full PHP script and links to sources I used — James, Oct 17 '17 at 18:00

score 0 · Answer 1 · answered Oct 17 '17 at 17:53

So a solution I have found by combining a few sources is this (3959 is for miles use 6371 for km). I have included a PHP file and a HTML table below in case this helps anybody else with this problem.

To start with you can get the data you need from here:

https://www.freemaptools.com/download-uk-postcode-lat-lng.htm

I would recommend using that site to create a basic MySQL table and import the data but be warned this will take a little while! Also either before or after I would run a query to delete all spaces so AA11 1AA becomes AA111AA, this will make form validation a lot simpler.

This mini walk through uses both the data and table construct from that link.

First of all you will need a user input form that will look something like this:

<form action="" method="POST">
    <table>
        <tr>
            <td>Distance:</td>
            <td><input type="number" id="distance" name="distance" required></td>
        </tr>
        <tr>
            <td>Miles:<br>Kilometres:</td>
            <td><input type="radio" id="unit" name="unit" value="3959" checked><br><input type="radio" id="unit" name="unit" value="6371"></td>
        </tr>
        <tr>
            <td>Postcode:</td>
            <td><input type="text" id="postcode" name="postcode" required></td>
        </tr>
        <tr>
            <td></td>
            <td><input type="submit" id="submit" name="submit" value="Submit"></td>
        </tr>
    </table>
</form>

This form submits to the same page so in your .php file where this sits you will need to include this code to convert the postcode to a long / lat value:

<?php include 'credentials.php';

$distance = $_POST['distance'];
$unit = $_POST['unit'];
$postcode = str_replace(' ','',$_POST['postcode']);

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT latitude, longitude FROM postcodelatlng WHERE postcode LIKE '$postcode'";

$result = $conn->query($sql);

if ($result->num_rows > 0) {

    while($row = $result->fetch_assoc()) {
        $latitude = $row['latitude'];
        $longitude = $row['longitude'];
    }
} else {
    echo "Postcode not found";
}
$conn->close();
?>

Make sure your have a credentials.php file to provide the connection details for your database for this to work.

Then directly underneath this php script we are going to put a second one which will return all postcodes within a certain radius. You can then adapt this script to meet your needs. I will personally be passing this script against a different and much smaller table to check if any records are within a certain radius on the CRM but for now and for demonstration purposes just keeping it simple!

<?php
$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT *,
($unit * acos( cos( radians($latitude) ) * cos( radians(latitude) ) * cos( radians(longitude) - radians($longitude)) + sin(radians($latitude)) * sin(radians(latitude)) ))
as distance
FROM postcodelatlng
HAVING distance < $distance";

$result = $conn->query($sql);

if ($result->num_rows > 0) {

    while($row = $result->fetch_assoc()) {
        echo "" . $row['postcode']. "<br>";
    }
} else {
    echo "<br>0 results";
}
$conn->close();
?>

Main thanks to this post which this is used from:

Fastest Way to Find Distance Between Two Lat/Long Points

miknik · Answer 2 · 2017-10-18T00:45:44.030

0

What sort of execution time do you get?

I made my own vehicle tracker and used a Point column in my db to store each recorded lat/lng as spacial data, along with a spacial index on the column.

I've currently got 13,000 sets of coordinates saved in the database and given another set of coordinates the below query returns my 50 closest along with distance to each in 0.009s

    SELECT *, (GLength(LineString(location, GeomFromText('POINT(55.0177 -3.0968)')))) 
    AS distance
    FROM blackbox
    ORDER BY distance ASC
    LIMIT 50

Edit I just tried your query with the addition of order by distance and it took 0.024s, adding your HAVING distance < x to my query got me down to 0.008s

edited Oct 18 '17 at 00:45

answered Oct 18 '17 at 00:17

miknik

5,748
1
10
26

Sounds like we're pretty evenly matched then! I Hasten to add this isn't all my work it's a combination of sources with my own tweaks here and there to help anybody who had the same question I did. I think the main benefit of the whole solution is converting postcodes to long/lat. I'll try running your query later and see what I get. – James Oct 18 '17 at 08:20

Long/Lat Distance Query Entire Database

2 Answers2