SpatiaLite - Tipps und Tricks - Versionsgeschichte

Stefan: /* Queries mit Spatial Index */

2012-04-25T06:20:10Z

Queries mit Spatial Index

← Nächstältere Version		Version vom 25. April 2012, 06:20 Uhr
Zeile 28:		Zeile 28:
	WHERE ST_Within(pt.Geometry, po.Geometry)		WHERE ST_Within(pt.Geometry, po.Geometry)
	AND pt.ROWID IN (		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 ==		== Drei Arten von Spatial Index-Syntax ==

Stefan: /* Queries mit Spatial Index */

2012-04-25T06:18:58Z

Queries mit Spatial Index

← Nächstältere Version		Version vom 25. April 2012, 06:18 Uhr
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 22:		Zeile 22:
	ORDER BY 1		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 (

	== Drei Arten von Spatial Index-Syntax ==		== Drei Arten von Spatial Index-Syntax ==

Stefan: /* Drei Arten von Spatial Index-Syntax */

2012-04-25T06:17:35Z

Drei Arten von Spatial Index-Syntax

← Nächstältere Version		Version vom 25. April 2012, 06:17 Uhr
Zeile 25:		Zeile 25:
	== 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 RTree following three* alternative approaches: e.g.		One can access the RTree following three* alternative approaches: e.g.

Stefan: /* Drei Arten von Spatial Index-Syntax */

2012-04-25T06:17:07Z

Drei Arten von Spatial Index-Syntax

← Nächstältere Version		Version vom 25. April 2012, 06:17 Uhr
Zeile 37:		Zeile 37:
	);		);

	2) Geometry Call-Backs interface (valid vor ~~vesion~~ 2.6? )		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 52:
	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 RTree 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 RTree looked apparently good. Unfortunately this is not at all applicable to RTree 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 RTree 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 RTree looked apparently good. Unfortunately this is not at all applicable to RTree 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]]

Stefan: /* Queries mit Spatial Index */

2012-04-23T11:15:02Z

Queries mit Spatial Index

← Nächstältere Version		Version vom 23. April 2012, 11:15 Uhr
Zeile 8:		Zeile 8:
	SELECT pt.ROWID, pt.PKUID, pt.NAME, pt.Geometry, po.Name		SELECT pt.ROWID, pt.PKUID, pt.NAME, pt.Geometry, po.Name
	FROM orte AS pt, gemeinden AS po		FROM orte AS pt, gemeinden AS po
	WHERE ~~ST_Contains~~(pt.~~way~~, po.~~way~~);		WHERE ST_Within(pt.Geometry, po.Geometry);

	wird...		wird...

Stefan: /* Queries mit Spatial Index */

2012-04-23T11:14:42Z

Queries mit Spatial Index

← Nächstältere Version		Version vom 23. April 2012, 11:14 Uhr
Zeile 3:		Zeile 3:
	== Queries mit Spatial Index ==		== Queries mit Spatial Index ==

	Um den Index von SpatiaLite zu verwenden ist eine Subquery nötig:		Um den Index von SpatiaLite zu verwenden ist eine Subquery nötig (DB Orte-Fluesse-Gemeinden):

	Beispiel: Aus...		Beispiel: Aus...
Zeile 11:		Zeile 11:

	wird...		wird...
	SELECT pt.PKUID, pt.NAME, pt.Geometry, po.Name		CREATE TABLE orte_name AS
			SELECT pt.PKUID, pt.NAME, pt.Geometry, po.Name AS GEMEINDE
	FROM orte AS pt, gemeinden AS po		FROM orte AS pt, gemeinden AS po
	WHERE ST_Within(pt.Geometry, po.Geometry)		WHERE ST_Within(pt.Geometry, po.Geometry)
Zeile 19:		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
			);

	== Drei Arten von Spatial Index-Syntax ==		== Drei Arten von Spatial Index-Syntax ==

Stefan: /* Queries mit Spatial Index */

2012-04-23T00:39:23Z

Queries mit Spatial Index

← Nächstältere Version		Version vom 23. April 2012, 00:39 Uhr
Zeile 11:		Zeile 11:

	wird...		wird...
	SELECT ~~pt.ROWID,~~ pt.PKUID, pt.NAME, pt.Geometry, po.Name		SELECT pt.PKUID, pt.NAME, pt.Geometry, po.Name
	FROM orte AS pt, gemeinden AS po		FROM orte AS pt, gemeinden AS po
	WHERE ~~ST_Contains~~(po.Geometry, pt.Geometry)		WHERE ST_Within(pt.Geometry, po.Geometry)
	AND pt.ROWID IN (		AND pt.ROWID IN (
	SELECT ~~ROWID~~ FROM idx_Orte_Geometry		SELECT pkid FROM idx_Orte_Geometry
	WHERE ~~RTreeContains~~(po.Geometry, pt.Geometry) ~~= 1~~		WHERE pkid MATCH RTreeIntersects(
	);		MbrMinX(po.Geometry), MbrMinY(po.Geometry),
			MbrMaxX(po.Geometry), MbrMaxY(po.Geometry))
			)

	== Drei Arten von Spatial Index-Syntax ==		== Drei Arten von Spatial Index-Syntax ==

Stefan: /* Queries mit Spatial Index */

2012-04-22T23:06:45Z

Queries mit Spatial Index

← Nächstältere Version		Version vom 22. April 2012, 23:06 Uhr
Zeile 8:		Zeile 8:
	SELECT pt.ROWID, pt.PKUID, pt.NAME, pt.Geometry, po.Name		SELECT pt.ROWID, pt.PKUID, pt.NAME, pt.Geometry, po.Name
	FROM orte AS pt, gemeinden AS po		FROM orte AS pt, gemeinden AS po
	WHERE ST_Contains(pt.way, po.way)		WHERE ST_Contains(pt.way, po.way);
	~~LIMIT 10~~;

	wird...		wird...

Stefan: /* Drei Arten von Spatial Index-Syntax */

2012-04-22T23:06:27Z

Drei Arten von Spatial Index-Syntax

← Nächstältere Version		Version vom 22. April 2012, 23:06 Uhr
Zeile 52:		Zeile 52:

	There is no real difference between all them. It's mainly syntactic sugar; performances is the same, because the underlaying RTree 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 RTree looked apparently good. Unfortunately this is not at all applicable to RTree 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 RTree 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 RTree looked apparently good. Unfortunately this is not at all applicable to RTree 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]]

Stefan: /* Drei Arten von Spatial Index-Syntax */

2012-04-22T23:06:07Z

Drei Arten von Spatial Index-Syntax

← Nächstältere Version		Version vom 22. April 2012, 23:06 Uhr
Zeile 26:		Zeile 26:
	One can access the RTree following three* alternative approaches: e.g.		One can access the RTree following three* alternative approaches: e.g.

	#1~~: Oldes~~ style		1) Oldest style (valid for all SpatiaLite versions)
	SELECT Name FROM GeoNames		SELECT Name FROM GeoNames
	WHERE ROWID IN (		WHERE ROWID IN (
Zeile 34:		Zeile 34:
	);		);

	#2: Geometry Call-Backs interface		2) Geometry Call-Backs interface (valid vor vesion 2.6? )
	SELECT Name FROM GeoNames		SELECT Name FROM GeoNames
	WHERE ROWID IN (		WHERE ROWID IN (
Zeile 42:		Zeile 42:
	);		);

	#3: VirtualSpatialIndex interface		3) VirtualSpatialIndex interface (valid in 3.0.0 ?)
	SELECT Name FROM GeoNames		SELECT Name FROM GeoNames
	WHERE ROWID IN (		WHERE ROWID IN (