Query to list near neighbours

[maxmarengo]

I am trying to write a query in Access (or SQL) that works on a table
like this:

Location Gridreference
Ben Nevis NQ1234512345
Ben Doon NQ1230012300
and so on for several thousand records.

I have a function that calculates the distance between two points.
What I want to do is write a query that will list for each point in
the database, which other points are within 500 metres.

I've managed to get the number of points within 500 metres and I can
list out the first point in the database within 500 metres, but I have
not managed a full list.

Any ideas?

Max

[maxmarengo]

Anne,

Thanks for the advice - and the clear explanation! I will try it out
this weekend - hope the size of the intermediate table doesn't scare
my old PC.

Thanks again,

Max

"Anne Nolan" <an***************@AOL.COM> wrote in message news:<2i************@uni-berlin.de>...

One approach:

Create a query (call it qryAllPairs, e.g.) that lists all pairs of points,
and the distance between them. This query will have your Locations table in
the query twice, with NO join between the two (this is called a "Cartesian
Product"):

SELECT Locations.Location AS FirstPoint, Locations_1.Location AS
SecondPoint, DistanceCalc(Locations.GridReference,
Locations_1.GridReference) AS Distance
FROM Locations, Locations AS Locations_1;

Note that each pair of points will appear twice, and each point will also be
paired with itself once. I've assumed a table name of "Locations" and a
function name of DistanceCalc.. change to whatever you've named yours.

SELECT FirstPoint, SecondPoint, Distance
FROM qryAllPairs
WHERE (FirstPoint < SecondPoint AND Distance < 500);

Then you can use this query as the basis for a 2nd query, which just gets
the point pairs where the Distance is less than 500.

The duplicate pairings are elimnated by including "WHERE FirstPoint <
SecondPoint ". This will also eliminate the records that pair a location
with itself.

If your distance calculation takes a long time, you may want to eliminate
the duplicate point pairs in the first query, so that you cut the number of
calls to the function in half.

Hope this helps,

Anne Nolan

"maxmarengo" <ma********@yahoo.co.uk> wrote in message
news:e5**************************@posting.google.c om...

I am trying to write a query in Access (or SQL) that works on a table
like this:

Location Gridreference
Ben Nevis NQ1234512345
Ben Doon NQ1230012300
and so on for several thousand records.

I have a function that calculates the distance between two points.
What I want to do is write a query that will list for each point in
the database, which other points are within 500 metres.

I've managed to get the number of points within 500 metres and I can
list out the first point in the database within 500 metres, but I have
not managed a full list.

Any ideas?

Max