Christophe Leske wrote: > > - one can somehow index the fields of an rtree table, and if so how? > - if there could be any other information in an rtree table other than > the integer ID and then the real values for the rectangles. > > The reason i am asking for this is because this yields to aways the same > setup - an rtree lookup table, which ndexes back to the real information > via its ID. >
This is precisely what an index is. An index entry stores a copy of the indexed data and the rowid of the table row that contains that data. These entries are stored in a data structure that allows fast access, a B-Tree for normal indexes, and an R-Tree for the new multidimensional indexes. These R-Tree tables are really best though of a indexes that you must manually add to your queries because SQLite won't add them automatically. > I would eventually like to ease my normal data table for the entries in > the rtree table (in my case, i?d like to purge the longitude and > latitude values that are used in the rtree table from the "normal" data > table, as they are redundant in there), yet this yields to slower queries. > That is one benefit of treating the R-Tree indexes as normal tables, you can do exactly that kind of elimination of redundant data. With normal indexes, SQLite will optimize data lookup and pull data from an index instead of the table if it can, but it must still store the duplicated data. > Usually, my rtree is used in queries like > > select * from citydatabase where id in (select id from rtree where > longitude_min>XX and longitude_max<YY and latitude_min>XY and > latitude_max<XZ) > > Effectively, these are two queries, and the second one may results in a > big subset (e.g. for big rectangle, like views from a high altitude on a > 3d globe). > Then don't do that. :-) Seriously, what do you expect when when you try to retrieve a large subset of your data? This could perhaps be mitigated by partitioning the data into several different R-Tree indexes (or tables), one for each of your city classes. If your application scans the index with the largest cities first, it may get enough cities to display relatively quickly. If it hasn't found enough cities at that zoom level, then your application would scan the next R-Tree table, with the next smaller size of cities, and also display those. A little further though along these lines leads to the idea of treating the city size as another dimension in your location data. Each city is located at some point in the latitude, longitude, and size space. If you do this you can use the R-Tree index to search for cities with the largest size, and then repeat the query with wider limits on the size dimension if you don't get enough results. By changing the limits you will completely skip all the records that were searched in the first query when doing the second. > I was also wondering if there is any way to get SQlite to cache the > results of a previous query - since many of my requests are based on a > Zoom in or zoom out in a given rectangle, it would probably be wise to > cache an initial query with its results and use this for subsequent > queries when zooming in for instance. > No, SQLite won't cache results, but it does cache the database pages scanned by a query. Your application could do this sort of caching by creating temp tables from the results of one query and then searching those tables in a subsequent zoom-in query. > Is there any function that would allow for tests like "new rect is > inside older rect" ? > No, again SQLite doesn't know anything about the rectangles your application is using to build the queries, only your application knows that. HTH Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
