SpatiaLite - Tipps und Tricks
Siehe auch SpatiaLite.
Queries mit Spatial Index
Um den Index von SpatiaLite zu verwenden ist eine Subquery nötig (DB Orte-Fluesse-Gemeinden):
Beispiel: Aus...
SELECT pt.ROWID, pt.PKUID, pt.NAME, pt.Geometry, po.Name FROM orte AS pt, gemeinden AS po WHERE ST_Within(pt.Geometry, po.Geometry);
wird (für <3.x)...
CREATE TABLE orte_name AS SELECT pt.PKUID, pt.NAME, pt.Geometry, po.Name AS GEMEINDE FROM orte AS pt, gemeinden AS po WHERE ST_Within(pt.Geometry, po.Geometry) AND pt.ROWID IN ( SELECT pkid FROM idx_Orte_Geometry WHERE pkid MATCH RTreeIntersects( MbrMinX(po.Geometry), MbrMinY(po.Geometry), MbrMaxX(po.Geometry), MbrMaxY(po.Geometry)) ORDER BY 1 );
bzw. (ab 3.x)...
CREATE TABLE orte_name AS SELECT pt.PKUID, pt.NAME, pt.Geometry, po.Name AS GEMEINDE FROM orte AS pt, gemeinden AS po WHERE ST_Within(pt.Geometry, po.Geometry) AND pt.ROWID IN ( SELECT ROWID FROM SpatialIndex WHERE f_table_name = 'Orte' AND search_frame = po.Geometry ORDER BY 1 );
Drei Arten von Spatial Index-Syntax
The R*Tree represents an efficient way to spatial filtering on the basis of MBR (aka BBOX) quick comparisons; for any precise spatial evaluation you are expected to use "true" spatial functions, such as ST_Contains(), ST_Within(), ST_Disjoint(), ST_Intersects(), etc..
One can access the R*Tree following *three* alternative approaches: e.g.
1) Oldest style (valid for all SpatiaLite versions)
SELECT Name FROM GeoNames WHERE ROWID IN ( SELECT pkid FROM idx_GeoNames_Geometry WHERE xmin <= 11.9 AND xmax >= 11.8 AND ymin <= 43.5 AND ymin >= 43.4 );
2) Geometry Call-Backs interface (valid vor version 2.6; deprecated in 3.x)
SELECT Name FROM GeoNames WHERE ROWID IN ( SELECT pkid FROM idx_GeoNames_Geometry WHERE pkid MATCH RTreeIntersects(11.8, 43.4, 11.9, 43.5) );
3) VirtualSpatialIndex interface (valid in 3.0.0 ?)
SELECT Name FROM GeoNames WHERE ROWID IN ( SELECT ROWID FROM SpatialIndex WHERE f_table_name = 'GeoNames' AND search_frame = BuildMbr(11.8, 43.4, 11.9, 43.5) );
There is no real difference between all them. It's mainly syntactic sugar; performances is the same, because the underlaying R*Tree implementation always is one and the same. The ugly details: What's really needed is the RTreeIntersects() callback function. But we already had MbrsWithin() and MbrsContains(): so applying the same approach for geometry callbacks functions accessing the R*Tree looked apparently good. Unfortunately this is not at all applicable to R*Tree callbacks: the internal logic implemented by SQLite is strictly bounded to "intersects". Any attempt to implement "within" or "contains" logic simply causes the whole callbacks chain to fail. So, *never* use RTreeContains and/or RTreeWithin; always use RTreeIntersects instead. Definitive solution: SpatiaLite v.3.0.0 "stable" will *DEPRECATE* both RTreeWithin and RTreeContains. They'll simply become alias-names for RTreeIntersects.