Queries to find places within a given lat/lng

问题:

So I'm trying to display lists of places within a range of the given lat/lng. I have no problem with this:

Places within one mile (list of places...)

Using something like

SELECT * FROM places WHERE lat < $latmax AND lat > $latmin AND lng < $lngmax AND lng > $lngmin

But then I want to list places within two miles, BUT not within one mile -- that is, I don't want to repeat the results from the first query.

Here's one version of what I've tried:

$milesperdegree = 0.868976242 / 60.0 * 1.2;

// 1 mile -- this works
$degrees = $milesperdegree * 1;
$latmin = $lat - $degrees;
$latmax = $lat + $degrees;
$lngmin = $lng - $degrees;
$lngmax = $lng + $degrees;

$query = "SELECT * FROM places WHERE lat < $latmax AND lat > $latmin AND lng < $lngmax AND lng > $lngmin";

// 2 miles -- this doesn't work
$degrees_2 = $milesperdegree * 2;
$latmin_2 = $lat - $degrees_2;
$latmax_2 = $lat + $degrees_2;
$lngmin_2 = $lat - $degrees_2;
$lngmax_2 = $lat + $degrees_2;

$query = "SELECT * FROM places WHERE ";
$query .= "lat BETWEEN $latmax AND $latmax_2 AND lng BETWEEN $lngmax AND $lngmax_2 OR ";
$query .= "lat BETWEEN $latmin AND $latmin_2 AND lng BETWEEN $lngmin AND $lngmin_2 OR ";
$query .= "lat BETWEEN $latmax AND $latmax_2 AND lng BETWEEN $lngmin AND $lngmin_2 OR ";
$query .= "lat BETWEEN $latmin AND $latmin_2 AND lng BETWEEN $lngmax AND $lngmax_2";

That's not doing it. I'm guessing it's just some logic I can't wrap my head around on Sunday afternoon, but I'm probably doing something else wrong too. Any input is greatly appreciated.

回答1:

We implement it more or less like the code below (disclaimer: I snipped this out of a file and deleted the code that was irrelevant to the problem at hand. I didn't run this, but you should be able to get the idea.

$maxLat = $city->latitude + ($max_distance / 69); // 69 Miles/Degree
$minLat = $city->latitude - ($max_distance / 69);

$maxLon = $city->longitude + ($max_distance / (69.172 * cos($city->latitude * 0.0174533)));
$minLon = $city->longitude - ($max_distance / (69.172 * cos($city->latitude * 0.0174533)));

// Simplify terms to speed query
$originLatRadCos = cos($city->latitude * 0.0174533);
$originLatRadSin = sin($city->latitude * 0.0174533);
$originLonRad = $city->longitude * 0.0174533;

$city_distance_query = "
SELECT city_id, 
  3963 * acos(($originLatRadSin * sin( latitude * 0.0174533)) + 
  ($originLatRadCos * cos(latitude * 0.0174533) * cos((longitude * 0.0174533) -
  $originLonRad))) AS distanceFromOrigin
FROM cities
WHERE
 latitude < $maxLat AND latitude > $minLat AND longitude < $maxLon AND longitude > $minLon";

The rest of the query

SELECT cities.city_name, CityDistance.distanceFromOrigin,
FROM cities 
INNER JOIN ($city_distance_query) AS CityDistance ON CityDistance.city_id=cities.city_id
WHERE (distanceFromOrigin < $distance OR distanceFromOrigin IS NULL) 

回答2:

I think you're missing some brackets and have the logical operators a bit mixed up. How about this.

$query  = "SELECT * FROM places WHERE ";
$query .= "((lat BETWEEN $latmin_2 AND $latmax_2) AND NOT (lat BETWEEN $latmin AND $latmax)) AND ";
$query .= "((lng BETWEEN $lngmin_2 AND $lngmax_2) AND NOT (lng BETWEEN $lngmin AND $lngmax)) AND ";

EDIT

To solve the circle/square problem:

$query  = "SELECT * FROM places WHERE ";
$query .= "(POW((lat - $lat) * $avgMilesPerLatDeg,2) + ".
           "POW((lng - $lng) * $avgMilesPerLngDeg,2) BETWEEN 1 AND 4)";
// the four at the end is 2 squared

I would suggest using this approach if your app is not large-scale (geographically speaking) and the averages produce acceptable results. Calculating the real distance takes longer to compute and the difference might not be that big. This is up to you and your application's goal.

回答3:

Here is what I think you need: Calculate distance in MySQL using lat/lng. This will give you a circle, and an ability to have exclusion you need.

Follow the steps described in a post, but instead of

HAVING `distance`<= 10

You will need to put

HAVING `distance` BETWEEN 1 AND 2

This will give you stuff within the range.

PS: If you have a database with large number of records - you will need to benchmark how would it perform, and do some optimization (if performance is not acceptable)