Only show results within range chosen by user

Question

I have a database table which stores long and lat coordinates. The user types in their postcode and should select a range for example 5 miles, then all the coordinates stored in the database which are within 5 miles should displayed. I have managed to convert the postcode the user types into coordinates but I am finding it difficult to do the next part to show only results within chosen miles.

<?php
$postcode = urlencode("$_POST[postcode]"); // post code to look up in this case status however can easily be retrieved from a database or a form post
$request_url = "http://maps.googleapis.com/maps/api/geocode/xml?address=".$postcode."&sensor=true"; // the request URL you'll send to google to get back your XML feed
$xml = simplexml_load_file($request_url) or die("url not loading");// XML request
$status = $xml->status;// GET the request status as google's api can return several responses
if ($status=="OK") {
    //request returned completed time to get lat / lang for storage
    $lat = $xml->result->geometry->location->lat;
    $long = $xml->result->geometry->location->lng;

}
echo "$lat,$long";

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}


$sql = "SELECT * FROM location";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
// output data of each row
while($row = mysqli_fetch_assoc($result)) {
    echo " Hobby: " . $row["lat"]. " Location: " . $row["long"]. "<br>";
    $lat1=$row["lat"];
    echo $lat1;
}
} else {
echo "Sorry, there are no meetups yet you can create one here ";
}

mysqli_close($conn);

     ?>

You shouldn't really do `SELECT *`. You should select the columns you want instead. — kkuilla, Feb 10 '15 at 12:27
Sounds like you need the Haversine Formula - http://stackoverflow.com/questions/14750275/haversine-formula-with-php — martincarlin87, Feb 10 '15 at 12:33

score 0 · Accepted Answer · edited May 23 '17 at 12:17

0

If you have an mySQL database, look to these questions, they deal with the same problem.

Mysql within distance query

https://gis.stackexchange.com/questions/31628/find-points-within-a-distance-using-mysql

You need to create a SQL statement where all your coordinates within a certain distance are the results. It may look like:

SELECT *, 
   ( 3959 * acos( cos( radians($lat) ) 
   * cos( radians( lat ) ) 
   * cos( radians( lng ) - radians($lng) ) 
   + sin( radians($lat) ) 
   * sin( radians( lat ) ) ) ) AS distance 
FROM locations 
HAVING distance < $miles 
ORDER BY distance 
LIMIT 0, 20

If you have a postgres database you can use the postGIS extention. There a function ST_DWithin exists, which gives true if the coordinates are within a certain distance.

edited May 23 '17 at 12:17

Community

1
1

answered Feb 10 '15 at 12:28

Bine

394
4
12

$lat, $long, $miles are the variables of the user: lat, lng are the names of your columns in your database – Bine Feb 10 '15 at 12:32
I keep getting an error for some reason "check the manual that corresponds to your MySQL server version for the right syntax to use near 'long ) - radians(0.0116469) ) + sin( radians(51.5556739) ) * sin( radi' at line 4" – user4258493 Feb 10 '15 at 13:16
check the right spelling of all your variables and whether all brackets are there. it seems that there is an interpretation error – Bine Feb 10 '15 at 13:30
yes I have checked, doesnt seem to be any mistakes. same code as above I have changed the variable names accordingly – user4258493 Feb 10 '15 at 14:36
fixed now added ` for the database column names – user4258493 Feb 11 '15 at 13:48

Only show results within range chosen by user

1 Answers1