On Thu, Aug 30, 2018 at 4:48 AM Richard Hipp <d...@sqlite.org> wrote:
> 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. > Morton code and zorder curves look, initially, to be good for keeping near things near, but the average distance for two elements is much greater than the row skip in a similar linear col + n*rows type flat array; and more often will span +4 distinct pages instead of being just 1 or 2 for here and +/- 1 page for rows above/below On the 2^n boundaries, the skips become very significant, and grabbing random queries is guaranteed to cover more space. so like at 15,15,15 to 16,16,16 there is a huge jump in space ( 4096) instead of a constant offset. There was a voxel engine that was claiming they were going to move to a morton encoding; and I was working with a different engine, so I built a simulator to test averge lookup distances; it was far more efficient to keep sectors of voxels (32x32x32) in flat indexing, which made the maximum distance 1025 ( but along that row is +1023, 1024, 1025, which are all in the same page, where morton would be like +512, +1024, +2048, which makes it much more likely to overflow to yet another page. (since the cells arent' just bytes, all indexes should be mulitplied by cell structure size) (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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
