Query on two tables of coordinates

[Greig]

Hi all,

I am having problems joining two tables together.

The first table contains X and Y coordinate values:

X Y
525914 176619
525942 176685
525964 176697
525952 176710
525905 176738

The second table contains the same/similar X and Y values and an asset ID.

ASSET_ID XX YY
00030781 525914 176617
00031583 525942 176684
00032265 525964 176697
00032260 525952 176711
00032255 525907 176738

I would like to make the query on the X and Y fields from both tables and when they match, join them - Simple. But I would also like to add a tolerance of 2 in the query, so if the XX or YY coordinate value from the second table is within 2 of the X or Y coordinate of the first table they should also join.

For example if table 1 had X and Y values of:

525914 176619

and table 2 had XX and YY values of:

525914 176617 or
525916 176619 or
525915 176617

I would want to make the join.

So far I have only managed to join when the values match:

X Y YY XX ASSET_ID
525687 176521
525697 176447
525709 176392 176392 525709 00032873
525709 176408
525709 176535
525711 176515 176515 525711 00035221

I have tried using < and > expressions but I can’t get the SQL right. Can anyone help please? It would be much appreciated.

Many Thanks,

Greig

 

[neileg]

Getting an exact match is easy, I presume you used the two tables in a query and used an inner join on the x and y fields.

However a tollerance of 2 on each co-ordinate generates 25 possible combinations and that's not easy to deal with. I think you'd have to do this in code and step through all the records in one table to test for a near match for each record in the other. Beyond my coding abilities, I'm afraid.