suggest:for geopoly,advice to add primary key like rtree
(1) By eternal (yh2216) on 2021-04-14 10:07:28
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]
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!