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.