For this formula, the circle touches the center of each side. Steve
-----Original Message----- From: Greg McDaniel [mailto:[EMAIL PROTECTED] Sent: Monday, September 08, 2003 12:22 PM To: SQL Subject: RE: I need some indexing advice Just curious. Does that formula guarantee that the circle always fits inside the square? Or does that formula end up touching the outer edges of the circle? Greg -----Original Message----- From: Steven Monaghan [mailto:[EMAIL PROTECTED] Sent: Monday, September 08, 2003 10:26 AM To: SQL Subject: RE: I need some indexing advice You could also try and do some creative math to reduce your results before doing the actual calculation. You are trying to find all of the zip codes within a circle of a known radius, right? So, draw a square around that circle, and reduce your result set by that first. That calculation, instead of being very complicated, should be nothing more than: lat between (yourlat - radius) and (yourlat + radius) and long between (yourlong - radius) and (yourlong + radius Then you can index lat and long, add the correct interpretation of the above to the top of your where clause, and see how it goes. I haven't actually done this, and someone else may tell you this won't work, but let me know how it goes. Thanks, Steve ------------------------------------- Steven Monaghan Oracle DBA / Cold Fusion Developer MSC Industrial Direct Co., Inc. http://www.mscdirect.com ------------------------------------- -----Original Message----- From: Raster, Tim [mailto:[EMAIL PROTECTED] Sent: Monday, September 08, 2003 11:00 AM To: SQL Subject: RE: I need some indexing advice Well, off the cuff, I'd index zips.zip for the 1st query. Your 2nd query is probably destined to be slow (depending on how many records are in dealers_geo). I don't see a lot of opportunity to index that one, because you are forcing a lot of math calculations on each row, and then applying your whereclause to that math, not a field in the table. I see 2 choices for you.... 1. Try to divide the 2nd query's workload by 50 by pulling by state first. For example, if the user pulled 14000 zipcode, you know to only pull state of NY stuff (and maybe its surrounding states, which would usually only had about 5-6 more). In other words, don't bother calculating and searching for all 50 states worth of dealers... So add a column to your dealers table that holds the State, and pass that as additional Whereclause criteria, and index that column. That can help your query greatly. 2. I did one of these once. I pre-calculated all zipcodes' distances from each other, and only kept the ones that were <= 100 miles (or some number). I jammed those into a table that had 3 columns in it: Zip, TargetZip, and DistanceInMiles. It had almost 10 million rows in it, but this is data that does not change (rarely anyway), *and* I pre-calc all the math stuff once, and store the results, so no more expensive math within each search. I built an index Zip column, and another on (Zip, TargetZip, DistanceInMiles) columns. This allowed me to pull together a query of the starting Zip (what the user wanted), target zips (in this case, your dealers in the area), and put some where clause criteria where DistanceInMiles < [some number]. This turned out to be VERY fast (like fractional seconds for searches). Its only drawback is it takes a little diskspace (about 4 gb for mine, but probably less because I didn't even try to keep disk down), and a lot of up front work to pre-calc the stuff (it took me 4 Pentium 2 200-400 PC about 3 days to compute it all, back then). -----Original Message----- From: Phillip B [mailto:[EMAIL PROTECTED] Sent: Monday, September 08, 2003 09:43 To: SQL Subject: I need some indexing advice This is what I have done. I took a zipcode table and my dealer table and made a view that pretty much joins the two on the zip code. Neither of these are indexed yet. I then wrote the following two queries to find dealers. The second query can take almost 8 seconds to run and is the view of the dealer and zip code table. I'm not that comfortable with indexing my tables yet so I was looking for some advice on what to index in the tables. SELECT zip, lat, long, city, state FROM zips WHERE zip='#form.passedzipcode#' SELECT TOP 15 lat, long, name, city, state, zipcode, area_code, phone, 3963 * (ACOS((SIN(#passedzip.lat#/57.2958) * SIN(lat/57.2958)) + (COS(#passedzip.lat#/57.2958) * COS(lat/57.2958) * COS(long/57.2958 - #passedzip.long#/57.2958)))) AS distance FROM dealers_geo #WHERE# 3963 * (ACOS((SIN(#passedzip.lat#/57.2958) * SIN(lat/57.2958)) + (COS(#passedzip.lat#/57.2958) * COS(lat/57.2958) * COS(long/57.2958 - #passedzip.long#/57.2958)))) <= #form.passedradius# ORDER BY distance Phillip B. www.LoungeRoyale.com www.FillWorks.com ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:6 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=<:emailid:>.<:userid:>.<:listid:> Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
