Unsure as to the table structure of the 2 tables i

# Thread: Unsure as to the table structure of the 2 tables i

1. Member
Join Date
Dec 1969
Posts
94

## Unsure as to the table structure of the 2 tables i

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## 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&#039;s accurate down to probably one or two *feet* all over the surface of the Earth.<BR><BR>And it&#039;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&#039;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&#039;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&#039;t dealing with real world problems. Try analyzing what is happening and simplify, simplify, simplify.<BR><BR>

3. Member
Join Date
Dec 1969
Posts
94

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 &#062; 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>&#039;================================ ===================================<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 = &#039;:1&#039;"<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

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## If you want to simply use...

...code that you find on the web and don&#039;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>

5. Member
Join Date
Dec 1969
Posts
94

## RE: You're a bit Hard there...

Why do you think that? That&#039;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&#039;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&#039;s behind the calculation, I can only ask someone who does. Not meaning I haven&#039;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&#039;t take it wrong, I am just trying to understand.

6. Senior Member
Join Date
Dec 1969
Posts
96,118

## I didn't mean it that way...

7. Senior Member
Join Date
Dec 1969
Posts
96,118

## 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 miles-per-degree for the target zip code, rather than just using a constant. And probably worth using different figures for the latitude and longitude miles-per-degree.<BR><BR>

8. Senior Member
Join Date
Dec 1969
Posts
96,118

## 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>

9. Member
Join Date
Dec 1969
Posts
94

## 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>

10. Senior Member
Join Date
Dec 1969
Posts
96,118

## One last comment...for now...

I mentioned in one post that among the "tricks" you can pull is pre-calculating 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 pre-calcs 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
•