Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
    
I can give you an approach that is several orders of magnitude faster, but it would require you to use postgresql 9 knngist index instead of spatialite... –  Ragi Yaser Burhum Oct 13 '11 at 2:20
    
actually faster than GRASS, ArcGIS, QGIS, SQLServer and pretty much any other spatial db/Desktop GIS (have not tried Oracle nearest neighbour functionality though).Just let me know if it is an option. –  Ragi Yaser Burhum Oct 13 '11 at 2:23
    
@Ragi: I'm aware that PostGIS would be much more efficient way to work with such problem. However the ultimate goal of this exercise would be to make small portable app and in this case SpatiaLite is a winner. –  radek Oct 13 '11 at 10:42
    
What's your development platform for your portable app? –  Allan Adair Oct 13 '11 at 12:27
    
@Allan: Working on both: Windows Server 2008 & Ubuntu at the moment. –  radek Oct 13 '11 at 14:07
show 4 more comments

3 Answers

up vote 2 down vote accepted

I have just tested this SQL and it works:

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

As you can read here "The naive way to carry out a nearest neighbour query is to order the candidate table by distance from the query geometry, and then take the record with the smallest distance".

Best regards,

Andrea

share|improve this answer
add comment

If you don't want to compute the distances between all the point combinations, you can use a spatial index on one of the table :

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
share|improve this answer
add comment

You can simplify your query like this.

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

For a more generic solution, it might be worth trying to convert this PostGIS Nearest Neighbor function: http://blog.mackerron.com/2011/03/postgis-nearest-neighbour/

share|improve this answer
    
unfortunately the code results in: SQL error: "misuse of aggregate: MIN()" –  radek Oct 13 '11 at 10:38
    
As of PostGIS there are also some examples on BostonGIS website, but so far I wasn't successful in translating them into SpatiaLite :/ –  radek Oct 13 '11 at 10:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.