查询以找到给定经纬度内的地方 [英] 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...)

使用类似

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.

推荐答案

我们或多或少地像下面的代码一样实现了它(免责声明:我将其从文件中删除并删除了与手头问题无关的代码.我没有执行此操作，但是您应该能够理解这个想法.

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";

其余查询

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) 

这篇关于查询以找到给定经纬度内的地方的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！