On Sun, Feb 13, 2011 at 04:18:25PM -0600, Puneet Kishor scratched on the wall: > On Sunday, February 13, 2011 at 4:15 PM, Jay A. Kreibich wrote:
> > > CREATE VIRTUAL TABLE points_rtree USING rtree ( > > > id, min_lon, min_lat, max_lon, max_lat > > > ); > > > > Almost. You need to match min/max pairs, so the column order would > > need to be: > > > > ( id, min_lon, max_lon, min_lat, max_lat ) > Fascinating. You mean, the order of the columns in the table definition > is important? I did not realize that would be important. Yes, it is very important. The power of R-Trees comes from their ability to work on ranges of data. They index objects that occupy a range (e.g. they have a start/stop or min/max value) within a dimension. Although the most common use is to index 2D or 3D space, you might also use a 1D R-Tree to index events (with a start time and stop time) within a timeline. It might not seem like much, but this is something that is extremely difficult to do efficiently with standard database indexes. SQLite R-Trees support one to five dimensions. If the table has an ID column, in addition to a min/max value for each dimension, that means tables must have an odd number of columns from 3 and 11. The module assumes the first column is the ID column, and each pair of additional columns is a min/max value pair in a specific dimension. The min/max values must be given as pairs, and the min value must come first, although it is permissible to set both the min and max column to the same value. This can be used to encode points. -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 [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
