`PostGIS` 扩展

Timescale Cloud：性能、规模、企业级

自托管产品

MST

PostGIS PostgreSQL 扩展提供了地理数据的存储、索引和查询功能。它有助于空间数据分析，即研究空间或地理数据中的模式、异常和理论。

有关这些函数和可用选项的更多信息，请参阅 PostGIS 文档。

使用 `PostGIS` 扩展分析地理空间数据

PostGIS PostgreSQL 扩展允许您对地理空间时序数据进行复杂的分析。Timescale 理解您面临众多数据挑战，并帮助您发现事件发生的时间和地点。在此示例中，您可以查询 covid 病例的报告时间、报告地点以及特定位置周围报告的病例数量。

使用 `PostGIS` 扩展分析地理空间数据

安装 PostGIS 扩展
```
CREATE EXTENSION postgis;
```

您可以使用 \dx 命令确认扩展是否已安装。已安装的扩展会列出。


List of installed extensions
Name         | Version |   Schema   |                                      Description                                      
---------------------+---------+------------+---------------------------------------------------------------------------------------
 pg_stat_statements  | 1.10    | public     | track planning and execution statistics of all SQL statements executed
 pgcrypto            | 1.3     | public     | cryptographic functions
 plpgsql             | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis             | 3.3.3   | public     | PostGIS geometry and geography spatial types and functions
 timescaledb         | 2.11.0  | public     | Enables scalable inserts and complex queries for time-series data (Community Edition)
 timescaledb_toolkit | 1.16.0  | public     | Library of analytical hyperfunctions,     time-series pipelining, and other SQL utilities
(6 rows)

创建一个名为 covid_location 的超表，其中 location 是一个 GEOGRAPHY 类型列，用于存储使用 4326/WGS84 坐标系的 GPS 坐标，time 记录特定 state_id 的 GPS 坐标记录时间。此超表按 time 列进行分区。
```
CREATE TABLE covid_location (
  time TIMESTAMPTZ NOT NULL,
  state_id INT NOT NULL,
  location GEOGRAPHY(POINT, 4326),
  cases INT NOT NULL,
  deaths INT NOT NULL 
) WITH (
  tsdb.hypertable,
  tsdb.partition_column='time'
);
```
如果您是自托管 TimescaleDB v2.19.3 及更早版本，请创建一个 PostgreSQL 关系表，然后使用 create_hypertable 进行转换。然后，通过调用 ALTER TABLE 启用 Hypercore。
为支持高效查询，在 state_id 列上创建索引。
```
CREATE INDEX ON covid_location (state_id, time DESC);
```

在 covid_location 表中插入一些随机生成的值。新泽西州的经纬度坐标为 (-73.935242 40.730610)，纽约州的经纬度坐标为 (-74.871826 39.833851)。


INSERT INTO covid_location VALUES
('2023-06-28 20:00:00',34,'POINT(-74.871826 39.833851)',5,2),
('2023-06-28 20:00:00',36,'POINT(-73.935242 40.730610)',7,1),
('2023-06-29 20:00:00',34,'POINT(-74.871826 39.833851)',14,0),
('2023-06-29 20:00:00',36,'POINT(-73.935242 40.730610)',12,1),
('2023-06-30 20:00:00',34,'POINT(-74.871826 39.833851)',10,4);

要在特定时期内获取特定州的所有病例，请使用


SELECT * FROM covid_location 
WHERE state_id = 34 AND time BETWEEN '2023-06-28 00:00:00' AND '2023-06-30 23:59:59';

您获得的数据大致如下所示：


time          | state_id |                      location                      | cases | deaths 
------------------------+----------+----------------------------------------------------+-------+--------
 2023-06-28 20:00:00+00 |       34 | 0101000020E61000005C7347FFCBB752C0535E2BA1BBEA4340 |     5 |      2
 2023-06-29 20:00:00+00 |       34 | 0101000020E61000005C7347FFCBB752C0535E2BA1BBEA4340 |    14 |      0
 2023-06-30 20:00:00+00 |       34 | 0101000020E61000005C7347FFCBB752C0535E2BA1BBEA4340 |    10 |      4
(3 rows)

要使用 Timescale SkipScan 功能获取所有州的最新记录病例。将 <Interval_Time> 替换为您运行查询的日期与表中最后一次报告记录日期之间的天数，在本例中为 2023 年 6 月 30 日。
```
SELECT DISTINCT ON (state_id) state_id, ST_AsText(location) AS location 
FROM covid_location 
WHERE time > now() - INTERVAL '<Interval_Time>' 
ORDER BY state_id, 
time DESC;
```
ST_AsText(location) 函数将二进制地理空间数据转换为人类可读的格式。您获得的数据大致如下所示：
```
state_id |          location           
----------+-----------------------------
34 | POINT(-74.871826 39.833851)
(1 row)
```

要随时获取曼哈顿 10000 米范围内的所有病例和州


SELECT DISTINCT cases, state_id 
FROM covid_location 
WHERE ST_DWithin(
location, 
ST_GeogFromText('POINT(-73.9851 40.7589)'), 
10000
);

您获得的数据大致如下所示：


cases | state_id 
-------+----------
 7 |       36
12 |       36
(2 rows)

PostGIS 扩展

使用 PostGIS 扩展分析地理空间数据

使用 PostGIS 扩展分析地理空间数据

相关内容

`PostGIS` 扩展

使用 `PostGIS` 扩展分析地理空间数据

使用 `PostGIS` 扩展分析地理空间数据