SpatiaLite - Tipps und Tricks: Unterschied zwischen den Versionen
Stefan (Diskussion | Beiträge) K (→Queries mit Spatial Index) |
Stefan (Diskussion | Beiträge) K (→Queries mit Spatial Index) |
||
(3 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 | + | 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. | ||
Zeile 37: | Zeile 48: | ||
); | ); | ||
− | 2) Geometry Call-Backs interface (valid vor | + | 2) Geometry Call-Backs interface (valid vor version 2.6; deprecated in 3.x) |
SELECT Name FROM GeoNames | SELECT Name FROM GeoNames | ||
WHERE ROWID IN ( | WHERE ROWID IN ( | ||
Zeile 52: | Zeile 63: | ||
search_frame = BuildMbr(11.8, 43.4, 11.9, 43.5) | 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. | 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. | ||
[[Kategorie:SQLite]] | [[Kategorie:SQLite]] |
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.