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

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

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

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 [link]

thank you very much!