Lindorm:Getting started

Prerequisites

• Java Development Kit (JDK) 1.8 or later is installed.

• The endpoint that is required when you use SQL to connect to and use LindormTable is obtained. A whitelist is configured for the Lindorm instance and the IP address of your client is added to the whitelist. For more information, see Connect to a Lindorm instance.

Procedure

You must perform the following steps to write trajectory points to a Lindorm wide table and query the trajectory points:

1. Create a spatio-temporal table and write collected trajectory points to the table

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

3. Query trajectory points within a specified spatio-temporal range

Create a spatio-temporal table and write collected trajectory points to the table

Use Lindorm-cli to connect to and write data to LindormTable

1. Connect to LindormTable. In this topic, Lindorm-cli is used to connect a client deployed on Linux to LindormTable.

   To use JDBC to connect to LindormTable, see Use Java JDBC APIs to develop applications.

   1. Download the installation package of Lindorm-cli.

   2. Decompress the installation package of Lindorm-cli.

   3. Obtain connection information. Then, run a command in the following format to connect to LindormTable:

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

      Parameter	Example	Method used to obtain the parameter value
      jdbc url	jdbc:lindorm:table:url=http://ld-bp17j28j2y7pm****-proxy-lindorm-pub.lindorm.rds.aliyuncs.com:30060	The endpoint that is required when you use SQL to connect to and use LindormTable. For more information about how to obtain the endpoint, see Connect to a Lindorm instance.
      Username	root	You can view the username or reset the password in the cluster management system of LindormTable. For more information, see Change the password of a user.
      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. Use one of the following methods to create a spatio-temporal table.

   A spatio-temporal table is used to store trajectory points. A trajectory point contains the longitude (x), latitude (y), and temporal (t) information. The longitude and latitude information is stored by using two methods. The following table describes the methods used to store longitude and latitude information in Lindorm Ganos.

   Storage method	Description
   Store the longitude and latitude information about a point as a geometry point in one column.	Data stored by using this method can provide better read and write performance.
   Separately store the longitude and latitude information of a point in two columns.	Historical longitude and latitude information is separately stored in two columns. The read and write performance of data stored by using this method is not as high as that of data stored as geometry points.

   • Store the longitude and latitude information about a point as a geometry point in one column. You can execute the following statement to create a spatial-temporal table that stores longitude and latitude information by using this method:

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

     Parameter	Description
     g	The column that stores spatial data. The type of data stored in this column is Geometry(Point).
     t	The column that stores temporal data. This column supports the following data types: Time, Timestamp, and Long. A value of the Long type in this column indicates a UNIX timestamp that is accurate to milliseconds.
     ship_name	The column that stores names. For example, you can store the names of ships by which trajectory points in this table are generated.
     PRIMARY KEY(id, t)	The primary key of the table, which contains the id and t columns.

   • Separately store the longitude, latitude, and temporal information about a point in two columns. You can execute the following statement to create a spatial-temporal table that stores longitude, latitude, and temporal information by using this method:

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

3. Use one of the following methods to individually write trajectory points to the table.

   • Use the spatio-temporal function ST_MakePoint to construct trajectory points. For example, ST_MakePoint(119.073544 25.3244) can be used to construct a trajectory point whose longitude is 119.073544 and latitude is 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 function, see ST_MakePoint.

     • You can also use the spatio-temporal function ST_GeomFromText to construct trajectory points. The trajectory points constructed by this function are in the standard Well-known Text (WKT) format. However, the write performance of ST_GeomFromText is not as high as the ST_MakePoint function. For more information about the ST_GeomFromText function, see ST_GeomFromText.

   • Write the longitude, latitude, and temporal information about the trajectory points to the table.

     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:Use one of the following methods write trajectory points to the table in batches.

   • Use the ST_MakePoint function to write trajectory points in batches.

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

   • Write the longitude, latitude, and temporal information about the trajectory points in batches.

     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 data that is written to the table.

   • Query the trajectory points in the table and use the spatio-temporal function ST_AsText to convert the points into readable texts.

     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   |
     +----+------------------------------+-----------+

   • Query the longitude, latitude, and temporal information about the trajectory points in the table.

     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   |
     +----+-------------------------------+------------+-----------+-----------+

Use JDBC to connect to and write data to LindormTable

// 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))");
}

// Specify the query statement that is used to write data.
final String upsertSql = "upsert into " + tableName + "(p1,c1,c2) values (?,?,ST_MakePoint(?,?))";

// Prepare the query statement.
try (PreparedStatement preparedStatement = conn.prepareStatement(upsertSql)) {
  // Specify parameters that correspond to the placeholders in the statement.
  preparedStatement.setInt(1, 0);
  preparedStatement.setString(2, "name");
  preparedStatement.setDouble(3, 5.0);
  preparedStatement.setDouble(4, 5.0);
  // Write data to LindormTable.
  preparedStatement.executeUpdate();
}

Create a spatio-temporal index to improve query efficiency

If the conditions specified by the WHERE clause in a query statement contain spatio-temporal ranges, you can create a spatio-temporal index to accelerate the query. Spatio-temporal indexes can be classified into two types: spatio-temporal primary key indexes and spatio-temporal secondary indexes, which separately correspond to primary key indexes and secondary indexes in Lindorm. For more information, see Create a spatio-temporal index.

1. Configure the attributes of the spatio-temporal table.

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

   Note

   To use an index that updates based on custom timestamps, you must execute the following statement to set MUTABILITY to MUTABLE_ALL: ALTER TABLE gps_data SET 'MUTABILITY' = 'MUTABLE_ALL';. For more information about the MUTABILITY attribute, see Terms.

2. Create a spatio-temporal secondary index In this example, a spatio-temporal table has already been created. Therefore, you can accelerate the query only by creating a spatio-temporal secondary index. The following statement provides an example on how to create an index that contains the spatial and temporal column.

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

Query trajectory points 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   |
+----+-------------------------------+------------------------+-----------+