calculating spherical distance in sql

"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: