multi dimensional array sorting, 1st element is a

Thread: multi dimensional array sorting, 1st element is a

1. Junior Member
Join Date
Dec 1969
Posts
5

multi dimensional array sorting, 1st element is a

I&#039;m reading a recordset in from a database and initially the first field is a blank. I assign the query results to an array using getrows. The array is 15 columns by &#039;n&#039; rows (where n can be 1 to 160,000). Next I process through the array and calculate distances then store those distances in the first element in the array. The distance can be anything from 0 to 999.99 miles. Now I need to sort on the first element in the array so that I can display the records in ascending order. I&#039;ve not found a single piece of code that works and I&#039;ve tried them all... I need help in finding a sort routine which will work on multi-dimensional arrays in vbscript.<BR><BR>Thanks, Phil

2. Senior Member
Join Date
Dec 1969
Posts
3,195

Silly question but is there

something preventing you from doing the distance calculation in the SQL itself? Then you can simply do an ORDER BY plus SQL does your work for you ; )

3. Senior Member
Join Date
Dec 1969
Posts
3,195

As an aside

160,000 records is an awfully large number of records to be manipulating in an ASP page/script. My point is this is better suited to do in batch like a windows shell script (which you may doing already..I don&#039;t know).<BR><BR>Good luck<BR>Pete

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

Pete's points are well taken...

160,000 records in memory in a 2D array would take a *looonnng* time to sort in VBScript, no matter what algorithm you used.<BR><BR>Not to mention a *ton* of memory.<BR><BR>The real problem with VBS 2D arrays is that, to swap the rows when you are sorting the array, you have to swap *all the individual columns* in the pair of row! We are talking a *LOT* of time to simply swap two rows. Do that a few million times, as you will need to in order to sort 160,000 rows...<BR><BR>Anyway, if you really think you need to do this, you can. You could use a QuickSort or maybe HeapSort algorithm. I *would* suggest that if you do it you use an *auxilliary* array (basically, an array of pointers to the rows of the main array) and do your sorting by swapping elements in the auxilliary array. Granted, it means an extra level of indirection in each comparison. I *think* (but I admit I don&#039;t know) that this will more than compensated for the time needed to do the row swaps if you go the other way.<BR><BR>But do reconsider: Isn&#039;t there a way you could fill in these "blank" fields using a SQL query, instead?<BR><BR>

5. Junior Member
Join Date
Dec 1969
Posts
5

RE: Silly question but is there

&#060;g&#062; Funny you should ask that... Well, nothing other than A) a complete and total lack of know how on my part.<BR><BR>The records are not contained in a SQL server (maybe late fall) but instead in a (*GASP*) Visual FoxPro 6.x database (I know, I know, but I had nothing to do with that part).<BR><BR>Basically I get passed in a record ID, I query the database and get &#039;n&#039; records back. I read the records into the array, pick through the array until I find the row with the id matching the id passed in (Session variable) and use those variables to initialize my starting lat/lon. From there I just process through the array calculating the distance for each element from the starting lat/lon. Finally I have to display the results. I can display everything sorted (because I can do an ORDER BY) on everything but the distance as that is always an unknown and dependent on the lat/lon of the starting record. I need to special case the sort on the distance. (FYI we&#039;re determining all doctors within &#039;x&#039; miles of a starting doctors office.).<BR><BR>It was hard enough to get the math to work in VBScript as certain key functions were missing and had to be improvised (for instance VBScript doesn&#039;t have an acos() and we had to write our own.).<BR><BR>Agreed that 160,000 records is a large number but that is absolutely worst case. 90% of all cases it will be less than 5,000 and 90% of those will be less than 3,000.<BR><BR>I looked at doing a sort on a second array containing the distance and record position and then using that to output the data but somehow just got lost in the translations... Now, I could do all this in about 30 minutes in C but that&#039;s just not a real option... sigh...

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

Horrible horrible way to do this!

If you have to search through more than 300 doctors, you are doing something DESPARATELY wrong.<BR><BR>This has been discussed in this forum before. Or maybe in the Databases forum. You might try searching for "doctor distance" to see what hits you get in the various forums.<BR><BR>And you sure as hell do *NOT* need ACOS if you do it the "smart" way.<BR><BR>

7. Senior Member
Join Date
Dec 1969
Posts
10,852

But this maybe help?<BR><BR>http://www.aspmessageboard.com/forum/showMessage.asp?F=20&M=254236&P=1

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

Yep...and since then...

I have definitely decided you *MUST* do the prequal step.<BR><BR>In other words, say you wanted all doctors within 50 miles.<BR><BR>First, you go get the lat/long of the target point.<BR><BR>Then you figure out how many degrees/minutes/seconds corresponds to 50 miles longitudinally and ditto latitudinally from the target point. Realistically, you can use the same numbers for anywhere in the USA except maybe Alaska. (That is, say 50 miles equals 0.35 degrees, any direction, no matter where. So maybe that&#039;s really 72 miles of longitude in South Florida while it&#039;s 50 miles in Seattle...who cares...this is just to do a rough filter.)<BR><BR>SO you get *only* the records that pass that filtering:<BR><BR>SQL = "...WHERE Lat &#062;= " & (targetLat-0.35) & " AND Lat &#060;= " & (targetLat+0.35) _<BR>&nbsp; &nbsp; &nbsp; " AND Long &#062;= " & (targetLong-0.35) & " AND Long &#060;= " & (targetLong+0.35) ...<BR><BR>And then you just use, on ONLY those records...<BR><BR>WHERE (lat-targetLat)*(lat-targetLat) + (long-targetLong)*(long-targetLong) * factor &#060; 2500<BR><BR>[No point in taking the square root...just square the target distance instead. And, of course, factor is something you come up with that gets you as close as is reasonable for that lat and long. But you get to calculate it *outside* the SQL [or just get it out of a table...probably only need a different value every 5 degrees or so].<BR><BR>Is this perfectly accurate? Of course not. Sometimes you&#039;ll end up with a doctor who is 54 mile away. Whoopee.<BR><BR>Unless you are entering the *actual* lat/long of the doctor&#039;s offices *AND* the *actual* lat/long of the place the person is driving from (residence?), who cares? I know, for example, that my own zip code covers roughly 9 miles North to South and 7 miles West to East. So if all I give you is my zip code, I could be as much as 11 miles away from a doctor who shares the same zip code!<BR><BR>This is one of those cases where "close enough will do."<BR><BR>

9. Junior Member
Join Date
Dec 1969
Posts
5

RE: Yep...and since then...

My mistake... I guess I somehow led you to believe that I was calculating distances for end users which is not the case. My client is involved in the brokering of medical practices and often times we&#039;re dealing with non-compete agreements which restrict a selling doctor from opening or acquiring a new practice within &#039;x&#039; miles of the practice they are selling. So, in this case a reasonably accurate distance does matter.<BR><BR>Also, as a side note, the formula I&#039;m using operates a little differently in that we calculate the distance to the center of the earth for both the starting and ending points and from there calculate the distance between points. This is much more accurate than having to worry about the diffent longitudinal bands. Even though realistically we&#039;ll never go beyond 2 decimal places, the owner of the company wants/demands the accuracy.<BR><BR>I agree that this is not the best way to do this but again I&#039;m coming late to the party and am stuck with working within the boundaries I was given.

10. Junior Member
Join Date
Dec 1969
Posts
5

RE: Pete's points are well taken...

Again agreed, it&#039;s always easier to sort an array of pointers than an array of data so I did begin working on building an auxilliary array and sorting that LATE last night... I think I need to go back to beginners basic school... lol<BR><BR>&#039; create a new array, fill the 1st element with the mileage<BR>&#039; and the 2nd with the record # in the original array.<BR>dim aMiles(), z<BR>redim aMiles(1, Ubound(allRows, 2))<BR>For z=0 To nRows<BR> aMiles(0, z) = allRows(F_Miles, z)<BR> aMiles(1, z) = z<BR>Next<BR><BR>&#039; if sorting by distance (vs. street address or last name)<BR>If sort="distance" then <BR> &#039; "multi-dimensional" quicksort routine from 4guys<BR> &#039; fields passed are: array, 0, 2, sortonfield = 0 for miles<BR> call QuickSort(aMiles, lBound(aMiles), uBound(aMiles, 1), 0)<BR>End If<BR><BR>The above produces nothing more than an array sorted by row number, not ever by mileage and in fact the rows columns get swapped. so that aMiles(0, z) produces the row number and aMiles(1, z) produces the mileage.<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
•