search locations within certain distance (proximity search)

Does anyone here have experience building search applications like Google local, where you return results within a certain distance of the searcher's specified location?

 

Yep.

You need some way of getting the searchers latitude and longitude (usually a zip code or postcode) and then you compare this to the latitude and longitude of each location you have listed in your database.

The distance is calculated using Pythagoras' Theorem.

Load the results into an array and order it by distance (ascending) and output the results.

Include google maps for the ultimate "find nearest" script.

 

Or an amended version which takes into account the fact the earth is in a globe shape instead of flat. That's better for accuracy.

See here if you want to buy the code for it.

 

mad4, do you have any resources for the SQL stored procedures that you used?

 

It's hard to do it on the SQL side of things. Without a 'geo enabled' database like PostGreSQL you won't be able to easily perform a 'WHERE point x is WITHIN area y'.

You'd normally compute the distance to all locations and then grab a subset from there.

 

This is the generl script, you have to create the queries and add values to the various variables etc.

//find the latitude and longitude of your user

//query to select the locations from your database

while ($row= mysql_fetch_array($result)) {

  $dist1 = abs($latitude1 - $latitude2);
  $dist2 = abs($longitude1 - $longitude2);
  $dist1 = $dist1^2;
  $dist2 = $dist2^2;
  $distance_in_metres = $dist1 + $dist2;
  $distance_in_metres = $distance_in_metres^0.5;
  $distance_in_miles = $distance_in_metres/$constant;
  $distance_in_miles = number_format($distance_in_miles, 1);
  #
  # put in array
  #
  $shops[$count]['name'] = $row[name];
  $shops[$count]['dist'] = $distance_in_miles;
  $count++;
  

}

function distSort ($a, $b) 
{
if ($a['dist'] == $b['dist']){
return 0;
}
return $a['dist'] < $b['dist'] ? -1 : 1;
} 
usort ($shops, 'distSort');    
 $bg=1;
foreach ($shops as $data) {
//output shop details here
  }
  $number++;
}

}

PHP:

 

awesome, thanks
I imagine that if the searchers are looking for results no greater than 50 miles aways, the curve of the earth may not be a huge factor right now

did you find that the results took a long time to tabulate?

also, in the SQL query, did you by default only select results with long/lat within a certain distance?

 

The script is very quick.

The point is that you can't select the results from the database based on distance as you have to select them first before you can calculate the distance.

My script selects all the points in the database and then loads them into an array before ordering them by the nearest first.

Just checkin my script you also need the following right at the top:

$shops = array();
$constant = 1609;

PHP:

and the foreach loop should read

foreach ($shops as $data) {
 if (($number <= $max) && ($number > $starting)){

PHP:

instead of

foreach ($shops as $data) {

PHP:

Let me know if you have any problems.

 

I dug up this SQL code for a stored procedure, does it look right?

Returns zip codes within specified range.
*/
CREATE Procedure sp_ZipCode_Range
 (
  @ZipCode Numeric(5, 0) = Null,
  @Miles Float
 )
As
 set nocount on
 
 Declare @Latitude Float(10)
 Declare @Longitude Float(10)

 -- Lookup longitude, latitude for zip codes
 Select @Latitude = Latitude, @Longitude = Longitude From State Where Zip = @ZipCode
 
 Select
  Zip, Zip_Name,
--   Zip_Name,
  Avg(3958.75 * ACos(Sin(@Latitude/57.2958) *
  Sin(Latitude/57.2958) +
  Cos(@Latitude/57.2958) *
  Cos(Latitude/57.2958) *
  Cos(Longitude/57.2958 - @Longitude/57.2958))) As Miles
 From
  State
 Where
  Longitude Is Not Null
  And Latitude Is Not Null
  And @Miles >=
   (
   3958.75 * ACos(Sin(@Latitude/57.2958) *
   Sin(Latitude/57.2958) +
   Cos(@Latitude/57.2958) *
   Cos(Latitude/57.2958) *
   Cos(Longitude/57.2958 - @Longitude/57.2958))
   )
  And Zip != @ZipCode
 Group by zip, zip_name
 Order by miles
 return

Code (markup):

 

That code uses the earth's curve, it's similar to my PHP code.

 

excellent
thanks

 

one quick question, are databases out there for purchase or for free of geocodes for zips and postal codes?

 

For free if you don't need accuracy. They'll be outdated. Paid for with regular updates if you want them accurate.

Search 'geolocation database' see the link I posted earlier for such databases.

 

Keep in mind that if you have a large number of records, this will be quite inefficient. In those cases, you should first limit your candidates by "boxing" them by latitude/longitude - say, if you're looking for things within 50 miles of your target location, you can limit your SELECT through a WHERE clause to be within 2 degrees of latitude/longitude - that will give you roughly a box of 140 miles on each side (discounting earth's curvature).

 

very smart, thank you

 

This site http://www.zipcodeworld.com/developers.htm has a more accurate distance calculation code which takes into account the curvature of the earth and they also have the latitude and longitude databases.

 

this forum many have knowledge

 

Hey, But I got something more efficient for large Database.
if your distance is 10 KM then the SQL for MySQL can be:

"SELECT [columns] FROM [column] WHERE 1=1 AND 3963.191 * ACOS((SIN(PI
() * [latitude] / 180) * SIN(PI() * [column].latitude / 180)) + (COS(PI
() * [latitude] /180) * cos(PI() * [column].latitude / 180) * COS(PI()
* [column].longitude / 180 - PI() * [longitude] / 180)) ) <= 10"

Above calculations will give the objects that are within the 10 KM
area from the country/city center point on earth.