How can I assign ID of point in layer A to closest point in Layer B with SpatiaLite (find nearest neighbors for each point in A)?
For each point stored in table fdo_fc1 I would like to select one, nearest point from table fdo_fc2.
Table fdo_fc1 has field id1 and contains 17 records. Table fdo_fc2 has field id2 and contains 33 record. (test data; real data has about 100,000 records)
I work with a sqlite database created with AutoCAD Map 3D. To query my database I use spatialite_gui.exe
My result table should look like this:
I have tried a lot but I can not find a solution.
I simplified code I found (improve performance using a maximum distance is not interesting for me now).
E.g. the accepted answer to How to find nearest neighbors between two tables with point locations in SpatiaLite? do not work for me:
Example 1: I tried:
SELECT t1.id1 As id1, t2.id2 As id2, min(Distance(t1.GEOMETRY,t2.GEOMETRY)) AS DIST FROM fdo_fc1 As t1, fdo_fc2 As t2
But result only contains one record form table fdo_fc1:
Example 2: I tried:
SELECT t1.id1 As id1, t2.id2 As id2, Distance(t1.GEOMETRY,t2.GEOMETRY) AS DIST FROM fdo_fc1 As t1, fdo_fc2 As t2
The result contains the distance only for first record of fdo_fc1:
Here is map of my test data:
I tried the solution of @user30184. I have noticed the following:
I created the sqlite database with AutoCAD Map 3D. The names of the layers/tables are fc1 and fc2. When I open the database with spatialite_gui.exe this message opens:
So now I have the new tables fdo_fc1 and fdo_fc2:
If I use fdo_fc1 and fdo_fc2 I only get one record in result. If I use fc1 und fc2 I get the correct number of records in result but the distance is not calculated:
This query returns still one record:
SELECT t1.id1 AS id1, t2.id2 AS id2, Min(Distance(t1.GEOMETRY,t2.GEOMETRY)) AS DIST FROM fdo_fc1 AS t1, fdo_fc2 AS t2 GROUP BY t1.id1 ORDER BY id1;
This query returns no distances:
SELECT t1.id1 AS id1, t2.id2 AS id2, Min(Distance(t1.GEOMETRY,t2.GEOMETRY)) AS DIST FROM fc1 AS t1, fc2 AS t2 GROUP BY t1.id1 ORDER BY id1;