Re: calculating spherical distance in sql - Mailing list pgsql-general

Doug McNaught <doug@wireboard.com> writes:
> Define "didn't work".

"Didn't work" no doubt means "a column name defined in the SELECT's
output list is not available in the SELECT's where clause".

The way you could actually suppress multiple calculations of an
expression is to use a sub-SELECT:

SELECT *
FROM
  (SELECT z.zip_code,z.poname,z.state,
   (3958.75 *
     acos(
       sin(s.latitude/57.2958) *
       sin(z.latitude/57.2958) +
       cos(s.latitude/57.2958) *
       cos(z.latitude/57.2958) *
       cos(z.longitude/57.2958 - s.longitude/57.2958)
     )
   ) AS dist
   FROM zipcodes AS z, zipcodes AS s
   WHERE s.zip_code='55401'
  ) AS ss
WHERE dist < 20
ORDER BY dist LIMIT 10;

I'm not convinced that it buys much in this example, but with a
*seriously* expensive expression to calculate, it might be worth
contorting your query like this...

            regards, tom lane