使用 WHERE 子句在经纬度范围内查找 POI [英] Using WHERE clause to find POI within a range of distance from Longitude and Latitude

问题描述

我正在使用以下 sql 代码来找出最接近设置坐标的所有"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;

推荐答案

问题是你不能在 selectdistance)> 或 where 子句.例如，您不能这样做:

The problem is that you can not reference an aliased column (distancein 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 时的 select 语句以及尝试处理时的 where 语句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) 使用外部 select 语句:

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屋！