SpatiaLite - Tipps und Tricks: Unterschied zwischen den Versionen

Aus Geoinformation HSR
Wechseln zu: Navigation, Suche
K (Drei Arten von Spatial Index-Syntax)
K (Queries mit Spatial Index)
 
(2 dazwischenliegende Versionen desselben Benutzers werden nicht angezeigt)
Zeile 10: Zeile 10:
 
   WHERE ST_Within(pt.Geometry, po.Geometry);
 
   WHERE ST_Within(pt.Geometry, po.Geometry);
  
wird...
+
wird (für <3.x)...
 
   CREATE TABLE orte_name AS  
 
   CREATE TABLE orte_name AS  
 
   SELECT pt.PKUID, pt.NAME, pt.Geometry, po.Name AS GEMEINDE
 
   SELECT pt.PKUID, pt.NAME, pt.Geometry, po.Name AS GEMEINDE
Zeile 20: Zeile 20:
 
       MbrMinX(po.Geometry), MbrMinY(po.Geometry),
 
       MbrMinX(po.Geometry), MbrMinY(po.Geometry),
 
       MbrMaxX(po.Geometry), MbrMaxY(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
 
     ORDER BY 1
 
   );
 
   );
Zeile 25: Zeile 36:
 
== Drei Arten von Spatial Index-Syntax ==
 
== 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..
+
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.
 
One can access the R*Tree following *three* alternative approaches: e.g.

Aktuelle Version vom 25. April 2012, 07:20 Uhr

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.