View Ticket
Not logged in
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 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-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 WHERE statement will run after the join has already done its work:

SELECT DISTINCT a.gml_id AS gml_id, b.gml_id AS id
FROM
    ax_gebaeude a
    LEFT JOIN ax_flurstueck b ON
        ST_Intersects(a.geometry, b.geometry)
WHERE b.ROWID IN (SELECT ROWID FROM SpatialIndex WHERE f_table_name='ax_flurstueck' AND search_frame=a.geometry)

This means that ST_Intersects() function runs on all possible row combinations from ax_gebaeude and ax_flurstueck even if some of those combinations involve features that are far apart.

To engage the spatial index, make sure it is called during the same step as the ST_Intersects() function. The following query replicates the same result set on my test database and improves the performance from 1 minute down to 3 seconds:

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)
WHERE b.ROWID IN (SELECT ROWID FROM SpatialIndex WHERE f_table_name='ax_flurstueck' AND search_frame=a.geometry)

PS I'm not even sure whether you really intended to have the results filtered after the join, but I kept the second WHERE to replicate the result set of your original query.


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