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

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:52
Joined
May 21, 2018
Messages
6,450
As I said the VBA trig functions work in Radians not degrees. You must convert. Pass in degrees then convert inside your distance functions. Use the converted values in your function
example
Code:
   Const decToRad = 3.14159265358979 / 180
   Const radiusOfEarth = 3963.1
   'radiusOfEarth =3963.1 statute miles, 3443.9 nautical miles, or 6378 km
   Dim radLatStart As Double
   Dim radLongStart As Double
   Dim radLatEnd As Double
   Dim radLongEnd As Double
   
   radLatStart = decLatStart * decToRad
   radLongStart = decLongStart * decToRad
   radLatEnd = decLatEnd * decToRad
   radLongEnd = decLongEnd * decToRad

The duplicates are not a big deal, until I figure out what is dragging down my implication. If I can get my array method to run as fast as your array method, then I should be able to make the KDTree much faster. In theory. As I said, I do the same method and algorithm as you show. The big difference is you use a 3 dimensions array to save ID, lat, long. I build an object called a LocationPoint. It has three properties ID, Lat, Long. My array is a one dimensioned array filled with LocationPoints. So you read the three dimensions as you loop, I read the properties inside the location point.
I believe in theory your implementation should be faster based on efficiency of memory location. I would expect that to be slightly faster not orders of magnitude faster. If that is truly the case then I do not think in VBA I can code the KDTree with any improvement since the algorithm may be faster, but the implementation using objects is not faster than an multidimensional array with a less efficient algorithm. You may be as good as you are going to get. but the implementation of the objects may not. This issue may be a shortcoming of VBA and not seen in other languages.
 

Blueskies

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

Sorry, I haven't replied for a few days. I'll try your code ASAP.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:52
Joined
May 21, 2018
Messages
6,450
I have not had chance to relook at this either to see what is causing the poor performance. Not sure if I can do better than what you are currently seeing performance wise. It may be worth playing with it on your computer though.
 

Users who are viewing this thread

Top Bottom