SpatiaLite - Tipps und Tricks: Unterschied zwischen den Versionen

Version vom 23. April 2012, 01:06 Uhr

Siehe auch SpatiaLite.

Queries mit Spatial Index

Um den Index von SpatiaLite zu verwenden ist eine Subquery nötig:

Beispiel: Aus...

 SELECT pt.ROWID, pt.PKUID, pt.NAME, pt.Geometry, po.Name
 FROM orte AS pt, gemeinden AS po
 WHERE ST_Contains(pt.way, po.way)
 LIMIT 10;

wird...

 SELECT pt.ROWID, pt.PKUID, pt.NAME, pt.Geometry, po.Name
 FROM orte AS pt, gemeinden AS po
 WHERE ST_Contains(po.Geometry, pt.Geometry) 
 AND pt.ROWID IN (
   SELECT ROWID FROM idx_Orte_Geometry 
   WHERE RTreeContains(po.Geometry, pt.Geometry) = 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 vesion 2.6? )

 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.