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:
<?php
require 'inc/dbparams.inc.php'; // defines $dsn, $username, $password
$db = new PDO($dsn, $username, $password);
$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
$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);
$points->execute($params);
?>
<html>
<table>
<? foreach ($points as $point): ?>
<tr>
<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>
</tr>
<? endforeach ?>
</table>
</html>
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.
I think the culprit lies somewhere in the calculation inside the $sql function.
Correct. But it's not what you are thinking.
You main problem is that your are using placeholder lie :lat in your select clause. This comment explains the problem well. But the bottom line is that you cant bind params in the SELECT and FROM clause because they are parsed before parameters are added.
Using PDO or MySQLi won't change that. So to add those variable into the SELECT clause, sanitize them properly and insert them as string in the query.
You're making use of one of my favorite features of PDO, the named placeholders. So I would suggest sticking with PDO.
To have any luck debugging a 500 error you need to check your server logs. Maybe /var/log/apache2/error.log if you have a typical LAMP stack.
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
<?
$username="Your_Database_Username";
$password="Your_Database_Password";
$database="The_Name_of_Your_Database";
?>
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.
Eg.phpsqlsearch_genxml.php?lat=37&lng=-122&radius=25
<?php
require("phpsqlsearch_dbinfo.php");
// 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",
mysql_real_escape_string($center_lat),
mysql_real_escape_string($center_lng),
mysql_real_escape_string($center_lat),
mysql_real_escape_string($radius));
$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` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 60 ) NOT NULL ,
`address` VARCHAR( 80 ) NOT NULL ,
`lat` FLOAT( 10, 6 ) NOT NULL ,
`lng` FLOAT( 10, 6 ) NOT NULL
) ENGINE = MYISAM ;
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');
etc...
etc...
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!
