使用WHERE子句在距离经度和纬度的距离范围内查找POI [英] Using WHERE clause to find POI within a range of distance from Longitude and Latitude
问题描述
我使用下面的sql代码来找出离设置坐标最近的'ALL'poi,但是我想找出具体的poi而不是所有的poi。当我尝试使用where子句时,我得到一个错误,它不起作用,这是我目前卡住的地方,因为我只使用一个表来处理所有poi的所有坐标。
I'm using following sql code to find out 'ALL' poi closest to the set coordinates, but I would want to find out specific poi instead of all of them. When I try to use the where clause I get an error and it doesn't work and this is where I'm currently stuck, since I only use one table for all the coordinates off all poi's.
SET @orig_lat=55.4058;
SET @orig_lon=13.7907;
SET @dist=10;
SELECT
*,
3956 * 2 * ASIN(SQRT(POWER(SIN((@orig_lat -abs(latitude)) * pi()/180 / 2), 2)
+ COS(@orig_lat * pi()/180 ) * COS(abs(latitude) * pi()/180)
* POWER(SIN((@orig_lon - longitude) * pi()/180 / 2), 2) )) as distance
FROM geo_kulplex.sweden_bobo
HAVING distance < @dist
ORDER BY distance limit 10;
推荐答案
问题是您无法引用别名列(距离
在这种情况下)在中选择
或其中
条款。例如,你不能这样做:
The problem is that you can not reference an aliased column (distance
in this case) in a select
or where
clause. For example, you can't do this:
select a, b, a + b as NewCol, NewCol + 1 as AnotherCol from table
where NewCol = 2
这两个操作都会失败:在试图处理
语句,并且在 NewCol + 1
时选择其中
语句中选择当试图处理 NewCol = 2
。
This will fail in both: the select
statement when trying to process NewCol + 1
and also in the where
statement when trying to process NewCol = 2
.
有两种方法可以解决这个问题:
There are two ways to solve this:
1)将参考值替换为计算值本身。示例:
1) Replace the reference by the calculated value itself. Example:
select a, b, a + b as NewCol, a + b + 1 as AnotherCol from table
where a + b = 2
2)使用外部选择
语句:
2) Use an outer select
statement:
select a, b, NewCol, NewCol + 1 as AnotherCol from (
select a, b, a + b as NewCol from table
) as S
where NewCol = 2
现在,考虑到你的巨大而不是非常人性化的计算栏:)我认为你应该选择最后一个选项来提高可读性:
Now, given your HUGE and not very human-friendly calculated column :) I think you should go for the last option to improve readibility:
SET @orig_lat=55.4058;
SET @orig_lon=13.7907;
SET @dist=10;
SELECT * FROM (
SELECT
*,
3956 * 2 * ASIN(SQRT(POWER(SIN((@orig_lat -abs(latitude)) * pi()/180 / 2), 2)
+ COS(@orig_lat * pi()/180 ) * COS(abs(latitude) * pi()/180)
* POWER(SIN((@orig_lon - longitude) * pi()/180 / 2), 2) )) as distance
FROM geo_kulplex.sweden_bobo
) AS S
WHERE distance < @dist
ORDER BY distance limit 10;
编辑:正如@Kaii在下面提到的那样,扫描。根据您要处理的数据量,您可能想避免这种情况,并选择第一个选项,该选项的执行速度会更快。
As @Kaii mentioned below this will result in a full table scan. Depending on the amount of data you will be processing you might want to avoid that and go for the first option, which should perform faster.
这篇关于使用WHERE子句在距离经度和纬度的距离范围内查找POI的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!