Re: haversine formula with postgreSQL

Jonathan <jharahush@gmail.com> writes:
> Here is my PHP with SQL:
> $query = sprintf("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",

Sigh, you've been misled by MySQL's nonstandard behavior.  You cannot
refer to output columns of a query in its HAVING clause; it's disallowed
per spec and not logically sensible either.  The simplest way to deal
with it is just to repeat the expression in HAVING.  If you really
really don't want to write it twice, you can use a subquery.

            regards, tom lane