Обсуждение: calculating spherical distance in sql

i'm trying to calculate spherical distance and seeing the closest zipcodes
to a specified zipcode. this query works:

find all zips less than 20 miles from zip XXXXX (s = starting zip table):

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='55404'
AND
(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)
  )
) <20
ORDER BY dist LIMIT 10;

that works fine. but it seems like a waste to calculate the distance
twice, so i thought about trying a simpler version:

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'
AND
dist <20
ORDER BY dist LIMIT 10;

but that didn't work. any ideas?

"Thomas T. Thai" <tom@minnesota.com> writes:

> i'm trying to calculate spherical distance and seeing the closest zipcodes
> to a specified zipcode. this query works:
>
> find all zips less than 20 miles from zip XXXXX (s = starting zip table):

[...]

> that works fine. but it seems like a waste to calculate the distance
> twice, so i thought about trying a simpler version:
>
> 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'
> AND
> dist <20
> ORDER BY dist LIMIT 10;
>
> but that didn't work. any ideas?

Define "didn't work".

Why not write 'dist' as a function and mark it cacheable?

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863

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

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