地理空间数据

PostgreSQL 扩展：使用 PostGIS 和 Timescale 获取高级地理空间洞察

数据领域是广阔而多面的，不同类型的数据需要不同的处理技术。对于地理空间数据，一个名为 PostGIS 的强大 Postgres 扩展可以扩展这个开源数据库，用一流的地理空间对象和广泛的功能集来丰富您的表格，以便与它们进行交互。

当与选择 Timescale 数据库时获得的时间序列功能相结合时，这将非常强大。

在这篇博文中，我们将讨论如何将这些数据类型设置整合在一起，以管理和操作地理空间和时间序列数据。

PostGIS：处理地理空间和时间序列数据

连接到您的 Timescale 实例，并创建一个新数据库

CREATE EXTENSION postgis IF NOT EXISTS;

在 Timescale 上，您可以通过转到 操作 > 扩展 从您的服务概述中找到可用的扩展，这也会提供安装说明。

现在，创建一个假设的表，用于存储地理空间时间序列数据：一支送货车辆车队及其随时间的 GPS 坐标。

CREATE TABLE vehicle_location (
    time TIMESTAMPTZ NOT NULL,
    vehicle_id INT NOT NULL,
    location GEOGRAPHY(POINT, 4326)
);

这里，location 是一个 GEOGRAPHY 类型列，用于存储 GPS 坐标（使用 4326/WGS84 坐标系），而 time 记录特定 vehicle_id 记录 GPS 坐标的时间。

要从这个常规 PostgreSQL 表创建时间序列超表并插入一些虚拟数据，请使用

SELECT create_hypertable('vehicle_location', 'time');
CREATE INDEX ON vehicle_location(vehicle_id, time DESC);

INSERT INTO vehicle_location VALUES 
  ('2023-05-29 20:00:00', 1, 'POINT(15.3672 -87.7231)'),
  ('2023-05-30 20:00:00', 1, 'POINT(15.3652 -80.7331)'),
  ('2023-05-31 20:00:00', 1, 'POINT(15.2672 -85.7431)');

基本查询

1. 查询时间序列数据

要获取特定车辆（例如，车辆 1）在特定时间段内的所有位置，您可以像这样查询 Timescale

SELECT * 
FROM vehicle_location 
WHERE vehicle_id = 1 AND time BETWEEN '2023-05-30 00:00:00' AND '2023-05-31 23:59:59';

2. 查询地理空间数据

要使用 Timescale SkipScan 功能有效地获取所有车辆的最后已知位置，我们可以编写一个稍微复杂的查询

SELECT DISTINCT ON (vehicle_id) vehicle_id, ST_AsText(location) AS location 
FROM vehicle_location 
WHERE time > now() - INTERVAL '24 hours' 
ORDER BY vehicle_id, 
time DESC;

这里，ST_AsText(location) 用于将二进制地理空间数据转换为人类可读的格式。

3. 同时查询地理空间和时间序列数据

要获取在任何时间都位于特定点 (15.2 -85.743) 1 公里范围内的所有车辆，我们可以使用以下 SQL

SELECT DISTINCT vehicle_id 
FROM vehicle_location 
WHERE ST_DWithin(
          location, 
          ST_GeogFromText('POINT(15.2 -85.743)'), 
          1000
       );

在这个查询中，ST_DWithin(location, ST_GeogFromText('POINT(15.2 -85.743)'), 1000) 检查 location 是否位于点 (15.2 -85.743) 的 1,000 米范围内。

开始使用 PostGIS 和 Timescale

当我们将 PostGIS 和 Timescale 结合在一起时，PostgreSQL 可以成为处理地理空间和时间序列数据的强大工具。这篇文章中展示的示例只是您使用这些工具可以做的事情的皮毛。这两个扩展都提供了更多功能，您可以使用它们对您的地理空间时间序列数据进行复杂的分析。

请记住，高效的数据分析都是关于选择合适的工具。有了 PostGIS 和 Timescale 作为您的武器，您就可以应对各种数据挑战，描述事情发生的时间和地点。

祝您在时间和空间中愉快旅行！

了解更多

想要了解更多关于扩展 PostgreSQL 以实现规模和时间序列场景的信息吗？查看 Timescale 文档中的教程以开始使用。