Trajectory datasets grow continuously as new points are appended, but historical data is accessed infrequently. To reduce database storage costs, export cold trajectory data to an Object Storage Service (OSS) bucket. The data remains queryable after export, though at lower performance than local storage.
Create all indexes on the trajectory table before exporting data to OSS. Indexes cannot be created after data is stored in OSS.
Prerequisites
Before you begin, ensure that you have:
An OSS bucket to store the trajectory data
An AccessKey pair with access permissions on the target OSS bucket
Export trajectory data to OSS
Run the following SQL statement to export trajectory data from your table to an OSS bucket:
UPDATE trajectory_table
SET traj = ST_exportTo(
traj,
'OSS://<access key>:<access secret>@oss-cn-beijing-internal.aliyuncs.com/<bucket>/<directory>',
'{"compress":"zstd"}'
);Replace the placeholders in the OSS URL with your actual values:
| Placeholder | Description |
|---|---|
<access key> | The AccessKey ID used to access the OSS bucket |
<access secret> | The AccessKey Secret |
oss-cn-beijing-internal.aliyuncs.com | The OSS endpoint. For the full list of endpoints, see OSS domain names. |
<bucket> | The name of the OSS bucket |
<directory> | The directory path within the bucket where the trajectory data is stored |
The "compress":"zstd" option compresses the exported data using the Zstandard algorithm. For other supported compression methods, see ST_exportTo.
Query exported data
After the export, you can query the trajectory data using the same statement format. Query performance is lower than local storage because data is retrieved from OSS.
Indexes cannot be created after data is exported to OSS. If your queries require indexes, create them before running the export.