
component or example for sorting by distance?
I'm working with some code that searches a zip code database pulling latitude and longitude, calculates the distance and then display the shops listed within that distance. Every example I've seen shows how to do all of this, but doesnt sort by the calculated distance. Using arrays arent feasible since I'm displaying 8 fields. Anybody know of a component or example that could help?

Why aren't arrays feasible?
Use a 2D array or (slightly better) an array of Arrays or (even better) an array of VBScript Class instances (of a custom class you create).<BR><BR>But how are you doing the pulling by lat and long? PLEASE tell me that you aren't pulling every blinking record in the DB and calculating distance for each one. PLEASE?<BR><BR>

RE: Why aren't arrays feasible?
well it would end up being like an 8dimensional array due to pulling name, address...etc No, I'm only pulling lat/lng where radius calculation is less than the user chosen distance.

You are confused...
It would be a *TWO DIMENSIONAL* array.<BR><BR>Eight columns by N rows, where N is the number of locations within the radius.<BR><BR>Eight columns does *NOT* make it "8dimensional". (If it did, then would 42 rows by 8 columns make it 336dimensional??)<BR><BR>**************<BR><BR>But if you are doing the radius calculation *IN THE SQL* then why don't you just ask to do an ORDER BY the radius in the same query?<BR><BR>

RE: Because the radius is a param
I'm passing the radius as variable chosen by the user.

I *know* that...
...I'm not talking about the *test* radius; I'm talking about the *calculated* radius. Just ORDER BY that!<BR><BR>

RE: I dont think thats possible...
the code starts with this:<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><BR>Then process with this:<BR><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 * ATN( SQR(1  (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))) ^2 ) /" & _<BR> "(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 zip"<BR><BR> sSql = Replace(sSql, "@radius",dRadius)<BR> sSql = Replace(sSql, "@lng",dLNG)<BR> sSql = Replace(sSql, "@lat",dLAT)<BR> rsTemp.Open sSql, sCN, 3<BR> <BR> Set GetDealers = rsTemp<BR><BR>Then calculates the distance with this:<BR><BR> sSql = "SELECT LAT, LNG FROM zip WHERE ZIP_CODE = ':1'"<BR> sSql = Replace(sSql, ":1",sZip)<BR> rsTemp.Open sSql, sCN<BR> <BR> If not rsTemp.EOF Then<BR> cLAT = rsTemp("LAT")<BR> cLNG = rsTemp("LNG")<BR> <BR> 'response.write "Debug: " & cLAT & "<BR>" & cLNG & "<P></P>"<BR><BR> '@@@@@@@@@@@@@@@@@@@@@@@@ BEGIN DISTANCE CALCULATION @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ <BR> Dim objZipDist<BR> Set objZipDist = Server.CreateObject("ZipCodeDownload.DistCalc")<BR > <BR> Dim dblLatitude1<BR> dblLatitude1 = dLAT<BR> <BR> Dim dblLongitude1<BR> dblLongitude1 = dLNG<BR> <BR> Dim dblLatitude2<BR> dblLatitude2 = cLAT<BR> <BR> Dim dblLongitude2<BR> dblLongitude2 = cLNG<BR> <BR> 'response.write "Debug: " & dblLatitude1 & " " & dblLongitude1 & " " & dblLatitude2 & " " & dblLongitude2<BR> <BR> Dim dblMiles<BR> dblMiles= objZipDist.GetDistance(dblLatitude1,dblLongitude1, dblLatitude2,dblLongitude2)<BR> <BR> Response.Write dblMiles & " mi."<BR><BR>Finally in the body, loop through the 2nd part pulling the records matched and writing the 3rd part's miles accordingly.<BR>Therefore the calculations are done in a component at the end, so I cant order by "calculated" radius.<BR><BR>

Yeah...might not be worth it...
<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 * ATN( SQR(1  (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))) ^2 ) /" & _ <BR> "(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>zip" <BR> "ORDER BY 3959 * ATN( SQR(1  (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))) ^2 ) /" & _ <BR> "(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> ", zip"<BR><BR>But it would work.<BR><BR>I have to tell you, this is putting one heluva strain on the SQL engine! (Even without my ORDER BY.)<BR><BR>At a minimum, I think you ought to "prequalify" the records to be tested via this complex formula by building LAT/LONG limits to search in (e.g., if the radius is 50 miles, then choose worst case min and max LATs and LONGs and select only records within that (approximate) square. *THEN* subject only records that pass that test to the actual radius test. <BR><BR>And store the LATs and LONGs in the DB in *radians* instead of degrees (or in addition to, if need be) so you don't have to do the silly divide by 57.3 all the time.<BR><BR><BR><BR><BR>

Sorry about the random zip...
... copied it but forgot to delete it.<BR><BR>

Actually...
...you could improve the hell out of the performance of that by adding four new fields to your table:<BR> COSLAT, COSLNG, SINLAT, and RADLNG<BR><BR>And store COS(LAT/57.3) in COSLAT, etc.<BR><BR>Because SIN and COS functions are *SLOW SLOW SLOW*, even on Pentium4 machines (well, compared to other things).<BR><BR>And pass in COS(@lat/57.3) instead of just @lat, too.<BR><BR>"WHERE @radius > 3959 * ATN( SQR(1  (@sinlat * SINLAT + " & _ <BR> "@coslat * COSLAT * " & _ <BR> "COS(RADLNG  @radlng)) ^2 ) /" & _ <BR> "(@sinlat * SINLAT + " & _ <BR> "@coslat * COSLAT * " & _ <BR> "COS(RADLNG  @radlng)) ) ) " & _<BR><BR>I bet it executes 3 to 10 times faster.<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

