Re: calculating spherical distance in sql

If for each query, you are going to compute a cartesian product every
time,
why not compute it once and for all... trade speed for space.

Find what will be the greatest distance ever queried
(let's try to limit the size of the resulting table).


CREATE TABLE zip_dist AS
(SELECT z.zip_code as from_zip, s.to_zip as to_zip
  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
) AS ss
WHERE dist < {MAX_DIST_EVER};

If you want more speed, you can even create an index.

CREATE index zip_dist_from on zip_dist( from_zip, dist);



Then

SELECT to_zip, dist from zip_dist where from_zip = '55401' and dist <
20;


jll


Tom Lane wrote:
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org