I have started playing with SpatiaLite today and already stumbled upon a problem.
For each point location stored in tableOne I would like to select one, nearest (linear distance) point from tableTwo.
So far I came up with a clumsy solution that utilizes 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;
And then:
SELECT * FROM testview
WHERE distance = (SELECT MIN(distance) FROM testview AS t WHERE t.id = testview.id)
seems to do the job.
Two questions:
Is there a way to perform such query without creating a VIEW?
Is there any other way to optimize this query for better performance? In a real world scenario tableOne will have hundreds-couple thousands records, and tableTwo - 1.3 million.