如何使此地理距离SQL查询与Postgres兼容

Question

我尝试使用的库具有给定的纬度和经度，并且对表中的条目进行计算后得出的纬度/经度在此范围内。生成的SQL查询适用于MySQL，但不适用于PostgreSQL。

这是我的server.log文件中详细说明错误psql和完整查询的条目：

ERROR:  column "distance" does not exist at character 507
STATEMENT:  select *, ( '3959' * acos( cos( radians('53.49') ) * cos( radians( places.lat ) ) * cos( radians( places.lng ) - radians('-2.38') ) + sin( radians('53.49') ) * sin( radians( places.lat ) ) ) ) AS distance from (
                        Select *
                        From places
                        Where places.lat Between 53.475527714192 And 53.504472285808
                        And places.lng Between -2.4043246788967 And -2.3556753211033
                    ) As places where "places"."deleted_at" is null having "distance" <= $1 order by "distance" asc

知道了什么是SQL之后，我可以编辑生成SQL的PHP​​代码，并将PR发送回库。

Answer 1

该查询使用特定于MySql的语法。在Postgres（以及我所知的所有其他RDBMS）中，应使用派生表：

select *
from (
    select *, 
        (3959 * acos( cos( radians(53.49) ) * cos( radians( places.lat ) ) 
        * cos( radians( places.lng ) - radians(-2.38) ) 
        + sin( radians(53.49) ) * sin( radians( places.lat ) ) ) ) AS distance 
    from (
        select *
        from places
        where places.lat between 53.475527714192 and 53.504472285808
        and places.lng between -2.4043246788967 and -2.3556753211033
        ) as places 
    where places.deleted_at is null 
    ) sub
where distance <= $1 
order by distance asc

我还从数字常量中删除了引号。