suggest:for geopoly,advice to add primary key like rtree
(1) By eternal (yh2216) on 2021-04-14 10:07:28 [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).
(3) By eternal (yh2216) on 2021-04-15 02:18:35 in reply to 2 [link] [source]
thank you very much!