SQLite Forum

suggest:for geopoly,advice to add primary key like rtree
Login

suggest:for geopoly,advice to add primary key like rtree

(1) By eternal (yh2216) on 2021-04-14 10:07:28 [link] [source]

rtree's struct like this:
CREATE VIRTUAL TABLE demo_index USING rtree(
   id,              -- Integer primary key
   minX, maxX,      -- Minimum and maximum X coordinate
   minY, maxY       -- Minimum and maximum Y coordinate
);
and we can insert other info in another table like below:
CREATE TABLE demo_data(
  id INTEGER PRIMARY KEY,  -- primary key
  objname TEXT,            -- name of the object
  objtype TEXT,            -- object type
  boundary BLOB            -- detailed boundary of object
);

this is very good!

but for geopoly,the table ddl is like this, 
CREATE VIRTUAL TABLE newtab USING geopoly(a,b,c);
and I can't add a primary key for query quickly.

my geopoly tbl is :
CREATE VIRTUAL TABLE
 IF NOT EXISTS path_index USING geopoly
(vehicle_id,
begin_time
);

my data tbl is:
CREATE TABLE IF NOT EXISTS pathway
(id         BIGINT PRIMARY KEY UNIQUE NOT NULL,
path        BLOB,
vehicle_id   TEXT,
begin_time   BIGINT,
end_time     BIGINT,
area         DOUBLE,
rect         TEXT,
block_id     BIGINT,
layer_index  TINYINT,
UNIQUE(vehicle_id,begin_time));

could you help me,thanks a lot?

(2) By Keith Medcalf (kmedcalf) on 2021-04-14 17:07:21 in reply to 1 [link] [source]

See the documentation https://sqlite.org/geopoly.html

A Geopoly virtual table has two pre-defined columns that have names you cannot change: rowid and _shape.

The rowid (integer primary key) of the row is called rowid.

The description of the polygon is called _shape.

The parameters you specify to the name geopoly when you are creating a geopoly table are additional data columns in addition to the rowid and the _shape. (Like column names prefaced with a + in r-tree tables).

So, using your pathway table above and assuming that rect contains a geojson area description, then you would declare and fill the geopoly table path_index as follows:

create virtual table path_index using geopoly();
insert into path_index(rowid, _shape) select id, rect from pathway;

Of course, you should fix your definition of id in pathway so that it is properly spelled integer primary key. A bigint primary key is not an integer primary key (rowid).

See https://sqlite.org/rowidtable.html

(3) By eternal (yh2216) on 2021-04-15 02:18:35 in reply to 2 [source]

thank you very much!