Re: haversine formula with postgreSQL

This behavior kinda gets me sometimes too, especially in WHERE clauses..

I'm a bit curious as to why this is so bad. I could see why it would
be expensive to do, since your clause wouldn't be indexed - but why is
the syntax itself not allowed? Repeating the clause isn't gonna gain
you any speed, and might make it even slower since the expression
would have to be evaluated twice right? Perhaps I'm missing
something..

Mike

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
> really don't want to write it twice, you can use a subquery.
>
>                        regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>