By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,485 Members | 1,804 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,485 IT Pros & Developers. It's quick & easy.

Query to list near neighbours

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.