使用 WHERE 子句在经纬度范围内查找 POI

2022-01-14 00:00:00 geometry sql geo gps mysql

我正在使用以下 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.

相关文章