Postgres查询以检索给定距离内的位置

Question

下面的sql查询在MYSQL中运行良好，但在Postgres中给出了一个错误：

SELECT * , 
        ( 3959 * acos( cos( radians('13.1') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('53.1') ) + sin( radians('13.1') ) * sin(radians(lat)) ) ) AS distance 
FROM person 
HAVING distance < 10 
ORDER BY distance LIMIT 20

**ERROR**:  column "distance" does not exist
LINE 1: ...(radians(lat)) ) ) AS distance FROM person HAVING distance <...

Answer 1

可以在筛选条件中重复表达式，也可以通过使用子查询来避免这种情况：

SELECT *
FROM (SELECT *, 
             3959 * acos(
                       cos(
                          radians('13.1')
                       ) * cos(
                          radians(lat)
                       ) * cos(
                          radians(lng) - radians('53.1')
                       ) + sin(
                          radians('13.1')
                       ) * sin(radians(lat))
                    ) AS distance 
      FROM person) AS q 
WHERE distance < 10 
ORDER BY distance LIMIT 20;