On 8/30/18, t...@qvgps.com <t...@qvgps.com> wrote: > > Structure is simple: > CREATE TABLE Lines(Id LONG PRIMARY KEY, Label VARCHAR(50), Coordinates > BLOB, Flags INT, StyleId INT); > And an rtree-index: > CREATE VIRTUAL TABLE Lines_bb USING rtree (Id LONG PRIMARY KEY, X0 > FLOAT, X1 FLOAT, Y0 FLOAT, Y1 FLOAT, Z0 FLOAT, Z1 FLOAT);
Three points that might help, either separately or in combination: (1) Use exactly "INTEGER PRIMARY KEY". "LONG PRIMARY KEY" and "INT PRIMARY KEY" are not the same thing and do not work as well. (2) In the very latest versions of SQLite, 3.24,0 and the beta for 3.25.0, you can put the "Lines" information directly in the RTree: CREATE VIRTUAL TABLE Lines_bb USING rtree(id, x0, x1, y0, y1, z0, z1, +Label, +Coordinates, +Flags, +StyleId); The extra columns in r-tree are prefaced by a "+" character so that the r-tree module knows that they are auxiliary columns and not extra coordinates. (3) Consider deliberating choosing INTEGER PRIMARY KEY values based on a "Morton code" or "Z-Order curve" of the coordinates. (https://en.wikipedia.org/wiki/Z-order_curve) That will cause features that are close together geographically to tend to be close together within the file. There is are two extension functions in the https://www.sqlite.org/src/artifact/b0ff58fa643afa1d file of the SQLite source tree that might help you with this. Or you can do the same using your own functions. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
