Re: haversine formula with postgreSQL

Rob Wultsch <wultsch(at)gmail(dot)com> writes:
> On Thu, Sep 17, 2009 at 2:47 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Jonathan <jharahush(at)gmail(dot)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

> This practice is also a bad habit for MySQL users. I regularly see queries
> from users that have conditions that logically belong in the WHERE clause
> but the user shoves it into the HAVING. This is often done without a
> specific GROUP BY. The MySQL optimizer does not deal with this well.

Actually, that reminds me that there's an even bigger nonstandard
behavior here. HAVING is *not* just some weird alternative spelling of
WHERE; it implies that the query is grouped. If you don't have a GROUP
BY clause then the implication is that the query should yield just one
row, and what's more it can't refer to any variables at all except
within aggregate functions, since there are no grouped variables.
MySQL gets this flat wrong. (In fairness, we used to get it wrong too,
until we read the spec more closely.)

> When would it make logical sense to have a HAVING clause that deals with a
> column that is not inside a aggregating function?

Well, HAVING is supposed to apply to the post-GROUP BY rows. So 99%
of the time you would want the HAVING condition to involve an aggregate
function --- otherwise you ought to put it in WHERE and filter away
the unwanted rows sooner. However I can imagine having a
very-expensive-to-execute test that you'd rather execute only once
per group.

regards, tom lane