recipe #15 

2011 January 28 
Previous Slide  Table of Contents  Next Slide 
The problem Yet another problem based on the populated_places dataset. This time the question is:

PopulatedPlace #1  Distance (meters)  PopulatedPlace #2 
Vallarsa  49.444299  Raossi 
Raossi  49.444299  Vallarsa 
Seveso  220.780551  Meda 
Meda  220.780551  Seveso 
...  ...  ... 
SELECT pp1.name AS "PopulatedPlace #1", GeodesicLength( MakeLine(pp1.geometry, pp2.geometry)) AS "Distance (meters)", pp2.name AS "PopulatedPlace #2" FROM populated_places AS pp1, populated_places AS pp2 WHERE GeodesicLength( MakeLine(pp1.geometry, pp2.geometry)) < 1000.0 AND pp1.id <> pp2.id AND pp2.ROWID IN ( SELECT pkid FROM idx_populated_places_geometry WHERE pkid MATCH RTreeDistWithin( ST_X(pp1.geometry), ST_Y(pp1.geometry), 0.02)) ORDER BY 2; 
Performing a Spatial query like this one in the most naive way requires an
extremely long time, even if you'll use the most recent and powerful CPU.
But carefully applying a little bit of optimization is not too much difficult. And a properly defined an well optimized SQL query surely runs in the smoothest and fastest way. 
Previous Slide  Table of Contents  Next Slide 
Author: Alessandro Furieri a.furieri@lqt.it  
This work is licensed under the AttributionShareAlike 3.0 Unported (CC BYSA 3.0) license.  
Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.3 or any later version published by the Free Software Foundation; with no Invariant Sections, no FrontCover Texts, and no BackCover Texts. 