Re: How many views...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Sunday 28 November 2004 10:43 pm, Michael Fuhr wrote:
> On Sun, Nov 28, 2004 at 06:41:50PM -0800, Uwe C. Schroeder wrote:
> > I need to search a lot of locations based on distance (simple zipcode
> > match based on longitude and latitude). However I need to calculate the
> > distance between each of the nodes, so if you are in xxx I need to get
> > the distance to all others in the database. I'm currently doing this with
> > a stored procedure that gets the originating zipcode and a maximum
> > distance in miles which then selects all other nodes within that search
> > radius. This is pretty unhandy, but it works.
>
> What's unhandy about this approach? I've written stored procedures
> that do exactly what you're talking about; they work fine and are
> easy to use.
>
> Are you using a bounding box to limit the number of nodes that
> you need to check? For example, if the originating zipcode is
> at 40.0N 90.0W and you want to find all other zipcodes within
> 50 miles, then you'd only need to check the distance to those
> zipcodes with a latitude between about 39.27N - 40.73N and a
> longitude between about 89.05W and 90.95W. No zipcode outside
> that box could possibly be within 50 miles of the origin, so
> there's no need to calculate and check the distances to them.
> If you have indexes on latitude and longitude then the search
> should be fast.

The "unhandy" part is maybe a personal perception. I like stored procs, but in
this case the calculation is repeated over and over again (ok, it would be
the same with views). Basically the part I don't like is that the proc
currently calculates way more values than needed. Because something like
.... where sqrt(pow((lat1 - lat2),2) + pow((long1 - long2),2)) >= 50
certainly calculates the distance of all the records and then compares the
result to the 50 mile radius. I'd rather have something that excludes most of
the records that aren't in question anyways. How do you come to the lat/long
values for the max difference? Is there a general formula for that? This
looks like I could omit records too far away from the calculation in the
first place. I know - maybe I should dig for those old geometry books that
are somewhere in a box, but if you have the base for that handy I'd
appreciate if you tell me (I hated math all my life ;-) )

UC

- --
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax: +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFBqsp/jqGXBvRToM4RAg/xAJ497wF1pwbzLFHbC/f1UehOoG2iGwCfWKYQ
5cNIUb984sPLtBGudDqspF8=
=hsl2
-----END PGP SIGNATURE-----