find the closest point

[asc4john]

I have a table of points with xy co-ordinates. I have tried using
SELECT BETWEEN, but what I actually need is the closest point to my
test point. BETWEEN seems to need limits, like plus or minus. This is
kind of like basic collision detection. The table has an X field and a
Y field for each piece of equipment and a field for the equipment ID.
What I need is to return the EquipmentID closest to my test point.

 

[OfficeDev18 via AccessMonster.com]

I wouldn't do it in a query, but in VBA code that would evaluate each record
in turn, and as it found another record closer to your goal, the VBA would
overwrite the local x and y variables (NOT the table variables) with these
new values. After the code finishes all the records, you might then run a
query to SELECT * records where the x and y co-ordinates equal the local x
and y variables, in case there are duplicate pieces of equipment that have
similar x and y values.

Hope this helps,

Sam

 

[David F Cox]

A SELECT TOP 1 on

distancesq: (x-testx)*(x-testx) + (y-testy)*(y-testy)

 

[asc4john]

The date is stored as a double to the millimeter actually acurate to
the centimeter if I am within 100 mm (4") thats close enought.
Currently there are about 500 records there will be up to 10,000 when
al the points are in, include there Z co-ordinate. Thats why I am
looking into solutions now not in three months. I can currently do it
with VBA based on Pythgoras Theorem. It works well with 500 records
but at 10,000 it might get a little slow.

 

[David F Cox]

are you making the mistake of taking the square root?
Integer multiply and add are very fast.

The date is stored as a double to the millimeter actually acurate to
the centimeter if I am within 100 mm (4") thats close enought.
Currently there are about 500 records there will be up to 10,000 when
al the points are in, include there Z co-ordinate. Thats why I am
looking into solutions now not in three months. I can currently do it
with VBA based on Pythgoras Theorem. It works well with 500 records
but at 10,000 it might get a little slow.

 

[asc4john]

Hey Right I don't need the square root and quite a bit can be done
with integers. Near the point I take the square root to get a
torlerance number. works Thanks all

are you making the mistake of taking the square root?
Integer multiply and add are very fast.