How to write collected trajectory points to a LindormTable and perform fast queries - Lindorm

Esta guía explica cómo almacenar y consultar datos de trayectorias de vehículos mediante SQL de Lindorm GanosBase. El ejemplo modela un escenario de Internet de los Vehículos (IoV): los puntos de trayectoria GPS se escriben en una tabla ancha de Lindorm y luego se consultan por rango espaciotemporal.

Al finalizar esta guía, usted habrá:

Creado una tabla de datos espaciotemporales e insertado puntos de trayectoria.
Construido un índice espaciotemporal para acelerar las consultas por rango.
Consultado puntos de trayectoria dentro de un polígono espacial y una ventana de tiempo específicos.

Requisitos previos

Antes de empezar, asegúrese de contar con lo siguiente:

LindormTable activado con la versión 2.6.5 o posterior del motor de base de datos. Para comprobar o actualizar su versión, consulte LindormTable Version Guide y Minor version update.
JDK 1.8 o posterior instalado en su entorno Java.
La dirección de conexión para SQL de tabla ancha de Lindorm y una lista de permitidos configurada. Consulte Access an instance.

Paso 1: Crear una tabla y escribir datos de trayectoria

Conectarse mediante Lindorm-cli

Este ejemplo utiliza Lindorm-cli en Linux. Si prefiere conectarse mediante JDBC, consulte Conectarse mediante JDBC de Java.

Descargue Lindorm-cli y descomprima el paquete.

Ejecute el siguiente comando para conectarse a la tabla ancha de Lindorm:

Parámetro	Valor de ejemplo	Cómo obtenerlo
`<jdbc-url>`	`jdbc:lindorm:table:url=http://ld-bp17j28j2y7pm****-proxy-lindorm-pub.lindorm.rds.aliyuncs.com:30060`	La dirección de conexión para SQL de tabla ancha de Lindorm. Consulte Access an instance.
`<username>`	`root`	Consúltelo en el sistema de administración del clúster de Lindorm.
`<password>`	`root`

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

Si la conexión es exitosa, se muestra una salida similar a la siguiente:

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

Elegir un método de almacenamiento

Cada punto de trayectoria almacena la longitud (x), la latitud (y) y el tiempo (t). Lindorm GanosBase admite dos formas de almacenar las coordenadas x e y:

Método de almacenamiento	Rendimiento	Cuándo usarlo
`Geometry(Point)` — almacena x e y en una sola columna espacial	Mayor	Tablas nuevas y la mayoría de los casos de uso en producción.
Columnas separadas — almacena x e y como columnas `double` individuales	Menor (pérdida de rendimiento por usar dos columnas)	Al integrarse con esquemas existentes que ya separan las coordenadas.

Utilice Geometry(Point) a menos que tenga un esquema existente que requiera columnas separadas.

Crear la tabla

**Opción 1 (recomendada): Usar Geometry(Point)**

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

Columna	Tipo	Descripción
`g`	`geometry(point)`	Columna espacial que almacena la longitud y la latitud como un solo punto.
`t`	`timestamp`	Columna de tiempo. Tipos compatibles: `Time`, `Timestamp` o `Long` (marca de tiempo UNIX en milisegundos).
`ship_name`	`varchar`	Nombre de la embarcación que genera los datos de trayectoria.
`PRIMARY KEY(id, t)`	—	Clave primaria compuesta por `id` y `t`.

Opción 2: Usar columnas separadas

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

Escribir datos de trayectoria

Insertar filas una por una

Para gps_data (con ST_MakePoint para construir cada punto):

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(longitude, latitude) construye un punto geométrico a partir de valores de coordenadas. Por ejemplo, ST_MakePoint(119.073544, 25.3244) genera un punto en la longitud 119.073544 y latitud 25.3244.

Nota

ST_GeomFromText también construye puntos geométricos mediante el formato Well-known Text (WKT), pero ofrece un rendimiento de escritura menor que ST_MakePoint. Consulte ST_GeomFromText para más detalles.

Para gps_data_point (con columnas de coordenadas separadas):

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

(Opcional) Insertar lotes con UPSERT

Utilice UPSERT para escribir varias filas en una sola instrucción:

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

Comprobar los datos

Consulte gps_data con ST_AsText para mostrar las coordenadas en un formato legible:

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

Salida esperada:

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

Consulte gps_data_point directamente:

SELECT * FROM gps_data_point;

Salida esperada:

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

Conectarse mediante JDBC de Java

Para escribir datos espaciotemporales mediante JDBC, utilice una consulta parametrizada con PreparedStatement. El siguiente ejemplo vincula las coordenadas a los marcadores de posición de ST_MakePoint(?, ?).

// Establish a connection
Connection connection = DriverManager.getConnection(url, properties);
final String tableName = "testtbl";

// Create the table
try (Statement stmt = conn.createStatement()) {
    stmt.execute("create table " + tableName +
        "(p1 int, c1 varchar, c2 geometry(point), constraint primary key (p1))");
}

// Parameterized upsert statement
final String upsertSql = "upsert into " + tableName + "(p1, c1, c2) values (?, ?, ST_MakePoint(?, ?))";

// Bind parameters and execute
try (PreparedStatement preparedStatement = conn.prepareStatement(upsertSql)) {
    preparedStatement.setInt(1, 0);         // p1: row ID
    preparedStatement.setString(2, "name"); // c1: name
    preparedStatement.setDouble(3, 5.0);    // longitude
    preparedStatement.setDouble(4, 5.0);    // latitude
    preparedStatement.executeUpdate();
}

Para ver una guía completa sobre la conexión JDBC, consulte Use the Lindorm wide table SQL Java API to connect to and use LindormTable.

Paso 2: Acelerar las consultas con un índice espaciotemporal

Sin un índice, una consulta por rango espaciotemporal examina toda la tabla, lo cual es lento para conjuntos de datos grandes. Un índice secundario espaciotemporal en las columnas espacial y de tiempo permite que GanosBase omita las filas irrelevantes y devuelva resultados en una fracción del tiempo.

Establezca las propiedades de tabla obligatorias antes de crear el índice:
Nota
Si su caso de uso requiere actualizar datos en cualquier marca de tiempo (no solo en la más reciente), establezca MUTABILITY en MUTABLE_ALL:
```
ALTER TABLE gps_data SET 'MUTABILITY'='MUTABLE_ALL';
```
Para más detalles sobre las opciones de mutabilidad, consulte Basic concepts.
```
ALTER TABLE gps_data SET 'MUTABILITY'='MUTABLE_LATEST';
ALTER TABLE gps_data SET 'CONSISTENCY'='strong';
```
Cree un índice secundario espaciotemporal en la columna espacial g y en la columna de tiempo t:
```
CREATE INDEX idt ON gps_data (Z-ORDER(g, t));
```
Para más información, consulte Create a spatio-temporal index.

Paso 3: Consultar puntos de trayectoria por rango espaciotemporal

Utilice ST_Contains para buscar puntos de trayectoria dentro de un polígono espacial y una ventana de tiempo. La siguiente consulta devuelve todos los puntos dentro de POLYGON ((18 24, 20 24, 20 26, 18 26, 18 24)) registrados entre las 08:21 y las 08:23 del 1 de enero de 2021:

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

Nota

Dado que el índice Z-ORDER abarca tanto g como t, la consulta debe incluir condiciones de rango en ambas columnas para aprovecharlo. Consulte Performance tuning for spatio-temporal queries.

Salida esperada:

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

Solo ship002 aparece en los resultados. Los puntos de ship001 se ubican cerca de la longitud 119°, fuera del polígono de consulta (rango de x de 18 a 20). Los puntos de ship002 están cerca de la longitud 19°, dentro del polígono, pero solo la lectura de las 08:22 cae en la ventana de tiempo de 08:21 a 08:23. La lectura de las 08:20 se excluye porque ocurrió antes de las 08:21.

Funciones espaciotemporales utilizadas en esta guía

Función	Firma	Descripción
`ST_MakePoint`	`ST_MakePoint(longitude, latitude)`	Construye un `geometry(point)` a partir de valores de coordenadas. Ofrece mayor rendimiento de escritura que `ST_GeomFromText`. Consulte ST_MakePoint.
`ST_GeomFromText`	`ST_GeomFromText(wkt_string)`	Construye una geometría a partir de una cadena Well-known Text (WKT). Consulte ST_GeomFromText.
`ST_AsText`	`ST_AsText(geometry)`	Convierte un valor geométrico en una cadena WKT legible (por ejemplo, `POINT (119.07 25.32)`).
`ST_Contains`	`ST_Contains(geometry A, geometry B)`	Devuelve verdadero si la geometría A contiene espacialmente a la geometría B. Se usa para filtrado espacial basado en polígonos.

Para consultar la referencia completa de funciones, consulte Introduction to spatio-temporal functions.

Pasos siguientes

Create a spatio-temporal index: conozca las opciones de clave primaria e índices secundarios espaciotemporales.
Performance tuning for spatio-temporal queries: optimice el rendimiento de las consultas para cargas de trabajo en producción.
Introduction to spatio-temporal functions: explore la biblioteca completa de funciones SQL de GanosBase.