CODEWITHSUNDEEP
sqlrubypostgresqlsequel
Reggie Escobar
Reggie Escobar

Reputation: 99

Sequel: PG::UndefinedColumn: ERROR: column "distance" does not exist

I'm trying to get a list of businesses nearby using this query:

query = "SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( lat ) ) ) ) AS distance FROM businesses HAVING distance < ? ORDER BY distance"

After running this:

records = Api.DB.fetch(query, lng, lat, distance)

I get that PG::UndefinedColumn: ERROR: column "distance" does not exist.

distance isn't a column in the table but I created it as a calculated column in the query so it suppose to work but it's not?

Any ideas?

Upvotes: 1

Views: 609

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270823

Postgres supports lateral joins, so you can do this without a subquery or CTE as:

SELECT b.id, v.distance
FROM business b CROSS JOIN LATERAL
     (VALUES ( 3959 * acos( cos( radians(37) ) * cos( radians( b.lat ) ) * cos( radians( b.lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( b.lat ) ) )
             )
     ) v(distance)
WHERE v.distance < ?
ORDER BY distance;

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522581

The query you are using was poached from MySQL, where select aliases can be used in a HAVING clause. But in Postgres, you will have to either subquery or just repeat the distance formula. Using the latter option as an example, here is one viable version of your Haversine query:

SELECT
    id,
    ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( lat ) ) ) ) AS distance
FROM business
WHERE
    ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( lat ) ) ) )
    < ?
ORDER BY
    distance;

Upvotes: 2

Related Questions