so in here: https://postgis.net/workshops/postgis-intro/knn.html it is shown how to use "cross join lateral" to execute a query that joins two tables with geometry/point column on each (with spatial index for performance).
I have two tables, table1 with points: (1 1) (2 2) and table2 with points (1.5 1.5) and (9 9).
with the cross join lateral by distance from table1 to table2 I get the following result:
(1 1) (1.5 1.5)
(2 2) (1.5 1.5)
but I am interested in "one to one" relation, meaning the same point (1.5 1.5) in table2 should not be mapped(joined) to more than one point in table1 so I expect a result like that:
(1 1) (1.5 1.5)
(2 2) (9 9)
is it possible to add such condition to the subquery ?
by the way, the above expected is only one option because switching the pairs also maps each points to its nearest without using the same point more than once.
performance is critical to me and I have spatial index on those columns on both tables.
thanks!