Re: haversine formula with postgreSQL

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 * FROM
   (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 gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD