Re: [sqlite] R-Tree Storage Optimization for Points

On 20/6/2014 8:08 PM, Clemens Ladisch wrote: Not without changing the SQLite code. A non-leaf R-tree node must store the extents covered by all its children, so these are (n-dimensional) rectangles. At the moment, SQLite assumes that user data has exactly the same format, so such a change

Re: [sqlite] R-Tree Storage Optimization for Points

Mohit Sindhwani wrote: > I was wondering if there is a way that we could save space on the > R-Tree storage if the item being inserting is just a single point > (such that x1=x2 and y1=y2). Not without changing the SQLite code. A non-leaf R-tree node must store the extents covered by all its

Re: [sqlite] R-Tree Storage Optimization for Points

Hello All... On 20/6/2014 3:01 AM, Wolfgang Enzinger wrote: I stand corrected. Should have tried this before: sqlite> INSERT INTO abc VALUES(2,30,20); Error: constraint failed Note to self: r-tree is about *ranges* in 1 to 5 dimensions. Coming back to the original problem again... I was

Re: [sqlite] R-Tree Storage Optimization for Points

Dan Kennedy writes: > Probably not. The CREATE TABLE code above actually creates a > 1-dimensional r-tree with deceptive column names. Column "y" contains > the maximum value for the first dimension: > > SQLite version 3.8.5 2014-06-19 12:34:33 > Enter ".help" for usage

Re: [sqlite] R-Tree Storage Optimization for Points

On 06/20/2014 12:10 AM, Alysson Gonçalves de Azevedo wrote: sqlite> INSERT INTO abc VALUES(NULL, 20, 10); *The first column is always a 64-bit signed integer primary key*. Right, but if you insert NULL it assigns a value automatically. The constraint failure is because the minimum value of

Re: [sqlite] R-Tree Storage Optimization for Points

> > sqlite> INSERT INTO abc VALUES(NULL, 20, 10); *The first column is always a 64-bit signed integer primary key*. The other >> columns are pairs, one pair per dimension, containing the minimum and >> maximum values for that dimension, respectively. >> > Alysson Gonçalves de Azevedo

Re: [sqlite] R-Tree Storage Optimization for Points

On 06/19/2014 11:57 PM, Mohit Sindhwani wrote: Hi Wolfgang, On 19/6/2014 11:54 PM, Wolfgang Enzinger wrote: Not sure why you think you have to store those point coordinates twice. This works: sqlite> CREATE VIRTUAL TABLE abc USING rtree(id,x,y); sqlite> INSERT INTO abc VALUES(1,20,30);

Re: [sqlite] R-Tree Storage Optimization for Points

Hi Wolfgang, On 19/6/2014 11:54 PM, Wolfgang Enzinger wrote: Not sure why you think you have to store those point coordinates twice. This works: sqlite> CREATE VIRTUAL TABLE abc USING rtree(id,x,y); sqlite> INSERT INTO abc VALUES(1,20,30); sqlite> SELECT id FROM abc WHERE x>=10 AND x<=30 AND

Re: [sqlite] R-Tree Storage Optimization for Points

Mohit Sindhwani writes: > However, since these are points that are stored in the table, x1=x2 and > y1=y2 when we do the insertion. As a former embedded systems engineer, > this feels like a waste since I can see that we are inserting exactly > the same value into the table. > >

Re: [sqlite] R-Tree Storage Optimization for Points

It should be painless if you omit geos, I think. On 19 June 2014 09:00, Mohit Sindhwani wrote: > Hi Noël, > > Thanks for our reply. > > > On 19/6/2014 2:19 PM, Noel Frankinet wrote: > >> Hi Mohit, >> >> Maybe you should use the spatialite extension ? >> >> Noël >> > > I have to

Re: [sqlite] R-Tree Storage Optimization for Points

Hi Noël, Thanks for our reply. On 19/6/2014 2:19 PM, Noel Frankinet wrote: Hi Mohit, Maybe you should use the spatialite extension ? Noël I have to see if indeed spatialite handles the data more efficiently since it also relies on the R-Tree for quite a bit of stuff. That said, I do

Re: [sqlite] R-Tree Storage Optimization for Points

Hi Mohit, Maybe you should use the spatialite extension ? Noël On 19 June 2014 06:27, Mohit Sindhwani wrote: > Hello! We are using SQLite3 for storing geographical points that can be > queried using a bounding box (find everything that lies within this box). > Obviously,

[sqlite] R-Tree Storage Optimization for Points

Hello! We are using SQLite3 for storing geographical points that can be queried using a bounding box (find everything that lies within this box). Obviously, this query fits the capabilities of the RTree module very well and it is a simple 2 dimensional search using an R-Tree that has 5