Lindorm:Create a spatio-temporal index

Differences between Z-ORDER primary key indexes and Z-ORDER secondary indexes

The following table describes the difference between Z-ORDER primary key indexes and Z-ORDER secondary indexes in usage.

Create a Z-ORDER primary key index

If you use the spatio-temporal code generated by the Z-ORDER function as a part of a primary key index, the primary key index is a Z-ORDER primary key index. The following examples show the syntax that you can use to create a Z-ORDER primary key index:

• Use the Z-ORDER indexing function to generate a spatio-temporal code for the g column.

  -- The Z-ORDER primary key index contains only the spatio-temporal code generated by the Z-ORDER function.
  CREATE TABLE point_table(id INT, g GEOMETRY(POINT), name VARCHAR, PRIMARY KEY(Z-ORDER(g)));
  
  -- The Z-ORDER primary key index contains the spatio-temporal code generated by the Z-ORDER function and the id column.
  CREATE TABLE point_table(id INT, g GEOMETRY(POINT), name VARCHAR, PRIMARY KEY(Z-ORDER(g),id));

• Use the Z-ORDER indexing function to generate spatio-temporal codes for the g and t columns.

  -- The Z-ORDER primary key index contains only the spatio-temporal code generated by the Z-ORDER function.
  CREATE TABLE point_table(id INT, g GEOMETRY(POINT), name VARCHAR, t LONG, PRIMARY KEY(Z-ORDER(g,t)));
  
  -- The Z-ORDER primary key index contains the spatio-temporal code generated by the Z-ORDER function and the id column.
  CREATE TABLE point_table(id INT, g GEOMETRY(POINT), name VARCHAR, t LONG, PRIMARY KEY(Z-ORDER(g,t),id));

Create a Z-ORDER secondary index

If you use the spatio-temporal code generated by the Z-ORDER function as a part of a secondary index, the secondary index is a Z-ORDER secondary index. The following example shows how to create a Z-ORDER secondary index:

1. Create a base table to store spatio-temporal data.

   CREATE TABLE point_table(id INT, g GEOMETRY(POINT), name VARCHAR, t LONG, PRIMARY KEY(id));

2. Configure attributes for the base table. Set the MUTABILITY attribute to MUTABLE_LATEST and the CONSISTENCY attribute to strong.

   ALTER TABLE point_table SET 'MUTABILITY'='MUTABLE_LATEST', 'CONSISTENCY'='strong';

3. Create a Z-ORDER secondary index.

   • Use the Z-ORDER indexing function to generate a spatio-temporal code for the g column.

     • Method 1: Do not specify included columns for the index

       CREATE INDEX idx ON point_table (Z-ORDER(g));

     • Method 2: Use include(g) to specify included columns for the index to avoid the querying base table data.

       CREATE INDEX idx ON point_table (Z-ORDER(g)) INCLUDE (g);

   • Use the Z-ORDER indexing function to generate spatio-temporal codes for the g and t columns.

     • Method 1: Do not specify included columns for the index

       CREATE INDEX idx ON point_table (Z-ORDER(g,t));

     • Method 2: Use include(g) to specify included columns for the index to avoid the querying base table data.

       CREATE INDEX idx ON point_table (Z-ORDER(g,t)) INCLUDE (g);

Create an S2 secondary index

1. Create a base table to store POLYGON, MULTIPOLYGON, LINESTRING, or MULTILINESTRING data. You can define the column that stores POLYGON, MULTIPOLYGON, LINESTRING, or MULTILINESTRING data by using the following methods:

   • Use GEOMETRY(POLYGON) to define a column that stores POLYGON data.

     CREATE TABLE test_table1 (id INT, g GEOMETRY(POLYGON), name VARCHAR, t LONG, PRIMARY KEY(id));

   • Use GEOMETRY(MULTIPOLYGON) to define a column that stores MULTIPOLYGON data.

     CREATE TABLE test_table1 (id INT, g GEOMETRY(MULTIPOLYGON), name VARCHAR, t LONG, PRIMARY KEY(id));

   • Use GEOMETRY(LINESTRING) to define a column that stores LINESTRING data.

     CREATE TABLE test_table1 (id INT, g GEOMETRY(LINESTRING), name VARCHAR, t LONG, PRIMARY KEY(id));

   • Use GEOMETRY(MULTILINESTRING) to define a column that stores MULTILINESTRING data.

     CREATE TABLE test_table1 (id INT, g GEOMETRY(MULTILINESTRING), name VARCHAR, t LONG, PRIMARY KEY(id));

   • Define a column that stores POLYGON, MULTIPOLYGON, LINESTRING, or MULTILINESTRING data without specifying a data type for GEOMETRY.

     CREATE TABLE test_table1 (id INT, g GEOMETRY, name VARCHAR, t LONG, PRIMARY KEY(id));

2. Configure attributes for the base table. Set the MUTABILITY attribute to MUTABLE_LATEST and the CONSISTENCY attribute to strong.

   ALTER TABLE test_table1 SET 'MUTABILITY'='MUTABLE_LATEST', 'CONSISTENCY'='strong';

3. Use the S2 indexing function to calculate the cells covered by the data in the g column at the specified level. You can create an S2 secondary index only for a static table that stores POLYGON or MULTIPOLYGON data. By default, the index is created asynchronously. The following statement provides an example on how to create an S2 secondary index:

   CREATE INDEX s2_idx ON test_table1 (s2(g, 10));

   Verify the result

   You can execute the SHOW INDEX FROM test_table1; statement to check whether the index is created.

4. Build the index.

   BUILD INDEX s2_idx ON test_table1;

   Note

   We recommend that you execute the BUILD INDEX statement to synchronize historical data after all data in the base table is written to the index. Data that is added to the base table after the BUILD INDEX statement starts to be executed is not synchronized to the index table.