Spatio-temporal indexes improve query performance on location and time data. Without an index, spatial range queries require a full table scan. LindormTable supports two index types: Z-ORDER indexes for point-based spatio-temporal queries, and S2 secondary indexes for polygon and line coverage queries.
Choose an index type
| Index type | Best for | Geometry types |
|---|---|---|
| Z-ORDER primary key index | Spatial coordinates are the primary lookup dimension | POINT, GEOMETRY(POINT), lon/lat pairs |
| Z-ORDER secondary index | Non-spatial primary key (such as id); spatial query is a secondary access path |
POINT, GEOMETRY(POINT), lon/lat pairs |
| S2 secondary index | Containment and intersection queries on polygon and line data | POLYGON, MULTIPOLYGON, LINESTRING, MULTILINESTRING |
For tables with multiple spatio-temporal columns, create a Z-ORDER primary key index for one column and Z-ORDER secondary indexes for the rest.
Z-ORDER indexes
Z-ORDER indexes encode multi-dimensional spatio-temporal coordinates into a single-dimensional key using a space-filling curve. This makes range queries on spatial and temporal columns efficient without a full table scan.
Z-ORDER primary key index vs. Z-ORDER secondary index
| Z-ORDER primary key index | Z-ORDER secondary index | |
|---|---|---|
| When to create | At table creation only | At table creation or after |
| Can be modified | No | No |
| Can be added later | No | Yes |
| Can be deleted | No | Yes |
Create a Z-ORDER primary key index
Include the Z-ORDER() function in the PRIMARY KEY clause when you create the table. The index cannot be added or changed after the table exists.
Single geometry column
-- Z-ORDER code as the only primary key
CREATE TABLE point_table(id INT, g GEOMETRY(POINT), name VARCHAR, PRIMARY KEY(Z-ORDER(g)));
CREATE TABLE point_table(id INT, lon DOUBLE, lat DOUBLE, name VARCHAR, PRIMARY KEY(Z-ORDER(lon, lat)));
-- Z-ORDER code combined with id
CREATE TABLE point_table(id INT, g GEOMETRY(POINT), name VARCHAR, PRIMARY KEY(Z-ORDER(g),id));
CREATE TABLE point_table(id INT, lon DOUBLE, lat DOUBLE, name VARCHAR, PRIMARY KEY(Z-ORDER(lon, lat),id));Geometry column with a time dimension
-- Z-ORDER code as the only primary key
CREATE TABLE point_table(id INT, g GEOMETRY(POINT), name VARCHAR, t LONG, PRIMARY KEY(Z-ORDER(g,t)));
CREATE TABLE point_table(id INT, lon DOUBLE, lat DOUBLE, name VARCHAR, t LONG, PRIMARY KEY(Z-ORDER(lon, lat, t)));
-- Z-ORDER code combined with id
CREATE TABLE point_table(id INT, g GEOMETRY(POINT), name VARCHAR, t LONG, PRIMARY KEY(Z-ORDER(g,t),id));
CREATE TABLE point_table(id INT, lon DOUBLE, lat DOUBLE, name VARCHAR, t LONG, PRIMARY KEY(Z-ORDER(lon, lat, t),id));Create a Z-ORDER secondary index
Step 1: Create the base table.
CREATE TABLE point_table1(id INT, g GEOMETRY(POINT), name VARCHAR, t LONG, PRIMARY KEY(id));
CREATE TABLE point_table2(id INT, lon DOUBLE, lat DOUBLE, name VARCHAR, t LONG, PRIMARY KEY(id));Step 2: Set the table properties.
Z-ORDER secondary indexes require strong consistency and mutable-latest semantics so that the index stays synchronized with the base table as rows are updated.
ALTER TABLE point_table1 SET 'MUTABILITY'='MUTABLE_LATEST', 'CONSISTENCY'='strong';
ALTER TABLE point_table2 SET 'MUTABILITY'='MUTABLE_LATEST', 'CONSISTENCY'='strong';Step 3: Create the index.
Choose whether to store additional columns in the index to avoid table lookups at query time.
Without data redundancy — the index stores only the Z-ORDER key. Queries that need non-indexed columns perform a table lookup.
-- Spatial column only
CREATE INDEX idx ON point_table1 (Z-ORDER(g));
CREATE INDEX idx ON point_table2 (Z-ORDER(lon, lat));
-- Spatial and time columns
CREATE INDEX idx ON point_table1 (Z-ORDER(g,t));
CREATE INDEX idx ON point_table2 (Z-ORDER(lon, lat, t));With data redundancy (INCLUDE) — the index stores additional columns alongside the Z-ORDER key. Queries that access only the covered columns skip the table lookup entirely, at the cost of increased index storage.
-- Spatial column only
CREATE INDEX idx ON point_table1 (Z-ORDER(g)) INCLUDE (g);
CREATE INDEX idx ON point_table2 (Z-ORDER(lon, lat)) INCLUDE (lon, lat);
-- Spatial and time columns
CREATE INDEX idx ON point_table1 (Z-ORDER(g,t)) INCLUDE (g);
CREATE INDEX idx ON point_table2 (Z-ORDER(lon, lat, t)) INCLUDE (g);S2 secondary indexes
S2 secondary indexes use the S2 geometry library to divide geographic space into a hierarchical grid of cells. Each cell covers a portion of the Earth's surface, and the index maps geometries to the cells they cover. This makes containment and intersection queries on polygon and line data efficient.
S2 secondary indexes apply to POLYGON, MULTIPOLYGON, LINESTRING, and MULTILINESTRING data in WGS84.
LINESTRING and MULTILINESTRING support requires LindormTable 2.6.7.5 or later. If the upgrade option is unavailable in the console, contact technical support (DingTalk ID: s0s3eg3).
S2 secondary indexes can only be created on static tables that store POLYGON or MULTIPOLYGON data.
Create an S2 secondary index
Step 1: Create the base table.
Define the geometry column using the specific type or the generic GEOMETRY type.
-- POLYGON data
CREATE TABLE test_table1 (id INT, g GEOMETRY(POLYGON), name VARCHAR, t LONG, PRIMARY KEY(id));
-- MULTIPOLYGON data
CREATE TABLE test_table1 (id INT, g GEOMETRY(MULTIPOLYGON), name VARCHAR, t LONG, PRIMARY KEY(id));
-- LINESTRING data
CREATE TABLE test_table1 (id INT, g GEOMETRY(LINESTRING), name VARCHAR, t LONG, PRIMARY KEY(id));
-- MULTILINESTRING data
CREATE TABLE test_table1 (id INT, g GEOMETRY(MULTILINESTRING), name VARCHAR, t LONG, PRIMARY KEY(id));
-- Generic GEOMETRY type
CREATE TABLE test_table1 (id INT, g GEOMETRY, name VARCHAR, t LONG, PRIMARY KEY(id));Step 2: Set the table properties.
S2 secondary indexes require strong consistency and mutable-latest semantics so that the index stays synchronized with the base table as rows are updated.
ALTER TABLE test_table1 SET 'MUTABILITY'='MUTABLE_LATEST', 'CONSISTENCY'='strong';Step 3: Create the S2 index.
The s2(column, level) function determines which S2 cells a geometry covers at the specified level. Index creation runs asynchronously by default.
CREATE INDEX s2_idx ON test_table1 (s2(g, 10));To verify the index was created, run:
SHOW INDEX FROM test_table1;Step 4: Build the index.
After all existing data is written to the base table, run BUILD INDEX to populate the index with historical data.
BUILD INDEX s2_idx ON test_table1;BUILD INDEX only synchronizes data that exists in the base table when the statement runs. Rows written after BUILD INDEX starts are not included in this operation.