I have 2 separate point layers and want to know the points from each layer that occur within 10m of each other. Can someone help me with the SQL syntax for that? Thanks in advance.
Select table_a.id, table_b.id
from table_a, table_b
where CartesianObjectDistance(table_a.obj, table_b.obj, "m") <10 and table_a.crossjoin_id = table_b.crossjoin_id into joined_points_10m?
crossjoin_id is an integer column containing all the same id (eg all zeros). You need to perform a cross join - where every point in table a is linked to every point in b before you can compare the distances.
You can use ObjectDistance for non-projected data.
Hi @Tom Parker?
I really like your workaround for doing a cross join. I had similar thought but never actually tried it out yet. 👌
However, I'd think you need to change the order of the conditions so that the actual join is the first condition:
where table_a.crossjoin_id = table_b.crossjoin_id
And CartesianObjectDistance(table_a.obj, table_b.obj, "m") <10
Thanks Tom and Peter, I appreciate your help.