
Unsure as to the table structure of the 2 tables i
I found this script that finds zip codes within a user defined radius. I'd love to use it, but I cannot figure out the structure of the 2 tables mentionned in the SP:<BR><BR>/**<BR>Here is the stored procedure which I have used in calculating distance but it is for SQL server 2000, modify it to your needs.In this procedure I am using t_Zipcode table and t_Distributors table in which I have the distributor information along with their zipcode.<BR>**/<BR><BR>CREATE PROCEDURE getAllDistributorsByZip<BR>@zip varchar(50),<BR>@intRadius numeric<BR><BR>AS<BR>Declare @lg1 realDeclare @l1 realSelect @l1 = lat,@lg1 = lon<BR><BR>From t_zipCode<BR>Where zip = @zip<BR><BR>Select d.*,@zip as Origin, @l1 as olat,@lg1 as olng,convert(numeric,((1.852 * 60.0 * ((acos((sin(RADIANS(@l1)) * sin(RADIANS(d.dist_lat)) + cos(RADIANS(@l1)) * cos(RADIANS(d.dist_lat)) * cos(abs((RADIANS(d.dist_long))(RADIANS(@lg1))))))/3.14159265358979323846)*180)) / 1.609344)) as Distance<BR><BR>From t_distributors d<BR>Where convert(numeric,((1.852 * 60.0 * ((acos((sin(RADIANS(@l1)) * sin(RADIANS(d.dist_lat)) + cos(RADIANS(@l1)) * cos(RADIANS(d.dist_lat)) * cos(abs((RADIANS(d.dist_long))(RADIANS(@lg1))))))/3.14159265358979323846)*180)) / 1.609344)) < @intRadiusand convert(numeric,((1.852 * 60.0 * ((acos((sin(RADIANS(@l1)) * sin(RADIANS(d.dist_lat)) + cos(RADIANS(@l1)) * cos(RADIANS(d.dist_lat)) * cos(abs((RADIANS(d.dist_long))(RADIANS(@lg1))))))/3.14159265358979323846)*180)) / 1.609344)) >= 0<BR><BR>GO<BR><BR><BR>Can someone please help???

That's actually horrible overkill...
...and pretty damned inefficient, to boot.<BR><BR>Using NUMERIC instead of REAL is a big mistake, to start. And storing the latitude and longitude in the DB in raw (degrees) form is a silly thing to do.<BR><BR>But anyway...<BR><BR>The ZIP table primarily consists of zipcode, latitude, and longitude, for each zipcode. Something simple like<BR> 90717, 120.73181, +40.18271<BR><BR>[numbers completely made up]<BR><BR>The only field in the distributors table that you care about is the zipcode. <BR><BR>The formula being used there to calculate the distance between two zip codes (actually, between a pair of latitudes and longitudes) is *way* overkill in all of the USA except maybe Anchorage, Alaska, on North. It's accurate down to probably one or two *feet* all over the surface of the Earth.<BR><BR>And it's just silly to even *try* to get that accurate. For example, the Zip code that we live in covers an area roughly 7 miles wide by 12 miles long. And we live near the southern boundary of it. So when, for example, I go looking for the nearest Home Depot, stupid systems like this one inevitably give me one that is about 15 miles from our house. Even though there is another about 6 miles from my house. Because it's at the far northern boundary of its zipcode area.<BR><BR>So realistically, if you imply IGNORE the curvature of the Earth and use the good old Pythagorean Theorem you will be *PLENTY* close enough for zip code purposes.<BR><BR>In other words:<BR> distance = SQR( (lat1  lat0) ^ 2 + (lon1  lon0 ) ^ 2 )<BR><BR>And forget all the rest of that crap.<BR><BR>Now, the distance you get from that formula will be measured in degrees, so you will need to multiply that by some value to convert it to miles, if you care. Or, the easier option, convert all the latitude/longitude pairs from degrees to miles in the first place! And for that ONE TIME calculation, you *COULD* use the more accurate formulas.<BR><BR>ANYWAY...<BR><BR>Even if you *do* decide to go with the ugly long formula, *AT LEAST*:<BR><BR>(1) Make sure that the lat/long values stored in the DB are *ALREADY* in RADIANS (not degrees) and are stored in REAL (DOUBLE) fields.<BR><BR>(2) For even better efficiency, *ALSO* store the SIN and COS of each latitude and longitude in their own fields, so that you don't have to do the INCREDIBLY EXPENSIVE sin() and cos() function calls at runtime.<BR><BR>(3) Precalculate values such as 180 / 3.14159265358979323846)*180 and other constant numbers.<BR><BR>But, really, all these people who use those kinds of formulas aren't dealing with real world problems. Try analyzing what is happening and simplify, simplify, simplify.<BR><BR>

RE: What about this??
That was seeing straight through the problem. Thanks.<BR><BR>I have these 2 simpler functions. can you tell me if it is accurate enough? simple as it seems.<BR><BR>Function GetDealers(dRadius, dLNG, dLAT)<BR><BR> Dim rsTemp, sSql<BR> Set rsTemp = Server.CreateObject("ADODB.Recordset")<BR><BR> **SQL Server vesion  use ACOS()**<BR> sSql = "SELECT name, phone, addr1, city, state, zip " & _<BR> "FROM dealer " & _<BR> "WHERE zip in ( SELECT ZIP_CODE FROM zip "& _<BR> "WHERE @radius > 3959 * ACOS(SIN(@lat/ 57.3) * SIN(LAT/ 57.3) + " & _<BR> "COS(@lat/ 57.3) * COS(LAT/ 57.3) * " & _<BR> "COS((LNG/ 57.3)  (@lng/ 57.3))) ) " & _<BR> "ORDER BY name"<BR><BR> sSql = Replace(sSql, "@radius",dRadius)<BR> sSql = Replace(sSql, "@lng",dLNG)<BR> sSql = Replace(sSql, "@lat",dLAT)<BR> rsTemp.Open sSql, sCN<BR> Set GetDealers = rsTemp<BR><BR>End Function<BR>'================================ ===================================<BR>Sub SetCoordinates(sZip)<BR><BR> Dim rsTemp, sSql<BR> Set rsTemp = Server.CreateObject("ADODB.Recordset")<BR><BR> sSql = "SELECT LAT, LNG FROM zip WHERE ZIP_CODE = ':1'"<BR> sSql = Replace(sSql, ":1",sZip)<BR> rsTemp.Open sSql, sCN<BR> If not rsTemp.EOF Then<BR> dLAT = rsTemp("LAT")<BR> dLNG = rsTemp("LNG")<BR> End If<BR> Set rsTemp = Nothing<BR> <BR>End Sub

If you want to simply use...
...code that you find on the web and don't really care about learning how it works or how efficient it is or how it impacts your server performance, then go for it.<BR><BR>

RE: You're a bit Hard there...
Why do you think that? That's not why I came to ask here. If I just wanted to do that, I would have bought a fully functional app that did the trick for me. I didn't know how to do it, wnated to understand, looked for a starting point and asked you if it was a good starting point.<BR>I understood what you ment with accuracy not being The relevant issue when it comes to this issue, but since I do not easily understand the strict math that's behind the calculation, I can only ask someone who does. Not meaning I haven't understood the main point behind it.<BR><BR>I would deduce from your last answer that you still thought the calculation(eventhough less strict than the previous one I posted), is still too "accurate", right?<BR><BR>please don't take it wrong, I am just trying to understand.

I didn't mean it that way...
It really is perfectly okay to use code you find that does the job you need done. Realistically, the only thing wrong with that code you found is that it puts a big load on the database when you execute the query. But if you are in a situation where the query won't be used often, then what difference does that *really* make??? The user is going to see the answer within a second or two or three, and that's probably fine for many many sites.<BR><BR>It really *IS* your *CHOICE* whether to dig deeper or not, so far as I am concerned. You don't have to listen to me, and I'm not offended if you don't. But you *did* post to the ADVANCED forum, so I was trying to give you what I considered an advanced answer.<BR><BR>Actually, I hadn't even finished the answer yet. There are more tricks you can use to improve the performance of this kind of query. And I'm quite willing to share them, but if you aren't interested, that's perfectly okay with me, too.<BR><BR>In answer to the question in your second paragraph: Yes, any calculation that requires computing SIN and COS even once per record is too "expensive" in my opinion. If you *need* really accurate results, I believe that the better way is to *PRE SELECT* those zip codes that are "candidates" for being within the required radius and then run the more accurate calculations on only those zips. <BR><BR>Oh, what the heck: The way I suggest is to create an imaginary square that is roughly 3 times the required radius on each side. That way it will *comfortably* include all locations within the required radius, right?<BR><BR>So let's say that there are roughly 50 miles per degree of latitude or longitude for locations in the continental USA. [It's 67 miles per degree at the equator.] And let's say that your user wants locations within 100 miles. So create a square that is 300 miles on a side, centered on his/her zip code.<BR><BR>First, look up his/her zip in the zip table. Get the lat and long in degrees.<BR> Set RS = "SELECT * FROM zips WHERE zipcode=" & Request("zip")<BR> lat = RS("latitude")<BR> lon = RS("longitude")<BR><BR>Now create the rectangle bounds:<BR> CONST MPD = 50 ' guess at miles per degree<BR> radius = Request("radius")<BR> delta = 1.5 * ( radius / MPD )<BR><BR> minlat = lat  delta<BR> maxlat = lat + delta<BR> minlon = lon  delta<BR> maxlon = lon + delta<BR><BR>And then look for *only* zip codes within that bounding box:<BR> SQL = "SELECT * FROM zips " _<BR> & " WHERE latitude BETWEEN " & minlat & " AND " & maxlat _<BR> & " AND longitude BETWEEN " & minlon & " AND " & maxlon<BR><BR>And *NOW* you can do the FINER calulations (*if* you want to) *ONLY* on the zip codes found by that last query.<BR><BR>Whether you do the finer calcs in SQL (by passing the zips found by that to another external query) or in VBScript doesn't really matter. The point is that now you have *SIGNIFICANTLY* reduced the database server load. You won't be checking for the exact distance from zip code 98004 to zip code 01183 when the user asked for a radius of 50 miles and they are roughly 3000 miles apart.<BR><BR>Does that make sense?<BR><BR>

Hmmm...interesting...
*IF* I just did the match correctly, it appears that at 30 degrees North (roughly southern border of continental US) there are about 60 miles per degree and at 50 degrees North (roughly northern border) there are about 45 miles per degree.<BR><BR>So it might be worth using a bit of math to do a rough calculation of the milesperdegree for the target zip code, rather than just using a constant. And probably worth using different figures for the latitude and longitude milesperdegree.<BR><BR>

Should have said...
...what the rough calculation is:<BR><BR>latitudeMilesPerDegree = ( 8000 * cos(latitude_in_radians) * PI ) / 360<BR><BR>8000 for diameter of Earth is surely close enough for government work.<BR><BR>For longitude, you can probably just use 68 or so all the time, I would think. <BR><BR>

RE: Should have said...
That makes perfect sense Bill. the query will be used extensively, so I really need it to be cost efficient on the db and be fast. I will try to come up with something keeping in mind all the elements you gave me and represent it to you. I really apreciate the time, effort, patience and thorougness on this, Bill.<BR><BR>Ibrahim<BR><BR>

One last comment...for now...
I mentioned in one post that among the "tricks" you can pull is precalculating the sin() and cos() of the latitude and longitude as they are needed and used in that messy calculation. Then you just pull <BR> cos_lat<BR>from the table instead of having to do the very expensive<BR> cos( Radians(lat) )<BR><BR>In general, the more precalcs you can do and stuff into the DB table, the better. This will *NOT* be a gigantic table, and having a few "extra" REAL values per record is not gonna be any big deal. And do remember to make everything REAL; the CPU hardware supports that whereas DECIMAL has to be done in software...orders of magnitude slower.<BR><BR>
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

