Running SQL statement from within a function called from a query (1 Viewer)

Blueskies

Registered User.
Local time
Today, 06:01
Joined
Oct 23, 2009
Messages
60
Hi

I've not tried to do this before and it's not currently working, so may be not possible, but here's what I'm trying to achieve:

I've got 2 tables - an address list and a restaurant list. I've got longitude and latitude values for all records, and I'm trying to flag each address with it's nearest restaurant. I can do this by using a couple of recordsets, but there are 5 million addresses and 3000 facilities and it takes about 10 hours to run, so I'm looking to use queries instead to see if it speeds things up.

So here's the query for my address list:

Code:
UPDATE [DATA 1] SET [DATA 1].Id_and_distance = update_address([lat],[lon]);

... and here's the 'update_address' function:

Code:
Function update_address(add_lat As Double, add_lon As Double) As String

    Dim nearest_rest As String
    Dim nearest_rest_dist As Double
    Dim rest_tab As String
    
    rest_tab = "Restaurants"

    DoCmd.RunSQL "UPDATE [" & rest_tab & "] SET [" & rest_tab & "].[temp distance] = distance(" & add_lat & "," & add_lon & ",[lat],[lon]);"
        
    nearest_rest = DLookup("[Nearest]", "Nearest Restaurant")
    nearest_rest_dist = DLookup("[Nearest Dist]", "Nearest Restaurant")
    
    update_address = nearest_rest & ":" & nearest_rest_dist
    
End Function

The function returns the nearest restaurant ID and the distance as a string separated by a colon - I thought it would probably be quicker to do this and then run an update query later to separate the ID and distance.

The 'distance' function is just something to get the distance between 2 sets of longitude and latitudes and the 'nearest_rest' bit just looks up the lowest distance from the restaurant table and returns it so I can flag the address with the right site.

The problem is that the function won't let me do the Run.SQL statement. I get the error:

You can't carry out this action at the present time

So it seems I can't run an SQL statement from within a function called by my query.

Can anyone suggest a way around this please?

Thanks!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:01
Joined
May 7, 2009
Messages
16,881
have you tried using Recordset to update the table:
Code:
Function update_address(add_lat As Double, add_lon As Double) As String

    Dim nearest_rest As String
    Dim nearest_rest_dist As Double
    Dim rest_tab As String
    
    'arnelgp
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    rest_tab = "Restaurants"

    Set db = CurrentDb
    Set rs = db.OpenRecordset("select * from [" & rest_tab & ";", dbOpenDynaset)
    With rs
        .MoveFirst
        .Edit
        ![temp distance] = distance(add_lat, add_lon, !lat, !lon)
        .Update
        .Close
    End With
    Set rs = Nothing
    Set db = Nothing
    
    'DoCmd.RunSQL "UPDATE [" & rest_tab & "] SET [" & rest_tab & "].[temp distance] = distance(" & add_lat & "," & add_lon & ",[lat],[lon]);"
        
    nearest_rest = DLookup("[Nearest]", "Nearest Restaurant")
    nearest_rest_dist = DLookup("[Nearest Dist]", "Nearest Restaurant")
    
    update_address = nearest_rest & ":" & nearest_rest_dist
    
End Function
 

SHANEMAC51

Active member
Local time
Today, 08:01
Joined
Jan 28, 2022
Messages
294
У меня есть значения долготы и широты для всех записей
это как раз то, что ближе, чем
  • 10 км по этому берегу реки
  • или на другом берегу, прямо напротив, по прямой 100 метров, только вот мост через реку в 20 км
 

Auntiejack56

Registered User.
Local time
Today, 15:01
Joined
Aug 7, 2017
Messages
168
All good, but for this to run fast, you need to utilize an array. Read through the biggest recordset from top to bottom (which is not slow if you are just reading once), and cycle through the in memory array over and over (which is fast) to find the shortest distance. You should be able to shave heaps off your 10 hours.

Code:
Function goFindClosestNosh()
Dim db As DAO.Database, aa As DAO.Recordset, rs As DAO.Recordset, strSQL As String
Dim n As Long, minDist As Double, calcDist As Double, saveID As Long
Dim arrRests

strSQL = "SELECT r.ID, r.Lat, r.Long FROM tblRestaurants as r ORDER BY r.Lat;"
Set aa = db.OpenRecordset(strSQL, dbOpenSnapshot)
arrRests = aa.GetRows(9999)

strSQL = "SELECT t.ID, t.Lat, t.Long, t.RestaurantID from tblAddresses as t;"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

Do Until rs.EOF
    minDist = 10000
    For n = 0 To UBound(arrRests, 2)
        '' calcDist = distance between rs.Lat/rs.Long and arrRests(1,n)/arrRests(2,n)
        If calcDist < minDist Then
            minDist = calcDist
            saveID = arrRests(0, n)
        End If
    Next
    rs.Edit
    rs!RestaurantID = saveID
    rs.Update
    rs.MoveNext
Loop
End Function

Jack
 

Auntiejack56

Registered User.
Local time
Today, 15:01
Joined
Aug 7, 2017
Messages
168
And please note, I have no idea how to calculate the distance between 2 lat/long pairs, so the calcDist bit is all yours!

Also, the array is in order of Lat, so you might not always have to search the whole 3000 restaurants every time. At some point in a search, the Lat of the Restaurant might be too large to get a smaller min distance, so you can Exit For, update the closest Restaurant ID and read the next address. If you can do that, you might expect to halve the search time again.
 

Slap

Registered User.
Local time
Today, 06:01
Joined
May 21, 2011
Messages
37
Should you be storing this? How do you handle a new restaurant popping up nearer to the address? Shouldn't you just always run the query?
 

Blueskies

Registered User.
Local time
Today, 06:01
Joined
Oct 23, 2009
Messages
60
Hi guys

Thanks everyone for responses - didn't expect any replies so soon!

arnelgp - I used recordsets originally, but I found in the past that queries can be quicker for some stuff, so that's what led me to this route. I may have to use a combination of both.

Auntiejack56 - I hadn't thought of arrays - that's a good idea, I'll give it a try to see if it makes things faster. No worries on the distance calculation, I picked up a formula by googling a while ago.

SHANEMAC51 - sorry, translate was a bit misleading, but I think I can answer by saying that the address and restaurant list is national, so some of the distances could be 200 miles away (no restaurants nearby if you live in the Shetland Islands!)

Slap - it's a bit of a snapshot really - my client wants a list of addresses near to his restaurants. He might not need the list updated for another 6 months, so I'll just run the process again then.
 

Auntiejack56

Registered User.
Local time
Today, 15:01
Joined
Aug 7, 2017
Messages
168
No worries. If you do try the array technique, please post any improvements in runtime. I'm a total array fan, always keen to see if they can improve things.
 

Blueskies

Registered User.
Local time
Today, 06:01
Joined
Oct 23, 2009
Messages
60
No worries. If you do try the array technique, please post any improvements in runtime. I'm a total array fan, always keen to see if they can improve things.
Will do!

Loving the name of your function by the way!
 

Eugene-LS

Registered User.
Local time
Today, 08:01
Joined
Dec 7, 2018
Messages
354
sorry, translate was a bit misleading
is exactly what's closer than
- 10 miles on this side of the river
- Or on the other shore, directly opposite, in distance of 100 yards, except that the bridge over the river is in 20 miles
:)
 

Blueskies

Registered User.
Local time
Today, 06:01
Joined
Oct 23, 2009
Messages
60
is exactly what's closer than
- 10 miles on this side of the river
- Or on the other shore, directly opposite, in distance of 100 yards, except that the bridge over the river is in 20 miles
:)
Oh I see!

The distance is as the crow flies - it's just the difference between longitude and latitude.

Anything else would require more calculations than I want to get into!
 

Blueskies

Registered User.
Local time
Today, 06:01
Joined
Oct 23, 2009
Messages
60
No worries. If you do try the array technique, please post any improvements in runtime. I'm a total array fan, always keen to see if they can improve things.
Hi

I just did some initial timings and it looks like I can now get between 600 and 625,000 records per hour - previously it was running at about 100,000.

I'll try to do some tidying up and then post the code.

Arrays for the win!

Many thanks for your input with this - really appreciate it.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:01
Joined
May 7, 2009
Messages
16,881
i don't think array will do the job for you if you have 100,000 rows.
i think the max single dim array is 65k (array elements), but surely will hog your system.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:01
Joined
May 21, 2018
Messages
6,450
I believe the array limit is much larger
2,147,483,647 elements, but i could be wrong if vba different than vb
 

Blueskies

Registered User.
Local time
Today, 06:01
Joined
Oct 23, 2009
Messages
60
Well, it is slowing down - last few lines it reported have been down to 535,000 per hour, but still a big improvement.

I haven't added the bit Auntiejack56 suggested about deciding when the lat was no longer worth checking (too far away), so that should help.

The array is just for the restaurants anyway - 3000 of those - the addresses are processed via the recordset.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:01
Joined
Feb 28, 2001
Messages
23,216
I looked it up and found that there is evidence for VBA-based arrays in excess of 65535 elements. However, given that VBA is emulated via a pseudo-compilation (vs. compiled to machine code), the speed of operation for loops through long arrays can be daunting. It doesn't help that using LAT/LON doesn't narrow it down by much, since one degree of latitude is about 70 miles. So truncating the evaluation to degrees, or even to degrees and minutes of arc, won't help much.

If you stop to think about it, "5 million addresses and 3000 facilities" is 15,000,000,000 combinations - in USA parlance, 15 billion address/facility distances to compute. The math is non-trivial, since LAT/LON computations involve floating-point math at the trigonometry level (i.e. involves SINE, COSINE, TANGENT stuff), and you would need to store LAT/LON for each of 5 million addresses and each of 3000 facilities. EVEN if you limited yourself to SINGLE (which, for LAT/LON work, isn't the best idea because of round-off issues), you are looking at 2 x SINGLE x 5 million locations + 3000 x 2 x SINGLE for each facility. BUT each address/facility combo needs indexes for each address and facility + a SINGLE for the distance, or well over 50 GB. Add to that the amount of space for conventional addresses and other info that normally rides along with this kind of data. We are talking humongous amounts of data. And Access has a 2 GB data limit. The "cheap" versions of SQL have higher limits, but many of the freebies stop at 10 GB or 16 GB.

Why did I go through this? To point out that Access can't hack that much space easily, and the computation is going to take 15 BILLION iterations of whatever complicated navigational computation you use, in an emulated environment.

BUT there is a simpler way to approach this: Don't store anything. Compute it on the fly when you need it. A single LAT/LON computation from point A to point B isn't trivial - but with GHz computers, will take at most a millisecond or two. (Remember, on a 2.6 GHz computer, 1 millisecond is right at 1 million instruction times.) Compute that distance on the fly. Store locations for when you need them.
 

Blueskies

Registered User.
Local time
Today, 06:01
Joined
Oct 23, 2009
Messages
60
Doc_Man: I agree with your reasoning - thanks for the level of detail you went into - but I do need to create the list.

The client wants to find the nearest x thousand address records to each of 3000 restaurants, so I will need to be able to produce this. They may change the x as they feel like it as well, so I need it stored so I can change the selection on the fly.

Your point about the complexity of this has been born out by the fact I've just got a corrupt database error message after running 429,000 addresses - that's the database with the actual data in of course (I've got the code in one db and linked to the data in another). It has gone over the 2gb limit.

I didn't get this originally when I was using recordsets instead of the array, so I guess I need to break things up a bit and do several loops of the code - say 100,000 records at a time maybe - to see if this helps. I assume (hope!) the back-end database will decrease in size again when it has finished each loop.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:01
Joined
May 21, 2018
Messages
6,450
@Blueskies,
You are doing a brute force method if you are calculated every distance. Unless you want the distance to all restaurants for later use. If you only need to find the nearest, there are known algorithms for finding Nearest Neighbor (NN). One of the most common is using a K-D tree to store your nodes. See this great explanation

Your solution operates in order N^2. The NN algorithm works in order LogN with worst case of order N. So assume you need to compare 1000 points. Your solution runs in 1,000,000 iterations minimum. The NN algorithm runs between 10 and 1000 iterations. So expect with a proper algorithm to theoretically run 100s to 1000s of times faster. Doing some other proper structures and short cuts will may even faster.

Any chance you can zip and post your locations? Just need lat, long, and an ID number. If you want I will see if I can implement this algorithm I have most of this structure already. If not I will run this on a random set of lat longs from the UK zip code database.
What countries are the restaurants and points in?
You have 5M addresses and for each address you want the closest restaurant out of a list of 3K?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:01
Joined
Feb 28, 2001
Messages
23,216
Here's a thought... I see you are from the UK. Your postal system has certain "locale" codes - you might call them something different, of course. We in the USA call the postal area codes "ZIP codes". If you pre-sort everything and compute distances only for things within the same postal codes, that will for a pre-filter that should help reduce the size of your data set to something manageable.

Further, if you can use your postal data to determine which postal codes are adjacent to each other, that should take care of reducing the address set considerably. With 3000 client locations, they are SURELY spread out over several postal areas, so that should give you a tremendous yet practical filter. You would probably only want to work on addresses for postal codes that are the same or that are geographically adjacent.

After all, (to give you a USA example), our postal code for a part of the city of Los Angeles is 90210. New Orleans is 70121. I live in Metairie, which is 70006. If we look up distances, 90210 is a couple of thousand miles from 70121 and 70006 - but 70121 and 70006 are less than 10 miles from each other. So when computing distances, you could skip looking at 90210 and 70121 - but would continue to work on 70006 vs 70121 because they are much closer. If you decide that the postal area centers are more than, say, 50 miles apart, you could probably skip the computation and not bother with storing those distances.

Does this approach make sense?
 

Blueskies

Registered User.
Local time
Today, 06:01
Joined
Oct 23, 2009
Messages
60
Hi MajP

Thanks for your post. After a compact of the back-end and increasing dbMaxLocksPerFile the code has managed 1,000,000 records in 1 hour and 34 minutes, which I think is pretty good.

Unfortunately I've got sidetracked with soemthing urgent, so can't spend much more time on this for now, but if you want to try out idea I'd be really grateful as it sounds like it's a smarter approach.

Files attached - the restaurants have some duplicates in as I needed to make the amount up to 3000, but that shouldn't make any difference. I've given you 1,000,000 addresses.

Many thanks for your kind offer to look at this.
 

Attachments

  • Restaurants and Addresses.zip
    655.1 KB · Views: 126

Users who are viewing this thread

Top Bottom