如何使此地理距离SQL查询与Postgres兼容 [英] How can I make this geo-distance SQL query Postgres compatible

问题描述

我要使用的图书馆要采用给定的纬度和经度，并且要对表中的条目计算出的纬度/经度在此范围内.生成的SQL查询可用于MySQL，但不适用于PostgreSQL.

A library I’m trying to use takes a given latitude and longitude and work out with entries in a table have a lat/lng within a certain distance from this. The generated SQL query works with MySQL, but not with PostgreSQL.

这是我的server.log文件中的条目，详细记录了psql给出的错误和完整的查询:

Here’s the entry in my server.log file detailing the error psql is giving and the full query:

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发送回库.

Knowing what the SQL should be I can then edit the PHP code that generates it and send a PR back to the library.

推荐答案

查询使用特定于MySql的语法.在Postgres(以及我所知的所有其他RDBMS)中，应使用派生表:

The query uses the syntax specific for MySql. In Postgres (and all other known to me RDBMS) you should use a derived table:

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

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

I have also removed quotes from numeric constants.

这篇关于如何使此地理距离SQL查询与Postgres兼容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！