On Mon, Apr 23, 2012 at 06:36:35PM +0100, Shish scratched on the wall: > The rtree docs say that as well as 2D geometry, it could be used to > index ranges of time - this is what I'm doing, but I've found a > problem and workaround:
I think you're missing the idea of R-Trees. I would suggest reading all of the document <http://sqlite.org/rtree.html>, especially section 4.0: "Using R*Trees Effectively". In specific, I'd point out this note: Key Point: An R*Tree index does not normally provide the exact answer but merely reduces the set of potential answers from millions to dozens. In a typical setup you have a standard table with your actual time and event records, including the full precision timestamps. The R-Tree is setup with a foreign key back to that table. The R-Tree acts as a domain specific index, to reduce a query on the primary table to a handful of rows. You then use the specific, exact values in the primary table as an exact filter. This can all be done with a single JOIN at a set of WHERE conditions. In other words, the R-Tree doesn't need to be super specific, as the final filtering should be done on the original data in the primary table. In the case of standard tables, that can be a 64-bit FP number, which will provide the resolution you need. Just be sure you adjust your R-Tree numbers so that they become inclusive to the original values when their precision is reduced. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
