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

Prerequisites

Before you begin, ensure that you have:

LindormTable activated with database engine version 2.6.5 or later. To check or upgrade your version, see LindormTable Version Guide and Minor version update
JDK 1.8 or later installed in your Java environment
The connection address for Lindorm wide table SQL and a configured whitelist. See Access an instance

Step 1: Create a table and write trajectory data

Connect using Lindorm-cli

This example uses Lindorm-cli on Linux. To connect via JDBC instead, see Connect using Java JDBC.

Download Lindorm-cli and decompress the package.

Run the following command to connect to the Lindorm wide table:

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. See Access an instance.
`<username>`	`root`	View in the Lindorm cluster management system.
`<password>`	`root`	View in the Lindorm cluster management system.

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

A successful connection returns output similar to:

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

Choose a storage method

Each trajectory point stores longitude (x), latitude (y), and time (t). Lindorm GanosBase supports two ways to store the x and y coordinates:

Storage method	Performance	When to use
`Geometry(Point)` — stores x and y in a single spatial column	Higher	New tables and most production use cases
Separate columns — stores x and y as individual `double` columns	Lower (performance loss from two columns)	When integrating with existing schemas that already split coordinates

Use Geometry(Point) unless you have an existing schema that requires separate columns.

Create the table

Option 1 (recommended): Use Geometry(Point)

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

Column	Type	Description
`g`	`geometry(point)`	Spatial column storing longitude and latitude as a single point
`t`	`timestamp`	Time column. Supported types: `Time`, `Timestamp`, or `Long` (UNIX timestamp in milliseconds)
`ship_name`	`varchar`	Name of the vessel generating the trajectory data
`PRIMARY KEY(id, t)`	—	Composite primary key on `id` and `t`

Option 2: Use separate columns

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

Write trajectory data

Insert rows one at a time

For gps_data (using ST_MakePoint to construct each point):

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) constructs a geometry point from coordinate values. For example, ST_MakePoint(119.073544, 25.3244) produces a point at longitude 119.073544, latitude 25.3244.

Note

ST_GeomFromText also constructs geometry points using the Well-known Text (WKT) format, but has lower write performance than ST_MakePoint. See ST_GeomFromText for details.

For gps_data_point (using separate coordinate columns):

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

(Optional) Batch insert with UPSERT

Use UPSERT to write multiple rows in a single statement:

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

Verify the data

Query gps_data using ST_AsText to display coordinates in human-readable format:

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

Expected output:

+----+------------------------------+-----------+
| 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 gps_data_point directly:

SELECT * FROM gps_data_point;

Expected output:

+----+-------------------------------+------------+-----------+-----------+
| 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 using Java JDBC

To write spatio-temporal data via JDBC, use a parameterized query with PreparedStatement. The example below binds coordinates to ST_MakePoint(?, ?) placeholders.

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

For a complete JDBC connection walkthrough, see Use the Lindorm wide table SQL Java API to connect to and use LindormTable.

Step 2: Speed up queries with a spatio-temporal index

Without an index, a spatio-temporal range query performs a full table scan—slow for large datasets. A spatio-temporal secondary index on the spatial and time columns lets GanosBase skip irrelevant rows and return results in a fraction of the time.

Set the required table properties before creating the index:
Note
If your use case requires updating data at any timestamp (not just the latest), set MUTABILITY to MUTABLE_ALL instead:
```
ALTER TABLE gps_data SET 'MUTABILITY'='MUTABLE_ALL';
```
For details on mutability options, see Basic concepts.
```
ALTER TABLE gps_data SET 'MUTABILITY'='MUTABLE_LATEST';
ALTER TABLE gps_data SET 'CONSISTENCY'='strong';
```
Create a spatio-temporal secondary index on the spatial column g and time column t:
```
CREATE INDEX idt ON gps_data (Z-ORDER(g, t));
```
For more information, see Create a spatio-temporal index.

Step 3: Query trajectory points by spatio-temporal range

Use ST_Contains to find trajectory points within a spatial polygon and a time window. The query below returns all points inside POLYGON ((18 24, 20 24, 20 26, 18 26, 18 24)) recorded between 08:21 and 08:23 on January 1, 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';

Note

Because the Z-ORDER index covers both g and t, the query must include range conditions on both columns to use the index. See Performance tuning for spatio-temporal queries.

Expected output:

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

Only ship002 appears in the results. ship001's points are around longitude 119°, which falls outside the query polygon (x range 18–20). ship002's points are around longitude 19°, which is inside the polygon—but only the 08:22 reading falls within the 08:21–08:23 time window. The 08:20 reading is excluded because it falls before 08:21.

Spatio-temporal functions used in this guide

Function	Signature	Description
`ST_MakePoint`	`ST_MakePoint(longitude, latitude)`	Constructs a `geometry(point)` from coordinate values. Higher write performance than `ST_GeomFromText`. See ST_MakePoint.
`ST_GeomFromText`	`ST_GeomFromText(wkt_string)`	Constructs a geometry from a Well-known Text (WKT) string. See ST_GeomFromText.
`ST_AsText`	`ST_AsText(geometry)`	Converts a geometry value to a human-readable WKT string (e.g., `POINT (119.07 25.32)`).
`ST_Contains`	`ST_Contains(geometry A, geometry B)`	Returns true if geometry A spatially contains geometry B. Used for polygon-based spatial filtering.

For the full function reference, see Introduction to spatio-temporal functions.

What's next

Create a spatio-temporal index — learn about primary key and secondary spatio-temporal index options
Performance tuning for spatio-temporal queries — optimize query performance for production workloads
Introduction to spatio-temporal functions — explore the full GanosBase SQL function library