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
