| Ticket Hash: | 503b25be31c2bf6f9ed0a9f482969331ba7674da | |||
| Title: | spatialindex this has no effect in spatialite 5 | |||
| Status: | Closed | Type: | Code_Defect | |
| Severity: | Important | Priority: | Immediate | |
| Subsystem: | Resolution: | Not_A_Bug | ||
| Last Modified: | 2021-02-04 18:16:51 | |||
| Version Found In: | 5.0 | |||
| User Comments: | ||||
|
anonymous added on 2021-01-26 10:41:50:
Hello, if I use a spatialindex this has no effect in spatialite 5. (Ubuntu 20.04 with ubuntugis) The select ist not faster as without where b.ROWID IN select distinct
a.gml_id as gml_id,
b.gml_id as id anonymous added on 2021-01-26 10:43:46: Hello, if I use a spatialindex this has no effect in spatialite 5. (Ubuntu 20.04 with ubuntugis) The select ist not faster as without where b.ROWID IN (SELECT ROWID FROM SpatialIndex WHERE f_table_name='ax_flurstueck' AND search_frame=a.geometry) select distinct a.gml_id as gml_id, b.gml_id as id from ax_gebaeude as a left join ax_flurstueck as b on intersects(a.geometry,b.geometry) where b.ROWID IN (SELECT ROWID FROM SpatialIndex WHERE f_table_name='ax_flurstueck' AND search_frame=a.geometry) limit 100 ; anonymous added on 2021-01-30 15:51:22: Reformatting the query with indentation to make it clear that the
This means that To engage the spatial index, make sure it is called during the same step as the
PS
I'm not even sure whether you really intended to have the results filtered after the join, but I kept the second sandro added on 2021-02-04 18:16:51: there are no changes affecting the Spatial Index logic since many years, so it seems very improbable that it wouldn't correctly work in 5.0 note #1: the actual efficiency of the Spatial Index has nothing to do with SpatiaLite itself, it simply depends on the data access strategy that SQLite will adopt when executing your query. note #2: SQLite has an internal component, the Query Planner aka Query Optimizer, that REWRITES your query so to possibly adopt the best strategy. this one is a largely euristhic process, and can easily fail in many cases. note #3: subsequents versions of SQLite usually introduce enhanced versions of the Query Optimizer. in some unlucky cases you can possibly experience some performace regressions. conclusion: when a Spatial Query runs slowly after upgradind the libraries it's almost surely due to some change in the SQLite's Optimizer. SELECT DISTINCT a.gml_id AS gml_id, b.gml_id AS id FROM ax_gebaeude a LEFT JOIN ax_flurstueck b ON b.ROWID IN (SELECT ROWID FROM SpatialIndex WHERE f_table_name='ax_flurstueck' AND search_frame=a.geometry) AND ST_Intersects(a.geometry, b.geometry); as per my personal direct experience the above one seems to be the most efficient way for getting a reasonably fast execution; just insert the Spatial Index sub-query directly into the appropriate JOIN ... ON () clause, possibly avoiding at all to declare any WHERE clause. bye Sandro | ||||