`postgis` 扩展

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 坐标记录时间
```
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 
);
```
使用 Timescale 提供的 create_hypertable() 函数将标准表转换为在 time 列上分区的超表。您必须提供表名以及表中包含时间戳数据的列，以便用于分区
```
SELECT create_hypertable('covid_location', by_range('time'));
```

在 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` 扩展分析地理空间数据