Lindorm:Quick Start

Prerequisites

• LindormTable is activated, and the database engine version is 2.6.5 or later. For more information about how to view or upgrade the version, see LindormTable Version Guide and Minor version update.

• A Java environment with JDK 1.8 or a later version must be installed.

• The connection address for Lindorm wide table SQL is obtained and a whitelist is configured. For more information, see Access an instance.

Procedure

To create a spatio-temporal data table and query trajectories by spatio-temporal range, perform the following steps:

1. Create a spatio-temporal data table and write data

2. Create a spatio-temporal index to improve query efficiency

3. Query trajectory point data within a specified spatio-temporal range

Create a spatio-temporal data table and write data

Connect to LindormTable using Lindorm-cli and write data

1. Connect to LindormTable. This example uses Lindorm-cli to connect from a client deployed on Linux.

   For more information about how to use a JDBC connection, see Use the Lindorm wide table SQL Java API to connect to and use LindormTable.

   1. Download Lindorm-cli.

   2. Decompress the Lindorm-cli package.

   3. Obtain the connection address and run the following command to connect to the Lindorm wide table.

      ./lindorm-cli -url <jdbc url> -username <username> -password <password>

      Parameter	Example value	How to obtain
      jdbc url	jdbc:lindorm:table:url=http://ld-bp17j28j2y7pm****-proxy-lindorm-pub.lindorm.rds.aliyuncs.com:30060	The connection address for Lindorm wide table SQL. For more information about how to obtain the address, see Access an instance.
      username	root	You can view the username in the Lindorm cluster management system. If you forget the password, you can change it in the cluster management system. For more information, see Change a user password.
      password	root

      The following result is returned:

      Connected to jdbc:lindorm:table:url=http://ld-bp17j28j2y7pm****-proxy-lindorm-pub.lindorm.rds.aliyuncs.com:30060
      lindorm-cli version: 1.0.15

2. Create a spatio-temporal data table in one of the following two ways.

   A spatio-temporal data table stores trajectory points. A trajectory point includes longitude (x), latitude (y), and time (t) information. In Lindorm GanosBase, you can store x and y data in one of two ways. The following table describes the storage methods and their features:

   Storage method	Features
   Use the Geometry(Point) spatial data type to store x and y data in a single column.	High performance.
   Use regular point coordinates to store x and y data in separate columns.	Storing historical data in two separate columns, x and y, causes a performance loss.

   • Use the Geometry(Point) spatial data type to store x and y data in a single column. The statement to create the spatio-temporal data table is as follows:

     CREATE TABLE gps_data (id int, g geometry(point), t timestamp, ship_name varchar, PRIMARY KEY(id, t));

     Parameter	Description
     g	The spatial column. The spatial data type is Geometry(Point).
     t	The time column. The supported data types are Time, Timestamp, or Long. If you use the Long type, the time is represented as a UNIX timestamp in milliseconds.
     ship_name	The name column. For example, the name of the ship that generates the spatio-temporal data points.
     PRIMARY KEY(id, t)	The primary key, which consists of id and t.

   • Use regular point coordinates to store x, y, and t data in three separate columns. The statement to create the spatio-temporal data table is as follows:

     CREATE TABLE gps_data_point (id int, x double, y double, t timestamp, ship_name varchar, PRIMARY KEY(id, t));

3. Write trajectory point data one at a time in one of the following two ways.

   • Use the ST_MakePoint spatio-temporal function to construct trajectory point data. For example, ST_MakePoint(119.073544,25.3244) represents a trajectory point with a longitude of 119.073544 and a latitude of 25.3244.

     INSERT INTO gps_data (id, g, t, ship_name) VALUES (1,ST_MakePoint(119.073544,25.3244), '2021-01-01 10:00:00', 'ship001');
     INSERT INTO gps_data (id, g, t, ship_name) VALUES (1,ST_MakePoint(119.073544,25.3244), '2021-01-01 10:05:03', 'ship001');
     INSERT INTO gps_data (id, g, t, ship_name) VALUES (1,ST_MakePoint(119.073544,25.324382), '2021-01-01 10:08:32', 'ship001');
     INSERT INTO gps_data (id, g, t, ship_name) VALUES (1,ST_MakePoint(119.073536,25.324418), '2021-01-01 10:10:22', 'ship001');
     INSERT INTO gps_data (id, g, t, ship_name) VALUES (2,ST_MakePoint(19.07352,25.34), '2021-01-01 08:20:21', 'ship002');
     INSERT INTO gps_data (id, g, t, ship_name) VALUES (2,ST_MakePoint(19.07352,25.33), '2021-01-01 08:22:20', 'ship002');

     Note

     • For more information about the ST_MakePoint spatio-temporal function, see ST_MakePoint.

     • You can also use the ST_GeomFromText spatio-temporal function to construct trajectory point data. The format of the trajectory points uses the Well-known Text (WKT) standard. However, the ST_GeomFromText function has lower write performance than the ST_MakePoint function. For more information about the ST_GeomFromText spatio-temporal function, see ST_GeomFromText.

   • Use regular point coordinates.

     INSERT INTO gps_data_point (id, x, y, t, ship_name) VALUES (1, 119.073544, 25.3244, '2021-01-01 10:00:00', 'ship001');
     INSERT INTO gps_data_point (id, x, y, t, ship_name) VALUES (1, 119.073544, 25.3244, '2021-01-01 10:05:03', 'ship001');
     INSERT INTO gps_data_point (id, x, y, t, ship_name) VALUES (1, 119.073544, 25.324382, '2021-01-01 10:08:32', 'ship001');
     INSERT INTO gps_data_point (id, x, y, t, ship_name) VALUES (1, 119.073536, 25.324418, '2021-01-01 10:10:22', 'ship001');
     INSERT INTO gps_data_point (id, x, y, t, ship_name) VALUES (2, 19.07352, 25.34, '2021-01-01 08:20:21', 'ship002');
     INSERT INTO gps_data_point (id, x, y, t, ship_name) VALUES (2, 19.07352, 25.33, '2021-01-01 08:22:20', 'ship002');

4. Optional: Write trajectory point data in batches in one of the following two ways.

   • Use the ST_MakePoint spatio-temporal function.

     UPSERT INTO gps_data (id, g, t, ship_name) VALUES(1,ST_MakePoint(119.073544,25.3244), '2021-01-01 10:00:00', 'ship001'),(1,ST_MakePoint(119.073544,25.3244), '2021-01-01 10:05:03', 'ship001'),(1,ST_MakePoint(119.073544,25.324382), '2021-01-01 10:08:32', 'ship001'),(1,ST_MakePoint(119.073536,25.324418), '2021-01-01 10:10:22', 'ship001'),(2,ST_MakePoint(19.07352,25.34), '2021-01-01 08:20:21', 'ship002'),(2,ST_MakePoint(19.07352,25.33), '2021-01-01 08:22:20', 'ship002');

   • Use regular point coordinates.

     UPSERT INTO gps_data_point (id, x, y, t, ship_name) VALUES(1, 119.073544, 25.3244, '2021-01-01 10:00:00', 'ship001'),(1, 119.073544, 25.3244, '2021-01-01 10:05:03', 'ship001'),(1, 119.073544, 25.324382, '2021-01-01 10:08:32', 'ship001'),(1, 119.073536, 25.324418, '2021-01-01 10:10:22', 'ship001'),(2, 19.07352, 25.34, '2021-01-01 08:20:21', 'ship002'),(2, 19.07352, 25.33, '2021-01-01 08:22:20', 'ship002');

5. Use the SELECT statement to query the written data.

   • Use the ST_AsText spatio-temporal function to convert spatio-temporal data into a human-readable text format.

     SELECT id, ST_AsText(g) AS position, ship_name FROM gps_data;

     The following result is returned:

     +----+------------------------------+-----------+
     | id |           position           | ship_name |
     +----+------------------------------+-----------+
     | 1  | POINT (119.073544 25.3244)   | ship001   |
     | 1  | POINT (119.073544 25.3244)   | ship001   |
     | 1  | POINT (119.073544 25.324382) | ship001   |
     | 1  | POINT (119.073536 25.324418) | ship001   |
     | 2  | POINT (19.07352 25.34)       | ship002   |
     | 2  | POINT (19.07352 25.33)       | ship002   |
     +----+------------------------------+-----------+

   • Use regular point coordinates.

     SELECT * FROM gps_data_point;

     The following result is returned:

     +----+-------------------------------+------------+-----------+-----------+
     | id |               t               |     x      |     y     | ship_name |
     +----+-------------------------------+------------+-----------+-----------+
     | 1  | 2021-01-01 10:00:00 +0000 UTC | 119.073544 | 25.3244   | ship001   |
     | 1  | 2021-01-01 10:05:03 +0000 UTC | 119.073544 | 25.3244   | ship001   |
     | 1  | 2021-01-01 10:08:32 +0000 UTC | 119.073544 | 25.324382 | ship001   |
     | 1  | 2021-01-01 10:10:22 +0000 UTC | 119.073536 | 25.324418 | ship001   |
     | 2  | 2021-01-01 08:20:21 +0000 UTC | 19.07352   | 25.34     | ship002   |
     | 2  | 2021-01-01 08:22:20 +0000 UTC | 19.07352   | 25.33     | ship002   |
     +----+-------------------------------+------------+-----------+-----------+

Connect to LindormTable using Java JDBC and write data

// Establish a connection.
Connection connection = DriverManager.getConnection(url, properties);
final String tableName = "testtbl"
// Create a table.
try (Statement stmt = conn.createStatement()) {
    stmt.execute("create table " + tableName +
        "(p1 int, c1 varchar, c2 geometry(point), constraint primary key (p1))");
}

// The parameterized query statement for writing data.
final String upsertSql = "upsert into " + tableName + "(p1,c1,c2) values (?,?,ST_MakePoint(?,?))";

// Prepare the statement.
try (PreparedStatement preparedStatement = conn.prepareStatement(upsertSql)) {
  // Bind the parameters corresponding to the placeholders.
  preparedStatement.setInt(1, 0);
  preparedStatement.setString(2, "name");
  preparedStatement.setDouble(3, 5.0);
  preparedStatement.setDouble(4, 5.0);
  // Execute the write operation.
  preparedStatement.executeUpdate();
}

Create a spatio-temporal index to improve query efficiency

If the WHERE clause of a query statement contains a spatio-temporal range, you can create a spatio-temporal index to accelerate the query. Spatio-temporal indexes are classified into spatio-temporal primary key indexes and spatio-temporal secondary indexes. These correspond to the primary key indexes and secondary indexes in Lindorm. For more information, see Create a spatio-temporal index.

1. Set the properties of the spatio-temporal data table.

   ALTER TABLE gps_data SET 'MUTABILITY'='MUTABLE_LATEST';
   ALTER TABLE gps_data SET 'CONSISTENCY'='strong';

   Note

   If you use an index that allows data updates at any timestamp, you must set the MUTABILITY property to MUTABLE_ALL. Run ALTER TABLE gps_data SET 'MUTABILITY' = 'MUTABLE_ALL';. For more information about Mutability classifications, see Basic concepts.

2. Create a spatio-temporal secondary index. In this example, a spatio-temporal data table is already created. Therefore, you can accelerate data queries only by creating a spatio-temporal secondary index. The following example creates an index on the spatial and time columns.

   CREATE INDEX idt ON gps_data (Z-ORDER(g,t));

Query trajectory point data within a specified spatio-temporal range

SELECT id,t,ST_AsText(g),ship_name FROM gps_data WHERE ST_Contains(ST_GeomFromText('POLYGON ((18 24, 20 24, 20 26, 18 26, 18 24))'),g) AND t>'2021-01-01 08:21:00' AND t<'2021-01-01 08:23:00';

+----+-------------------------------+------------------------+-----------+
| id |               t               |     "ST_AsText"(g)     | ship_name |
+----+-------------------------------+------------------------+-----------+
| 2  | 2021-01-01 08:22:20 +0000 UTC | POINT (19.07352 25.33) | ship002   |
+----+-------------------------------+------------------------+-----------+