Create new 2-d array from existing array 1

[hererxnl]

I'm creating an array using the GetRows method from my access db like this:

SQL = "SELECT [uid], [username], [zipcode], [lat], [lng] FROM [users];"
adoConn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0; DATA SOURCE=" & Server.MapPath("users.mdb") & "; User ID=admin; Password=;"
adoRS.Open SQL,adoConn, 1, 1
Dim arrUserMiles
arrUserMiles = adoRS.GetRows
Set adoRS = Nothing
Set adoConn = Nothing

Like another recent post my goal is to determine the distance between a users current location and the location of other users. I'm using a similiar function, which is called like this:

Distance(strMyLat, strMyLng, strOtherLat, strOtherLng)

The problem is that I want to sort the distance and display the closest users first. I'm imagining I'll need to create another array as I don't see a way to incorporate the Distance function into my Select statement.

Is that correct or the best way to do this? Some help would be, as always, greatly appreciated.

 

[Chopstik]

I'm sorry, but there's not enough information for anyone (or at least for me) to give you good assistance. My understanding is that you are returning the latitude and longitude (among other values) from your database and then you are manually calculating the distance in your ASP file. Are you unable to perform the calculation in your SELECT statement from your DB up front and then sort from there? Without knowing what the calculation actually is, it's hard to tell you for certain whether it is possible or not - but to my way of thinking, that would be the best way of handling it. More code may be of benefit here...

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[hererxnl]

Choptik:

Sorry if there was a lack of info on my part. Your understanding is correct. I don't see a way to include the calcuation in the SELECT statement because it involves the data the statement is built to request.

Here are the functions I'm using to calculate distance between Zip Codes:

Function Distance(Latitude1, Longitude1, Latitude2, Longitude2)
    Dim R, Lat, Lon, a, c
        'Radius of the Earth (Miles)
        R = 3956.4538
        'Calculate the Deltas...
        Lon = AsRadians(Longitude2) - AsRadians(Longitude1)
        Lat = AsRadians(Latitude2) - AsRadians(Latitude1)
        'Intermediate values...
        a = Sin2(Lat / 2) + Cos(AsRadians(Latitude1)) * Cos(AsRadians(Latitude2)) * Sin2(Lon / 2)
        'Intermediate result c is the great circle distance in radians...
        c = 2 * Arcsin(GetMin(1, Sqr(a)))
         'Multiply the radians by the radius to get the distance in specified units...
        Distance = R * c
End Function
Function AsRadians(Degrees)
    'To convert decimal degrees to radians, multiply
    'the number of degrees by pi/180 = 0.017453293 radians/degree
   AsRadians = Degrees * (3.14159265358979 / 180)
End Function
Function Sin2(X)
    Sin2 = (1 - Cos(2 * X)) / 2
End Function
Function Arcsin(X)
    Arcsin = Atn(X / Sqr(-X * X + 1))
End Function
Function GetMin(X, Y)
    If X <= Y Then
        GetMin = X
    Else
        GetMin = Y
    End If
End Function

Any help is greatly appreciated. I'd be glad to provide more or try to explain it a different way

Thanks

 

[Chopstik]

Now I'm reminded of why I didn't like math as a child...

But, that being said, I *think* (I'm not exactly a DB expert) that this can be done more easily in a SQL or Oracle database (though I don't know about Access). If you don't have access to a non-Access database, you may have to use what you already have. But I'd look in one of the Access forums to see if this formula can be replicated in Access. That would be the preferred solution.

On the other hand, if that is the case, are you planning on running this calc for all values that are returned and then wanting to sort from there? I don't know but that seems not very efficient to me. Problem is, I'm not thinking of a better solution at the moment. I'll try to think on it some more and let you know if I come up with something better.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[hererxnl]

"are you planning on running this calc for all values that are returned and then wanting to sort from there" - yes

"that seems not very efficient to me" - precisely why I'm asking.

This has froze my brain.

 

[Chopstik]

I've taken a brief look at Access (I'm running 2003) and I think this is possible to do there. As I mentioned earlier, it may not be the most efficient (or very quick) but it's probably better than trying to do it in ASP.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[hererxnl]

Do you mean using a stored procedure? This is outside the scope of my use of Access, could you elaborate?

 

[kss444]

I am not a sql server guru but I beleive you can take your functions and create them in sql server as User Defined Functions. In sql manager click on the database.

For help doing this you might want to try sql server forums.

http://www.tek-tips.com/threadminder.cfm?pid=183

 

[hererxnl]

Unfortunately the db is Access, for now. That's how it was requested.

 

[Chopstik]

Not necessarily. You would have to create a rather complex query but I think it could be done in Access. Unfortunately, I am not an Access person and you might have better luck asking in one of the Access forums for assistance on how best to re-create your calculation in Access. (When and if you do get it working, though, I -and probably others - would be interested in seeing the results. I'll try to take a stab at it if I have some free time later in the week.) You might try either forum181 or forum701.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[hererxnl]

Chops: Thanks for the direction, I'm looking into this further and will definitely post my results. I think I named this post wrong to begin with. Should have been "Sorting Multi-Dimensional Arrays" or something like that as that's proving to be the real challenge.

Seems that running my distance calc in a For Next doesn't kill performance but I've only tested for about 1000 records, so we'll see.

Here's my current approach from top to bottom:

SQL = "SELECT [uid], [username], [zipcode], [lat], [lng] FROM [users];"
adoConn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0; DATA SOURCE=" & Server.MapPath("users.mdb") & "; User ID=admin; Password=;"
adoRS.Open SQL,adoConn, 1, 1
Dim arrUsers(), r
arrUsers = adoRS.GetRows
Set adoRS = Nothing
Set adoConn = Nothing
Dim arrDistance()
ReDim arrDistance(4, UBound(arrUsers))
r = 0
For r = LBound(arrUsers) To UBound(arrUsers)
    'User Name
	arrDistance(0, r) = arrUsers(1, r)
    'UID
	arrDistance(1, r) = arrUsers(0, r)
    'ZipCode
	arrDistance(2, r) = arrUsers(2, r)
    'Distance
	arrDistance(3, r) = CStr(Round(Distance(strMyLat, strMyLng, arrUsers(3, r), arrUsers(4, r)), 1))
Next

Function Distance(Latitude1, Longitude1, Latitude2, Longitude2)
    Dim R, Lat, Lon, a, c
        'Radius of the Earth (Miles)
        R = 3956.4538
        'Calculate the Deltas...
        Lon = AsRadians(Longitude2) - AsRadians(Longitude1)
        Lat = AsRadians(Latitude2) - AsRadians(Latitude1)
        'Intermediate values...
        a = Sin2(Lat / 2) + Cos(AsRadians(Latitude1)) * Cos(AsRadians(Latitude2)) * Sin2(Lon / 2)
        'Intermediate result c is the great circle distance in radians...
        c = 2 * Arcsin(GetMin(1, Sqr(a)))
         'Multiply the radians by the radius to get the distance in specified units...
        Distance = R * c
End Function
Function AsRadians(Degrees)
    'To convert decimal degrees to radians, multiply
    'the number of degrees by pi/180 = 0.017453293 radians/degree
   AsRadians = Degrees * (3.14159265358979 / 180)
End Function
Function Sin2(X)
    Sin2 = (1 - Cos(2 * X)) / 2
End Function
Function Arcsin(X)
    Arcsin = Atn(X / Sqr(-X * X + 1))
End Function
Function GetMin(X, Y)
    If X <= Y Then
        GetMin = X
    Else
        GetMin = Y
    End If
End Function

All of this seems to work just fine. The sort, however I haven't had any luck with and, as I understand it, can be a real problem with large arrays (read: stack errors, terrible performance, etc...).

BTW: I giving you a star as the only only willing to wade through this with me.

 

[Chopstik]

Well, like I said, now I'm intrigued. I'm going to try to remember to leave a note for myself to look at this later because I think it can be done (though it may be rather complex at the very least) - though I'm not 100% certain about Access and would recommend another DB if possible.

And yes, I would not want to try to run the calc and then sort on the front end for the reasons you've enumerated. It just seems terribly inefficient to me...

Thanks for the star. Now I'm going to have to earn it...

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[hererxnl]

For now I'm using a guitardave post found here thread333-1088342 to sort the new array. Hopefully this is the best method. It will work until I find a better way, or convince my client to use SQL.