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 [source]
See the documentation https://sqlite.org/geopoly.html
A Geopoly virtual table has two pre-defined columns that have names you cannot change:
The rowid (integer primary key) of the row is called
The description of the polygon is called
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
pathway so that it is properly spelled
integer primary key. A
bigint primary key is not an
integer primary key (rowid).
(3) By eternal (yh2216) on 2021-04-15 02:18:35 in reply to 2 [link] [source]
thank you very much!