查询在给定的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:

所以我想在给定的lat/液化天然气的范围内显示位置列表。我对此没有异议:

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.

这不是这样做。我猜这只是我在周日下午不能完全理解的逻辑，但我可能也做错了别的事情。非常感谢您的投入。

3 个解决方案

#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.

下面是我认为您需要的:使用lat/lng在MySQL中计算距离。这将给你一个循环，一个你需要的排除的能力。

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)

PS:如果你有一个拥有大量记录的数据库，你需要对它的性能进行基准测试，并进行一些优化(如果性能不可接受)

#1