Spatial index not taken into consideration

[Fabian]

Hi,

I have around ~4500 geometries where I create an Envelope of to do fast bbox lookups for points. However, one query takes almost 500ms with is already incredibly high, even without index, since it's just boundings.

Here's what I'm doing:

# SPATIALITE_SECURITY=relaxed sqlite3 iso3166-2.db
SQLite version 3.40.1 2022-12-28 14:03:47
Enter ".help" for usage hints.
sqlite> .load mod_spatialite 

sqlite> select sqlite_version(), spatialite_version(), geos_version();  
3.40.1|5.0.1|3.11.1-CAPI-1.17.1

sqlite> SELECT InitSpatialMetaDataFull(1);
1
sqlite> SELECT ImportSHP('iso3166-2-boundaries', 'boundaries', 'UTF-8', 4326);
========
Loading shapefile at 'iso3166-2-boundaries' into SQLite table 'boundaries'

BEGIN;
CREATE TABLE "boundaries" (
"pk_uid" INTEGER PRIMARY KEY AUTOINCREMENT,
"osm_id" TEXT,
"osm_way_id" TEXT,
"iso3166-2" TEXT,
"admin_leve" TEXT);
SELECT AddGeometryColumn('boundaries', 'geometry', 4326, 'MULTIPOLYGON', 'XY');
COMMIT;

Inserted 4741 rows into 'boundaries' from SHAPEFILE
========
4741
sqlite> SELECT AddGeometryColumn('boundaries', 'bbox', 4326, 'POLYGON', 'XY');
1
sqlite> UPDATE boundaries SET bbox = Envelope(geometry);
sqlite> SELECT CreateSpatialIndex('boundaries', 'bbox');
1
sqlite> EXPLAIN QUERY PLAN  
WITH position AS MATERIALIZED (
       SELECT MakePoint(9.5810496, 48.9808291, 4326) AS point
)
SELECT * FROM boundaries b, position p WHERE Contains(b.bbox, p.point);
QUERY PLAN
|--MATERIALIZE position
|  `--SCAN CONSTANT ROW
|--SCAN p
`--SCAN b
sqlite> select * from spatialindex;
sqlite> SELECT CheckSpatialIndex('boundaries', 'bbox');
1
sqlite> .timer on
sqlite> WITH position AS MATERIALIZED (
       SELECT MakePoint(9.5, 48.9, 4326) AS point
)
SELECT * FROM boundaries b, position p WHERE Contains(b.geometry, p.point);
356|62611||DE-BW|4|||
Run Time: real 0.424 user 0.228159 sys 0.196259
sqlite>

Everything was tested in a clean docker container with spatialite & sqlite from the official repo.

I'm a bit clueless at the moment.

Best regards Fabian

[a.fu...@lqt.it]

Hi Fabian,

SpatiaLite is not PostGIS.
Spatialite too supports the SpatialIndex, but it works completely
differently.

it is not enough to simply create a SpatialIndex for it to be
actually used, an explicit subquery must be carried out in the
SQL statement.

please, read the documentation:

https://www.gaia-gis.it/fossil/libspatialite/wiki?name=SpatialIndex

bye Sandro