Ryan Tirrell Ryan Tirrell - 2 years ago 111
PHP Question

Return results within a given radius using a bounding circle as a 'First Cut' in MySQL

I was working with Chris Veness' scripts from http://www.movable-type.co.uk I was trying to run a query against a MySQL database to return only rows that fall within a given radius, using his Bounding Circle script. As follows:


require 'inc/dbparams.inc.php'; // defines $dsn, $username, $password
$db = new PDO($dsn, $username, $password);

$lat = $_GET['lat']; // latitude of centre of bounding circle in degrees
$lon = $_GET['lon']; // longitude of centre of bounding circle in degrees
$rad = $_GET['rad']; // radius of bounding circle in kilometers

$R = 6371; // earth's mean radius, km

// first-cut bounding box (in degrees)
$maxLat = $lat + rad2deg($rad/$R);
$minLat = $lat - rad2deg($rad/$R);
// compensate for degrees longitude getting smaller with increasing latitude
$maxLon = $lon + rad2deg($rad/$R/cos(deg2rad($lat)));
$minLon = $lon - rad2deg($rad/$R/cos(deg2rad($lat)));

$sql = "Select Id, Postcode, Lat, Lon,
acos(sin(:lat)*sin(radians(Lat)) + cos(:lat)*cos(radians(Lat))*cos(radians(Lon)-:lon)) * :R As D
From (
Select Id, Postcode, Lat, Lon
From MyTable
Where Lat Between :minLat And :maxLat
And Lon Between :minLon And :maxLon
) As FirstCut
Where acos(sin(:lat)*sin(radians(Lat)) + cos(:lat)*cos(radians(Lat))*cos(radians(Lon)-:lon)) * :R < :rad
Order by D";
$params = array(
'lat' => deg2rad($lat),
'lon' => deg2rad($lon),
'minLat' => $minLat,
'minLon' => $minLon,
'maxLat' => $maxLat,
'maxLon' => $maxLon,
'rad' => $rad,
'R' => $R,
$points = $db->prepare($sql);

<? foreach ($points as $point): ?>
<td><?= $point->Postcode ?></td>
<td><?= number_format($point->D,1) ?></td>
<td><?= number_format($point->Lat,3) ?></td>
<td><?= number_format($point->Lon,3) ?></td>
<? endforeach ?>

I renamed my existing columns in my database to match what Chris Veness used - and instead of using a $_GET value, I entered some static values of

  • $lat = 51.552971553688500;

  • $lon = -3.028690575475280;

  • $rad = 25;

This did not work... And moreover, I could not find a solution as to why it wasn't working, exactly... Although I think @dan08 was very much onto something with his answer below. He knows a lot more about this stuff than I do.

Despite all that - I do [finally] have a working solution! Please see my answer below.

Answer Source

Those folks over at Google Devs are clever bunch!

So here's the link I followed to find my solution: Creating a Store Locator with PHP, MySQL & Google Maps

Despite the fact that this tutorial is geared toward using Google Maps API, the first half of the tutorial focuses on using PHP to query a database and create a bounding circle in order to search for matches within a given radius and return only those results that match.

In the tutorial, the query is super-fast and outputs the results in XML which is exceptionally useful for integrating into API. I didn't need that functionality, so I simplified mine a little.

Here's what I have - and it works perfectly for what I need:

Create a page called: phpsqlsearch_dbinfo.php


Create a new page called: phpsqlsearch_genxml.php

When visiting this page, We pass some values to it, as we're using $_GET to collect the 'lat','lng' and 'radius' values.


// Get parameters from URL
$center_lat = $_GET["lat"];
$center_lng = $_GET["lng"];
$radius = $_GET["radius"];

// Opens a connection to a mySQL server
$connection=mysql_connect (localhost, $username, $password);
if (!$connection) {
  die("Not connected : " . mysql_error());
// Set the active mySQL database
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
  die ("Can\'t use db : " . mysql_error());
// Search the rows in the markers table
$query = sprintf("SELECT id, address, name, lat, lng, ( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM candidates HAVING distance < '%s' ORDER BY distance LIMIT 0 , 20",
$result = mysql_query($query);
if (!$result) {
  die("Invalid query: " . mysql_error());
while ($row = @mysql_fetch_assoc($result)){
  $ID = mysql_real_escape_string($row['id']);
  $name = mysql_real_escape_string($row['name']);
  $address = mysql_real_escape_string($row['address']);
  $lat = mysql_real_escape_string($row['lat']);
  $lng = mysql_real_escape_string($row['lng']);
  $distance = mysql_real_escape_string($row['distance']);

  echo $name .", ". $address .", Latitude:". $lat .", Longitude:". $lng .", Distance From Home = ". round($distance)." Miles <br /><br />";

   // I then insert these matches into a new table for later use. 
   $new = "INSERT INTO matrix (Marker_ID, Cand_Name, Distance)
    VALUES ('$ID', '$name', '$distance')";

   $resulting = mysql_query($new);
   if (!$resulting) {
     die("Invalid query: " . mysql_error());

Finally, to get this example working, you need to have your database set up. If you don't have access to phpMyAdmin or prefer using SQL commands instead, here's the SQL statement that creates the table:

CREATE TABLE `markers` (
`name` VARCHAR( 60 ) NOT NULL ,
`address` VARCHAR( 80 ) NOT NULL ,
`lat` FLOAT( 10, 6 ) NOT NULL ,
`lng` FLOAT( 10, 6 ) NOT NULL

Now for the example data to populate the table: Click Here - This example data set contains 169 rows in total. If you follow the link above, you can copy the full data set in the following format:

INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Frankie Johnnie & Luigo Too','939 W El Camino Real, Mountain View, CA','37.386339','-122.085823');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Amici\'s East Coast Pizzeria','790 Castro St, Mountain View, CA','37.38714','-122.083235');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Kapp\'s Pizza Bar & Grill','191 Castro St, Mountain View, CA','37.393885','-122.078916');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Round Table Pizza: Mountain View','570 N Shoreline Blvd, Mountain View, CA','37.402653','-122.079354');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Tony & Alba\'s Pizza & Pasta','619 Escuela Ave, Mountain View, CA','37.394011','-122.095528');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Oregano\'s Wood-Fired Pizza','4546 El Camino Real, Los Altos, CA','37.401724','-122.114646');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Round Table Pizza: Sunnyvale-Mary-Central Expy','415 N Mary Ave, Sunnyvale, CA','37.390038','-122.042034');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Giordano\'s','730 N Rush St, Chicago, IL','41.895729','-87.625411');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Filippi\'s Pizza Grotto','1747 India St, San Diego, CA','32.723831','-117.168326');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Lou Malnati\'s Pizzeria','439 N Wells St, Chicago, IL','41.890346','-87.633927');

I hope this helps anyone who has struggled as much as I have. With just basic understanding of PHP and MySQL, you'll have this up and running in no time.

Good Luck!

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download