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