Re: [sqlite] Knowing when not to use a R-Tree index, and clustering tables along spatial indexes

Am Mon, 1 Jan 2018 20:30:10 +0100 schrieb Clemens Ladisch: > Wolfgang Enzinger wrote: >> Am Mon, 1 Jan 2018 16:20:21 +0100 schrieb Clemens Ladisch: >>> It is indeed possible to change the query so that SQLite uses rowid >>> lookups for the R-tree filter (INDEX 1). However, any likelihood on the

Re: [sqlite] Knowing when not to use a R-Tree index, and clustering tables along spatial indexes

Wolfgang Enzinger wrote: > Am Mon, 1 Jan 2018 16:20:21 +0100 schrieb Clemens Ladisch: >> It is indeed possible to change the query so that SQLite uses rowid >> lookups for the R-tree filter (INDEX 1). However, any likelihood on the >> R-tree search expression still did not make any difference.

Re: [sqlite] Knowing when not to use a R-Tree index, and clustering tables along spatial indexes

On Mon, Jan 1, 2018 at 10:45 AM, Clemens Ladisch wrote: > Wolfgang Enzinger wrote: > > First, query the overall extent of your data, like this: > > SELECT MIN(mingx),MAX(maxgx),MIN(mingy),MAX(maxgy) FROM > flst_shape_index; > > This results in a full table scan. Instead of

Re: [sqlite] Knowing when not to use a R-Tree index, and clustering tables along spatial indexes

Am Mon, 1 Jan 2018 16:20:21 +0100 schrieb Clemens Ladisch: > It is indeed possible to change the query so that SQLite uses rowid > lookups for the R-tree filter (INDEX 1). However, any likelihood on the > R-tree search expression still did not make any difference. Do you have > an example?

Re: [sqlite] Knowing when not to use a R-Tree index, and clustering tables along spatial indexes

Wolfgang Enzinger wrote: > Am Mon, 1 Jan 2018 10:45:50 +0100 schrieb Clemens Ladisch: >> Wolfgang Enzinger wrote: >>> Let SQLite know about that likelihood in a JOIN query >> >> This does not appear to change anything with a virtual table: >> >> SELECT t.* FROM t JOIN i USING (id) WHERE

Re: [sqlite] Knowing when not to use a R-Tree index, and clustering tables along spatial indexes

Am Mon, 1 Jan 2018 10:45:50 +0100 schrieb Clemens Ladisch: > Wolfgang Enzinger wrote: >> First, query the overall extent of your data, like this: >> SELECT MIN(mingx),MAX(maxgx),MIN(mingy),MAX(maxgy) FROM flst_shape_index; > > This results in a full table scan. Instead of caching these values

Re: [sqlite] Knowing when not to use a R-Tree index, and clustering tables along spatial indexes

Wolfgang Enzinger wrote: > First, query the overall extent of your data, like this: > SELECT MIN(mingx),MAX(maxgx),MIN(mingy),MAX(maxgy) FROM flst_shape_index; This results in a full table scan. Instead of caching these values manually, it would be a better idea to read them from the index:

Re: [sqlite] Knowing when not to use a R-Tree index, and clustering tables along spatial indexes

Am Fri, 29 Dec 2017 19:59:12 +0100 schrieb Andrea Aime: > With SQLite R-Tree I'm using either a join with the index virtual table, or > a subquery > retrieving the ids from the rtree. Regardless, the query is basically > ordering SQLite > to use the index. > So I was wondering, is there any

Re: [sqlite] Knowing when not to use a R-Tree index, and clustering tables along spatial indexes

Andrea Aime wrote: > So I was wondering, is there any opportunity to run a blazing fast pre-query > against > the index that will tell me whether joining/subquerying into the rtree is > going to be a win, or not? Each node in an R-tree index stores the coordinates of the leaf objects/child

[sqlite] Knowing when not to use a R-Tree index, and clustering tables along spatial indexes

Hi, I'm writing some software that can read data off GeoPackage (SQLite + rtree + standardized set of metadata tables) as an alternative format for spatial databases, like PostgreSql with the PostGIS extension. Now, when I use PostGIS the query plan optimizer checks the bbox provided in the query