Lindorm:Create a spatio-temporal index

Usage differences

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 primary key index contains only the Z-ORDER code.
  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)));
  
  -- The primary key index contains a combination of the Z-ORDER code and the 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));

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

  -- The primary key index contains only the Z-ORDER code.
  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)));
  
  
  -- The primary key index contains a combination of the Z-ORDER code and the 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

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_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));

2. Set the properties of the primary table. Set the MUTABILITY property to MUTABLE_LATEST and the CONSISTENCY property to strong.

   ALTER TABLE point_table1 SET 'MUTABILITY'='MUTABLE_LATEST', 'CONSISTENCY'='strong';
   
   ALTER TABLE point_table2 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: No data redundancy.

       CREATE INDEX idx ON point_table1 (Z-ORDER(g));
       
       CREATE INDEX idx ON point_table2 (Z-ORDER(lon, lat));

     • Method 2: Use include(g) for data redundancy to avoid table lookups.

       CREATE INDEX idx ON point_table1 (Z-ORDER(g)) INCLUDE (g);
       
       CREATE INDEX idx ON point_table2 (Z-ORDER(lon, lat)) INCLUDE (lon, lat);

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

     • Method 1: Using no data redundancy.

       CREATE INDEX idx ON point_table1 (Z-ORDER(g,t));
       
       CREATE INDEX idx ON point_table2 (Z-ORDER(lon, lat, t));

     • Method 2: Use include(g) for data redundancy to avoid table lookups.

       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);

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 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));

   • Set the data column type to the generic GEOMETRY type.

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

2. Set the properties of the primary table. Set the MUTABILITY property to MUTABLE_LATEST and the CONSISTENCY property 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

   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

   Execute the BUILD INDEX statement to synchronize historical data after all data is written to the primary table. Data that is added to the primary table after the BUILD INDEX statement starts to execute is not synchronized to the index table.