![]() |
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 Attribution-ShareAlike 3.0 Unported (CC BY-SA 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 Front-Cover Texts, and no Back-Cover Texts. |