Postgres Pro
Downloads
Home   >   mailing lists

Thread: calculating spherical distance in sql

calculating spherical distance in sql

From
"Thomas T. Thai"
Date:
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?

Re: calculating spherical distance in sql

From
Doug McNaught
Date:
"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

Re: calculating spherical distance in sql

From
Tom Lane
Date:
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

Re: calculating spherical distance in sql

From
Jean-Luc Lachance
Date:
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