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

added 663 characters in body

Source Link

edited Apr 20, 2015 at 8:49

69k
4
71
125

ItYour database is not quite rightreally a SpatiaLite database but a SQLite database which contains geometries which are encoded according to say thatFDO 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 first query does not workspatialite-gui as named to fdo_. It works perfectly.. and returnsmarked with a linked table symbol. However, despite the closest distance that is found between alltext in the features onsplash screen, virtual tables fdo_fc1do not behave totally similarly to real Spatialite tables and fdo_fc2you have faced one such case. You must use GROUP BY for getting a separate result

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 fdo_fc1spl_fc1 AS t1, fdo_fc2spl_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

I don't understand why your latter query gives only 1x32 results (you have 32 records in the second test table instead of 23, haven't you?). For me it returns the full distance matrix 17x23=391 rows. I made my test tables as you describes with 17 and 23 rows.

It is not quite right to say that the first query does not work. It works perfectly and returns the closest distance that is found between all the features on tables fdo_fc1 and fdo_fc2. You must use GROUP BY for getting a separate result for each feature in the first table.

SELECT t1.id AS id1, t2.id AS id2, 
Min(Distance(t1.GEOMETRY,t2.GEOMETRY)) AS DIST 
FROM fdo_fc1 AS t1, fdo_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

I don't understand why your latter query gives only 1x32 results (you have 32 records in the second test table instead of 23, haven't you?). For me it returns the full distance matrix 17x23=391 rows. I made my test tables as you describes with 17 and 23 rows.

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

Clarify the usage of group by

Source Link

edited Apr 20, 2015 at 7:33

user30184

69k
4
71
125

UseIt is not quite right to say that the first query does not work. It works perfectly and returns the closest distance that is found between all the features on tables fdo_fc1 and fdo_fc2. You must use GROUP BY for getting a separate result for each feature in the first table.

SELECT t1.id AS id1, t2.id AS id2, 
Min(Distance(t1.GEOMETRY,t2.GEOMETRY)) AS DIST 
FROM fdo_fc1 AS t1, fdo_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

I don't understand why your latter query gives only 1x32 results (you have 32 records in the second test table instead of 23, haven't you?). For me it returns the full distance matrix 17x23=391 rows. I made my test tables as you describes with 17 and 23 rows.

Use GROUP BY

SELECT t1.id AS id1, t2.id AS id2, 
Min(Distance(t1.GEOMETRY,t2.GEOMETRY)) AS DIST 
FROM fdo_fc1 AS t1, fdo_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

I don't understand why your latter query gives only 1x32 results (you have 32 records in the second test table instead of 23, haven't you?). For me it returns the full distance matrix 17x23=391 rows. I made my test tables as you describes with 17 and 23 rows.

It is not quite right to say that the first query does not work. It works perfectly and returns the closest distance that is found between all the features on tables fdo_fc1 and fdo_fc2. You must use GROUP BY for getting a separate result for each feature in the first table.

SELECT t1.id AS id1, t2.id AS id2, 
Min(Distance(t1.GEOMETRY,t2.GEOMETRY)) AS DIST 
FROM fdo_fc1 AS t1, fdo_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

I don't understand why your latter query gives only 1x32 results (you have 32 records in the second test table instead of 23, haven't you?). For me it returns the full distance matrix 17x23=391 rows. I made my test tables as you describes with 17 and 23 rows.

Source Link

created Apr 20, 2015 at 7:25

user30184

69k
4
71
125

Use GROUP BY

SELECT t1.id AS id1, t2.id AS id2, 
Min(Distance(t1.GEOMETRY,t2.GEOMETRY)) AS DIST 
FROM fdo_fc1 AS t1, fdo_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

I don't understand why your latter query gives only 1x32 results (you have 32 records in the second test table instead of 23, haven't you?). For me it returns the full distance matrix 17x23=391 rows. I made my test tables as you describes with 17 and 23 rows.

Stack Exchange Network

Return to Answer