What are in-memory spatio-temporal indexes in Lindorm Ganos-Lindorm(Lindorm)-阿里云帮助中心

Lindorm Ganos provides an in-memory RTree-based index in the Lindorm streaming engine. In-memory spatio-temporal indexes can significantly improve query efficiency for spatio-temporal data when you perform join queries on stream tables. When you use the Lindorm streaming engine to process computing tasks, you can configure related parameters in the connector to specify whether to create an in-memory spatio-temporal index. This topic describes how to use in-memory spatio-temporal indexes and the limits on these indexes.

Limits

You can build only one in-memory spatio-temporal index for a table. The data used to build the index must meet the following requirements:

Points with coordinates: The longitude of the points must be within a range of [-180°,180°] and the latitude of the points must be within a range of [-90°,90°].
Polygons or line strings: The specified polygons or line strings cannot be empty.

Usage

You can enable in-memory spatio-temporal indexes by using the following two methods:

Configure parameters in the wide table connector of the Lindorm streaming engine to enable in-memory spatio-temporal indexes. The following statement provides an example:
```
CREATE TABLE lindorm_table (
  c1 INT,
  c2 VARCHAR,
  c3 GEOMETRY, 
  PRIMARY KEY (c1, c2) NOT ENFORCED
) WITH (
  'connector'='lindorm',
  'seedServer'='ld-bp17pwu1541ia****-proxy-lindorm.lindorm.rds.aliyuncs.com:30020',
  'userName'='yourUser',
  'password'='yourPassword',
  'tableName'='yourTableName',
  'namespace'='yourNamespace',
  'geomHint'='c3:st_contains',
  'geomIndex'='true',
  'cacheTTLMs'='1800000'
);
```
The geomHint, geomIndex, cacheTTLMs parameters are specified for in-memory spatio-temporal indexes. In the statement, the value of geomIndex is set to true, which indicates that an in-memory spatio-temporal index is build when the table is created. For more information, see Configure the wide table connector of the Lindorm streaming engine.
Use hints in SQL statements to configure parameters for the wide table connector of the Lindorm streaming engine.
If you do not configure parameters related to in-memory spatio-temporal indexes in the wide table connector when you create the table, you can use hints in the statement when you query data to dynamically configure the parameters.
In the following example, a stream table named carData is used as an example. The table contains the carID, lng, and lat columns, which separately contain vehicle IDs, the longitude of vehicles, and the latitude of vehicles. When you execute the following statement to query data based on geofences, you can use hints to configure parameters related to in-memory spatio-temporal indexes:
```
SELECT A.carID, B.c1, B.c2 
FROM carData AS A 
JOIN lindorm_table /*+ OPTIONS('geomHint'='c3:st_contains','geomIndex'='true','cacheTTLMs'='1800000') */ 
FOR SYSTEM_TIME AS A.proctime AS B 
ON B.c3=ST_MakePoint(A.lng, A.lat);
```