wELL i One upped the anty...
My records are all LAT/LON encoded already.. so I can do the
comparisons on the individual record :)
Check out ARCVIEW for doing such... complicated and expensive, but
necessary...
Have to test that this weekend when I get some time... know a lot of
poeple are itching for timings on this too :)
-paris
[finding the future in the past, passing the future in the present]
[connecting people, places and things]
-----Original Message-----
From: "Correa, Orlando (ITSC)" <[EMAIL PROTECTED]>
Date: Thu, 09 Aug 2001 12:33:42 -0600
Subject: RE: Zip Codes
> Any quantification of execution time for "spankin fast"? on the
> 43,000
> record table?
>
> -----Original Message-----
> From: Edward Smith [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, August 09, 2001 8:50 AM
> To: CF-Talk
> Subject: Re: Zip Codes
>
>
> Well, we've used it :) I just cut it out of a page, so it may need
> tweaking.
>
> I don't know of any other way to do it other then calculate against
> each
> lat/long other then precalculating some sort of matrix to try to weed
> out rows that you KNOW are not in the radius.
>
> It is spankin fast with US 5 digit zips. With +4 zipcodes, well,
> that's
> a lot of rows to calculate against, and I wouldn't so this. I'd have
> another table with the 5 digit zips, and returning the zips that
> match
> the radius, then do a +4 search.
>
> Remember that how many "locations" you have is not important to the
> zip-radius query. It just returns zipcodes that match - then query
> your
> 500+K row table for locations with those zipcodes.
>
> So, the flow would go like this:
>
> 1) Query to get Lat/Long (etc.) for User-Entered Zip code.
> 2) Query to get Zip5 zips within the radius of User-Entered Zip code
> (query against about 43,000 zip codes)
> 3) (Optional) Query against Zip+4 table filtering on the Zip5, so you
> are only hitting the rows that have the main zipcode returned by step
> 2
> 4) Query your location table for locations with the resulting
> zipcodes
>
> Paris Lundis wrote:
> >
> > Haven't tested the code below yet myself... ANyone used it
> before/yet?
> > Had been looking for an elegant SQL based computation... I wrote a
> much
> > more complicated and assumption oriented search to facilitate
> such...
> > It works not on the circle basis but on a SQUARE basis... a small
> error
> > margin in each corner :)
> >
> > I ask about folks using it to gauge both the speed of it compared
> to
> > how I do it and the number of records everyone is using with it...
> >
> > I have some tables that computations need to run against upwards of
> > 500k records quickly and usably so in a web setting.. My use for it
> is
> > people searching for other people or places based on where they are
> or
> > will be... QUite typical use I think...
> >
> > Interested in everyone's input..
> >
> > -paris
> >
> > -----Original Message-----
> > From: Edward Smith <[EMAIL PROTECTED]>
> > Date: Tue, 07 Aug 2001 14:45:48 -0500
> > Subject: Re: Zip Codes
> >
> > > Here's the important code - I think this works in Access,
> probably
> > > SQLServer as well. You need a database with zipcodes, longitude,
> and
> > > latitude. This assues Lon/Lat in degrees.
> > >
> > > This code takes in a radius, and returns all the zipcodes within
> that
> > > radius:
> > >
> > > <CFPARAM NAME="Radius" DEFAULT="1">
> > >
> > > <CFQUERY NAME="Ziplookup" DATASOURCE="ZipCodeDegrees">
> > > select longitude,latitude from ZipCodes where ZipCode
> ='#Zip1#'
> > > </CFQUERY>
> > >
> > > <CFQUERY NAME="getCities" DATASOURCE="ZipCodeDegrees">
> > > SELECT ZipCode FROM ZipCodes WHERE
> > > SQR( ( LONGITUDE-(#Ziplookup.LONGITUDE#) )^2 +
> > > (LATITUDE-(#Ziplookup.LATITUDE#))^2 ) <= (#RADIUS#)/60
> > > </CFQUERY>
> > >
> > > I'm sure this could be refined into a single query, if needs be.
> > >
> > > Here's the calculation for Lat/Long in radians:
> > >
> > > <CFSET Bung = sin(SourceLatitude) * sin(DestinationLatitude) +
> > > cos(SourceLatitude) * cos(DestinationLatitude) *
> > > cos(DestinationLongitude - SourceLongitude)>
> > > <CFSET Mileage = 3963.0 * (ATN(-Bung / SQR(-Bung * Bung + 1)) +
> > > 1.570796326794895)>
> > >
> > > Hope this helps.
> > >
> > > Rey Bango wrote:
> > > >
> > > > Hey all. I need to build a routine that will allow a site
> visitor
> > > to find a
> > > > store within "x" miles of a specific zip code. Has anyone done
> this
> > > before
> > > > and if so, would you mind lending some guidance? If anyone
> knows of
> > > a custom
> > > > tag to do this, that would great.
> > > >
> > > > Thanks,
> > > >
> > > > Rey Bango
> > > > Team Allaire...
> > > >
> > > >
> > >
> >
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
