SQLite db with FDO geometries: how to assign ID of point in layer A to closest point in Layer B with SpatiaLite?

Question

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:

EDIT:

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;

Answer 1

Your database is not really a SpatiaLite database but a SQLite database which contains geometries which are encoded according to FDO specification. Something about FDO can be read from http://trac.osgeo.org/fdo/wiki/FDORfc16. Some other software, like TatukGIS, are also creating SQLite databases with FDO geometries.

Spatialite can read FDO geometries transparently through special virtual tables. They are what you see in the spatialite-gui as named to fdo_... and marked with a linked table symbol. However, despite the text in the splash screen, virtual tables do not behave totally similarly to real Spatialite tables and you have faced one such case.

I recommend to convert the FDO tables into Spatialite tables. It can be done simple with SQL

create table spl_fc1 as select * from fdo_fc1;
create table spl_fc2 as select * from fdo_fc2;

Now you can query the shortest distance for each feature in the first table as

SELECT t1.id AS id1, t2.id AS id2, 
Min(Distance(t1.GEOMETRY,t2.GEOMETRY)) AS DIST 
FROM spl_fc1 AS t1, spl_fc2 AS t2
GROUP BY t1.id
ORDER BY id1;

Result with my test data:

id1 id2 DIST
1   3   16.278821
2   2   35.114100
3   1   44.721360
4   15  65.734314
5   14  43.289722
6   1   68.029405
7   22  56.727418
8   13  48.301139
9   10  60.876925
10  4   34.205263
11  9   51.224994
12  10  54.817880
13  10  47.853944
14  9   64.124878
15  8   39.560081
16  7   59.304300
17  4   62.169124