如何将采集的轨迹点写入Lindorm宽表中并进行快速查询 - 云原生多模数据库 Lindorm

本文以车联网轨迹查询场景为例，通过Lindorm GanosBase SQL将采集的轨迹点写入Lindorm宽表中并根据时空范围进行快速查询。

前提条件

已安装Java环境，要求安装JDK 1.8及以上版本。
已获取Lindorm宽表SQL的连接地址并配置白名单，具体操作，请参见访问实例。

操作流程

从创建时空数据表到根据时空范围查询轨迹，您需要完成以下操作：

创建时空数据表并写入数据
创建时空索引提高查询效率
查询指定时空范围的轨迹点数据

创建时空数据表并写入数据

通过Lindorm-cli连接宽表引擎并写入数据

连接Lindorm宽表引擎。以通过Lindorm-cli连接宽表引擎为例（客户端部署在Linux）。

如果您需要使用JDBC连接方式，请参见通过Lindorm宽表SQL Java API连接并使用宽表引擎。

下载Lindorm-cli。
解压Lindorm-cli压缩包。

获取连接信息并执行下述命令连接Lindorm宽表。

./lindorm-cli -url <jdbc url> -username <用户名> -password <密码>

参数	示例值	获取方法
jdbc url	jdbc:lindorm:table:url=http://ld-bp17j28j2y7pm****-proxy-lindorm-pub.lindorm.rds.aliyuncs.com:30060	Lindorm宽表SQL的连接地址，具体获取方法请参见访问实例。
用户名	root	您可以通过云原生多模数据库 Lindorm集群管理系统查看用户名，如果您忘记对应的密码，可以在集群管理系统中修改密码，具体操作请参见修改用户密码。
密码	root

返回如下结果：

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

通过以下两种方式创建时空数据表。

时空数据表是用来存储轨迹点的。轨迹点包括经度x、纬度y和时间t三类信息，其中x和y数据在Lindorm GanosBase中有两种存储方式。存储方式和特点如下：

存储方式	特点
使用空间数据类型`Geometry(Point)`将x和y数据打包存储在一列。	高性能。
使用普通点坐标的方式将x和y数据分别存储在两列。	历史数据以x、y两列分开存储，但性能会有损失。

使用空间数据类型Geometry(Point)将x和y数据打包存储在一列。创建时空数据表语句如下：

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

参数	说明
g	空间列，空间数据类型为`Geometry(Point)`。
t	时间列，数据类型支持Time、Timestamp或者Long。如果使用Long类型，时间表示方式为Unix时间戳（毫秒）。
ship_name	名称列，例如产生不同时空数据点的船名称。
PRIMARY KEY(id, t)	PRIMARY KEY由`id`和`t`组成。

使用普通点坐标的方式将x、y和t数据分别存储在三列。创建时空数据表语句如下：

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

通过以下两种方式单次写入轨迹点数据。

使用时空函数ST_MakePoint来构造轨迹点数据，例如ST_MakePoint(119.073544,25.3244)表示经度为119.073544，纬度为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');

说明

有关时空函数ST_MakePoint，请参见ST_MakePoint。
您也可以使用时空函数ST_GeomFromText来构造轨迹点数据，轨迹点的格式使用WKT（Well-known Text）标准格式。但是通过ST_GeomFromText函数构造数据的写入性能比ST_MakePoint函数低。有关时空函数ST_GeomFromText，请参见ST_GeomFromText。

使用普通点坐标的方式。

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

可选：通过以下两种方式批量写入轨迹点数据。

使用时空函数ST_MakePoint的方式。

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

使用普通点坐标的方式。

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

通过SELECT语句查询已写入的数据。

使用时空函数ST_AsText将时空数据转换为用户可读的文本描述格式。

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

查询结果如下：

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

使用普通点坐标的方式。

SELECT * FROM gps_data_point;

查询结果如下：

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

通过Java JDBC连接宽表引擎并写入数据

与Lindorm SQL写入其他数据类型一样，写入时空类型的数据时同样支持通过参数化查询的方式进行绑参写入。下面是一个以Java JDBC通过PreapredStatement接口的参数化查询方式写入时空数据的Java代码示例。

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

// 写入的参数化查询语句
final String upsertSql = "upsert into " + tableName + "(p1,c1,c2) values (?,?,ST_MakePoint(?,?))";

// 准备语句
try (PreparedStatement preparedStatement = conn.prepareStatement(upsertSql)) {
  // 与参数占位符一一对应地绑定参数
  preparedStatement.setInt(1, 0);
  preparedStatement.setString(2, "name");
  preparedStatement.setDouble(3, 5.0);
  preparedStatement.setDouble(4, 5.0);
  // 执行写入
  preparedStatement.executeUpdate();
}

创建时空索引提高查询效率

当查询语句的WHERE查询条件中包含时空范围时，需要创建时空索引来加速查询。时空索引分为时空主键索引和时空二级索引，分别对应Lindorm的主键索引和二级索引，具体请参见创建时空索引。

设置时空数据表的属性。
```
ALTER TABLE gps_data SET 'MUTABILITY'='MUTABLE_LATEST';
ALTER TABLE gps_data SET 'CONSISTENCY'='strong';
```
说明
如果使用自定义时间戳（可以在任意时间戳进行数据更新）的索引，需要将MUTABILITY属性设置为MUTABLE_ALL，请执行ALTER TABLE gps_data SET 'MUTABILITY' = 'MUTABLE_ALL';。有关Mutability的分类请参见基本概念。
创建时空二级索引。本示例已经创建了时空数据表，所以只能通过创建时空二级索引来加速数据查询。以下示例是对空间列和时间列进行索引。
```
CREATE INDEX idt ON gps_data (Z-ORDER(g,t));
```

查询指定时空范围的轨迹点数据

使用时空函数ST_Contains查询空间范围为POLYGON ((18 24, 20 24, 20 26, 18 26, 18 24))且时间范围为2021年01月01号08点21分~2021年01月01号08点23分的轨迹点数据。

说明

由于时空二级索引中的Z-ORDER函数包含了g列和t列，所以查询条件中必须同时包含g列和t列的范围。关于优化时空查询的方法请参见时空查询的性能调优。

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

说明

更多时空函数请参见函数概览。