How can I make this geo-distance SQL query Postgres compatible

Question

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.

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

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

Note: `'3959'` is a string constant. BTW: please add some vertical whitespace to your query. Scrolling horizontally is not good for readability... — wildplasser, Oct 10 '15 at 12:56
Sorry, that’s exactly as it was in the server.log file. And yes the 3959 is a constant which changes depending on if the distance is in miles or kilometers. — Jonny Barnes, Oct 10 '15 at 13:11
While the answer you have accepted is a literal translation of your query from mysql to postgresl it is is suboptimal and does not make use of the full features of postgresql — e4c5, Oct 11 '15 at 00:30
Klin give you the right answer. But I want point to where is the syntaxis error. You cant do `SELECT value as Distance From X Order By Distance` Because postgres doesnt recognize the alias at the same level. That is why klin use a derived table. — Juan Carlos Oropeza, Oct 11 '15 at 03:48
btw, instead of calculate `cos( radians(53.49)` and `sin( radians(53.49)` create constants for those once. And in your table add a field for `cos( radians( places.lng )` Those will speed your calculation by a lot. And if you have time try to check [Postgis](http://postgis.net/) extension. Have several geographic function will help you in the future. — Juan Carlos Oropeza, Oct 11 '15 at 03:53

score 4 · Accepted Answer · answered Oct 10 '15 at 13:03

4

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.

answered Oct 10 '15 at 13:03

klin

112,967
15
204
232

I think so, I have no MySql now to test it. – klin Oct 10 '15 at 13:15
I’m getting this error with psql: https://gist.github.com/jonnybarnes/ff1042d60b7a9130dd81 Do I just need to work out how to get the where null clause inside the second from? – Jonny Barnes Oct 10 '15 at 15:51
good one, But I need to know how to get results using with join two tables using above query in postgresql. 3959 is km or miles? – sankar muniyappa Dec 27 '16 at 14:35
@shankarmsr - I think you should describe your issue in a more specific way as a new question. – klin Dec 27 '16 at 15:06

score 1 · Answer 2 · answered Oct 10 '15 at 13:18

What your query does is a replace the ST_Dwithin function of postgresql with a long query.

ST_DWithin — Returns true if the geometries are within the specified distance of one another. For geometry units are in those of spatial reference and For geography units are in meters and measurement is defaulted to use_spheroid=true (measure around spheroid), for faster check, use_spheroid=false to measure along sphere.

it's very simple just do

ST_DWithin(geometry1, geometry2)

where geometry1 and geometry2 can be point fields (but they can be other geometric data types two)

So your query might become

SELECT * FROM places WHERE
 ST_DWithin(ST_GeomFromText('POINT(-71.060316 48.432044)', 4326), places.geom) ORDER BY ST_Distance (ST_GeomFromText('POINT(-71.060316 48.432044)', 4326), places.geom)

The only change that you will need to do is to put your lat, lng together into a single POINT Field. (your probably should do that even in mysql). You can continue to have your lat,lng in separate columns but you will be sacrificing the gains you can get by using a proper geo type.

Using ST_Dwithin will be faster than a manual distance query because it's designed to make use of geometry columns on postgresql which can be indexed.

What if you want to go back to mysql someday? Well mysql 5.7 added the St_Dwithin feature.

ERROR: function st_geomfromtext(unknown, integer) does not exist — klin, Oct 11 '15 at 01:42
Obviously because you don't have Postgis extension enabled. Here is the reference to the function http://postgis.net/docs/ST_GeomFromText.html — e4c5, Oct 11 '15 at 01:50

How can I make this geo-distance SQL query Postgres compatible

2 Answers2

Linked