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 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?
I had good results in a similar situation with this strategy: First, query the overall extent of your data, like this: SELECT MIN(mingx),MAX(maxgx),MIN(mingy),MAX(maxgy) FROM flst_shape_index; Second, for every spatial query, calculate the size of the area in question. Then, with these two rectangles, you can calculate the LIKELIHOOD that a particular record in your data is located within the requested area, i.e. meets the spatial criteria. Let SQLite know about that likelihood in a JOIN query, using the LIKELIHOOD function (http://www.sqlite.org/lang_corefunc.html#likelihood). Also, if possible, give LIKELIHOOD information to the query planner for any other criteria used. SQLite will consider them. HTH Wolfgang _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
