Znaleźć najbliższych sąsiadów między dwiema tabelami z lokalizacjami punktów w SpatiaLite?

Dzisiaj zacząłem grać ze SpatiaLite i już natknąłem się na problem.

Dla każdego położenia punktu zapisanego w tableOne chciałbym wybrać jeden, najbliższy (odległość liniowa) punkt z tableTwo.

Do tej pory wymyśliłem niezdarne rozwiązanie, które wykorzystuje VIEW:

CREATE VIEW testview AS 
SELECT 
A.id , 
B.myValue, 
Distance(A.Geometry, B.Geometry) AS distance
FROM tableOne AS A, tableTwo AS B
WHERE distance < 10000
ORDER BY A.Id, distance;

I wtedy:

SELECT * FROM testview
WHERE distance = (SELECT MIN(distance) FROM testview AS t WHERE t.id = testview.id)

zdaje się wykonywać pracę.

Dwa pytania:

Czy istnieje sposób na wykonanie takiego zapytania bez tworzenia WIDOKU?

Czy istnieje inny sposób optymalizacji tego zapytania w celu uzyskania lepszej wydajności? W rzeczywistym scenariuszu tableOne będzie miało setki par tysięcy rekordów, a tableTwo - 1,3 miliona.

Właśnie przetestowałem ten SQL i działa:

SELECT g1.OGC_FID As id1, g2.OGC_FID As id2, MIN(ST_Distance(g1.GEOMETRY,g2.GEOMETRY)) AS DIST
FROM table_01 As g1, table_02 As g2   
WHERE g1.OGC_FID <> g2.OGC_FID
AND ST_Contains(ST_Expand(g1.geometry,50),g2.geometry)
GROUP BY id1
ORDER BY id1

Jak można przeczytać tutaj: „Naiwnym sposobem wykonania zapytania najbliższego sąsiada jest uporządkowanie tabeli kandydatów na odległość od geometrii zapytania, a następnie zapisanie rekordu z najmniejszą odległością”.

Z poważaniem,

Andrea

Jeśli nie chcesz obliczać odległości między wszystkimi kombinacjami punktów, możesz użyć indeksu przestrzennego na jednej z tabel:

SELECT 
  A.id , 
  B.myValue, 
  MIN(Distance(A.Geometry, B.Geometry)) AS distance
FROM tableOne AS A, tableTwo AS B
WHERE A.ROWID IN (
  SELECT ROWID
  FROM SpatialIndex WHERE
    f_table_name = 'A' 
    AND search_frame = BuildCircleMbr(ST_X(B.Geometry), ST_Y(B.Geometry), 10000))
GROUP BY A.id, B.myValue

Od wersji 4.4.0 SpatiaLite obsługuje indeks tabeli wirtualnej KNN dla problemów z najbliższym sąsiadem. Oto zapytanie, które znajduje najbliższą linię w tabeli linii do każdego punktu w tabeli punktów.

SELECT k.* FROM knn k, points p
WHERE f_table_name = 'linestrings' 
AND ref_geometry = p.geometry
AND max_items = 1;

Możesz uprościć swoje zapytanie w ten sposób.

SELECT 
   A.id , 
   B.myValue, 
   MIN(Distance(A.Geometry, B.Geometry)) AS distance
FROM tableOne AS A, tableTwo AS B
GROUP BY A.id, B.myValue

Aby uzyskać bardziej ogólne rozwiązanie, warto spróbować przekonwertować tę funkcję PostGIS Nearest Neighbor: http://blog.mackerron.com/2011/03/postgis-nearest-neighbour/