Find all records in database that are within a certain distance of a set of lat and long points

Question

I've seen all the examples and here's what I got so far.

my table is simple:

schools (table name) - School_ID - lat - long - county - extrainfo

here's my code:

<?php

 $con = mysql_connect("xxx","xxx","xxx");



 if (!$con) {

            die('Could not connect: ' . mysql_error());

 } else {}



 mysql_select_db("xxx", $con);

 $latitude = "36.265541";

 $longitude = "-119.207153";

 $distance = "1"; //miles



 $qry = "SELECT *, (3958.75 * ACOS(SIN(" . $latitude . " / 57.2958)*SIN(lat / 57.2958)+COS(" . $latitude . " / 57.2958)*COS(lat / 57.2958)*COS(long / 57.2958 - " . $longitude . " / 57.2958))) as distance FROM schools WHERE (3958.75 * ACOS(SIN(" . $latitude . " / 57.2958)*SIN(lat / 57.2958)+COS(" . $latitude . " / 57.2958)*COS(lat / 57.2958)*COS(long / 57.2958 - " . $longitude . " / 57.2958))) <= " . $distance;

 $results = mysql_query($qry);
 if (mysql_num_rows($results) > 0) {
            while($row = mysql_fetch_assoc($results)) {
                            print_r($row);
            }
 } else {}

 mysql_close($con);

 ?>

but I get this error when I try to run it:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource

Answer 1

To be honest, I recommend you just grab all the lat/long for all the schools and loop through running a Haversine function through your PHP code.

Answer 2

First, 'long' is a reserved keyword in MySQL. You'll need to enclose it in backticks like so:

SELECT `long`,lat FROM schools

Full list of reserved keywords can be found here: Reserved Words

If you have access to a tool like phpMyAdmin, I recommend running your query testing there.

Otherwise, try executing this in your code after running mysql_query():

print(mysql_errno().' '.mysql_error());

That should give you the error code and error message generated by MySQL. The query looks ok other than the keyword issue, but this will tell you definitively.

Answer 3

This is so perfect. I've been looking for this script for weeks and this one did just the trick!! Thanks so much.