Find Data that is closest to a value

  • Thread starter Thread starter kf2003
  • Start date Start date
K

kf2003

I need to be able to enter 2 values and search a database
table for the closest entry to the 2 vaules (separate
fields). The values in the databse are to 10 decimal
places. There are one million entries to search through -
but these can be grouped by another field that can be used
for a searchthat splits it into 46 groups.

All and any help appreciated
 
An example of defining what is the closest?

Given
x-array 1,20,30
Y-array 5,6,40

What is the closest to x = 7 and y = 34?
Do you calculate that as the sum of the absolute difference between the two sets
of numbers?
1-7 >> 6, 5-34 >> 29 --->> 35
20-7 >> 13, 6-34 >> 28 --->> 41
30-7>> 23, 40-34 >>6 --->> 29 (this 30,40 pair is the "closest" to 7,34)
 
I don't have a solution. I don't even know what method KF2003 wants to use. I
was trying to point him to a method of telling us what he wants.
 
Thanks for all responses,

To explain a bit further....

I have a database with GPS long and lat co-ordinates for 1
million points aong a railway line. Long and Lat are to
10 decimal palces. If I bring in from a handheld GPS unit
a Long and Lat GPS reading (that only have 8 decimal
places) how do I match it to the record in the database
that has the closest value for both Long and Lat.

I hope that helps. I was trying to use "between" and
adding a tolerance to the vaules I have from the GPS unit.

Sorry I'm taking a while to respons each time - but I'm
very busy.

Thanks

Kate
 
Because I have 10 years engineering and land surveying experience,
along with some Access experience, I thought I'd jump in here.

By "nearest" then, I assume you might just use the square root of the
sum of the squares, or you could use a more complex algorighm that
takes into account the curvature of the earth. So, for each new
point, you must calculate the distance to every one of the 1 million
other points, then find the minimum of this value. That's going to
take a little while. We'll cover the performance aspects in just a
bit.

So, you would use a filter (a criterion) in which you limit the
results to those points that are exactly at the minimum calculated
distance. This minimum calculated distance is done in a subquery that
performs a simple aggregation.

As to performance, lets reduce the number of calculations from 1
million to a much more reasonable number. That could be done by first
filtering the 1 million points down to a smaller set. Perhaps you
could start out by considering only those points that are within .01
degrees of the sample point in both lat and long. If none are found,
try again with .1 degrees, and increase this range till at least one
point is found in the set of 1 million. This has the advantage of
being able to very quickly limit the number of points being
considered.

However, it is just possible that the point "nearest" using this
subset will not be the closest point. If you limit the points
considered to .01 degrees, and the nearest point found is actually
..014 degrees away (being .01 in lat and .01 in long), then there may
be a point that was not considered which is closer. You should then
repeat the search using all points within .015 of the sample point.

Hope this was clear. It's not going to be extremely easy, but a
methodology such as this can be devised that will produce results that
are guaranteed correct with reasonable performance.

Please let me know if this helped, and if I can be of any further
assistance.

Thanks for all responses,

To explain a bit further....

I have a database with GPS long and lat co-ordinates for 1
million points aong a railway line. Long and Lat are to
10 decimal palces. If I bring in from a handheld GPS unit
a Long and Lat GPS reading (that only have 8 decimal
places) how do I match it to the record in the database
that has the closest value for both Long and Lat.

I hope that helps. I was trying to use "between" and
adding a tolerance to the vaules I have from the GPS unit.

Sorry I'm taking a while to respons each time - but I'm
very busy.

Thanks

Kate
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top