On Thu, 17 Sep 2009, Jonathan wrote:

Invalid query: ERROR: column "distance" does not exist LINE
1: ...ude ) ) ) ) AS distance FROM aaafacilities HAVING distance <...

You can't use distance in a HAVING clause if distance is computed as part of the query result. You can rewrite this to use a subquery instead:

  (SELECT 'ID', 'FACILITY', 'ADDRESS', latitude, longitude,
    ( 3959 * acos( cos( radians('%s') ) * cos( radians
    ( latitude ) ) * cos( radians( longitude ) - radians('%s') ) + sin
    ( radians('%s') ) * sin( radians( latitude ) ) ) ) AS distance
   FROM aaafacilities)
HAVING distance < '%s' ORDER BY dist LIMIT 0 OFFSET 20

And I think that might work for you. The performance is going to be miserable for large stat sets, because it's going to scan the whole aaafacilities table every time and recompute every distance, but as an example goes it's probably acceptable.

Be warned that this example is like a textbook example of how to introduce a SQL injection vulnerability into your code. Once you get the syntax right, you should be using a parameterized query here rather than generting the query using sprintf before exposing this code to the outside world.

* Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD

Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:

Reply via email to