Logo

菜单

发布于 2026-01-06 4 阅读
0

利用数据绘制 COVID-19 传播图 导入数据集 全球每天报告多少例确诊病例？意大利米兰附近每天报告多少例确诊病例？我附近每周报告多少例确诊病例？使用 Grafana 按地理位置可视化确诊病例 COVID-19 在我附近的传播速度有多快？结论

利用数据绘制 COVID-19 的传播图

导入数据集

全球每天报告的确诊病例有多少例？

意大利米兰附近每天报告多少例确诊病例？

我附近每周报告的确诊病例有多少？

使用 Grafana 按地理位置可视化确诊病例

我附近的 COVID-19 传播速度有多快？

结论

数据让我们深入了解周围的世界，并使我们能够预见和应对 COVID-19 疫情的蔓延。

（本文最初发表于Timescale 博客，日期为 2020 年 3 月 5 日。）

近几个月来，我们目睹了新冠病毒（通常被称为“冠状病毒”）的传播。我们不仅是一个全球分布的团队，而且Timescale团队的一些成员还是家庭成员，他们在急诊室工作时亲身感受到了人们的恐惧和担忧。

首先，我们向所有受到影响的人们致以最深切的慰问。我们与全球社会一道，衷心希望未来几周和几个月情况能够好转，尤其是在控制病毒传播和减缓病毒扩散方面。

我们最近了解到约翰·霍普金斯大学提供了一份时间序列数据集，其中包含来自世界各地的每日病例报告。Timescale 社区成员Joel Natividad创建了这个时间序列实用程序库，使用户能够将约翰·霍普金斯大学的数据集与 TimescaleDB 结合使用，从而更好地监测 COVID-19 疫情。

本文将逐步介绍如何将数据集加载到 TimescaleDB 中，并使用 Grafana 可视化查询结果。我们希望您能将此数据集和教程作为起点，开展您自己的分析（并扩展我们提供的查询），或者与同事、家人和朋友分享信息。

您可以通过两种方式获取TimescaleDB：托管的 Timescale Cloud（包含免费额度）或 TimescaleDB 下载版。您可以按照以下说明选择安装方式。如果您尚未安装 psql，则还需要安装 psql以便从命令行访问数据库。

导入数据集

安装完 TimescaleDB 后，您需要加载数据集。首先，您需要克隆 Joel 创建的 GitHub 仓库：

git clone https://github.com/dathere/covid19-time-series-utilities.git
Enter fullscreen mode Exit fullscreen mode

此仓库包含多个文件，具体说明请参见README文件。请按照GitHub 仓库中的说明设置数据库并导入数据。

数据完全导入后，您应该能够通过以下方式登录数据库psql：

psql -x "postgres://[YOUR USERNAME]:[YOUR PASSWORD]@[YOUR HOST]:[YOUR PORT]/[DB_NAME]?sslmode=require"
Enter fullscreen mode Exit fullscreen mode

登录后，您就可以运行以下查询：

SELECT * FROM covid19_ts LIMIT 5;
Enter fullscreen mode Exit fullscreen mode

您的输出结果应该类似于这样：

province_state | country_region |    observation_date    | confirmed | deaths | recovered
---------------+----------------+------------------------+-----------+--------+-----------
Hubei          | Mainland China | 2020-01-31 23:59:00+00 |      5806 |    204 |       141
Zhejiang       | Mainland China | 2020-01-31 23:59:00+00 |       538 | [null] |        14
Guangdong      | Mainland China | 2020-01-31 23:59:00+00 |       436 | [null] |        11
Henan          | Mainland China | 2020-01-31 23:59:00+00 |       352 |      2 |         3
Hunan          | Mainland China | 2020-01-31 23:59:00+00 |       332 | [null] |         2
(5 rows)
Enter fullscreen mode Exit fullscreen mode

至此，您的时间序列数据库已正确配置，并且您已加载了 COVID-19 数据集。

注：约翰·霍普金斯大学的数据是累计总数，而非每日总数。因此，任何特定地点2月23日的数据代表截至2月23日该地点所有病例的累计总数。

在继续之前，我们需要清理一下数据集。

covid19_ts在某些情况下，表或表中的某一行covid19_locations可能没有province_state正确输入值。清理公共数据集相当常见，幸运的是，这个数据集很容易准备，以便我们继续进行后续教程。请psql输入以下命令：

UPDATE covid19_locations 
SET province_state = 
    CASE WHEN province_state = '' 
    THEN country_region 
    ELSE province_state 
    END;
Enter fullscreen mode Exit fullscreen mode

此时，我们所有的行在列中要么有特定的省或州province_state，要么，如果没有，则默认为country_region。

现在，让我们开始运行一些查询，以便更好地了解疫情。

全球每天报告的确诊病例有多少例？

我们首先来确定每天累计确诊病例数；数据集从 1 月 22 日开始，所以我们的查询也将从那天开始。

SELECT time_bucket('1 day', observation_date) AS day,
       SUM(confirmed)
FROM covid19_ts
GROUP BY day
ORDER BY day;
Enter fullscreen mode Exit fullscreen mode

您的输出结果应该类似于这样：

         day           |  sum
-----------------------+-------
2020-01-22 00:00:00+00 |   555
2020-01-23 00:00:00+00 |   653
2020-01-24 00:00:00+00 |   941
2020-01-25 00:00:00+00 |  1438
2020-01-26 00:00:00+00 |  2118
2020-01-27 00:00:00+00 |  2927
2020-01-28 00:00:00+00 |  5578
2020-01-29 00:00:00+00 |  6165
2020-01-30 00:00:00+00 |  8235
2020-01-31 00:00:00+00 | 10037
2020-02-01 00:00:00+00 | 12009
2020-02-02 00:00:00+00 | 16685
2020-02-03 00:00:00+00 | 19719
2020-02-04 00:00:00+00 | 23821
2020-02-05 00:00:00+00 | 27457
(edited for length)
Enter fullscreen mode Exit fullscreen mode

意大利米兰附近每天报告多少例确诊病例？

截至本文发布之日，除中国以外，意大利北部是新冠肺炎病例最集中的地区，而且疫情仍在加剧。

如果我们想查看意大利新冠肺炎的增长率该怎么办？

为此，我们需要查询country_region field数据库中的数据：

SELECT * FROM covid19_ts WHERE country_region = 'Italy';
Enter fullscreen mode Exit fullscreen mode

我们的输出结果大致如下：

province_state | country_region | observation_date       | confirmed | deaths | recovered
---------------+----------------+------------------------+-----------+--------+-----------
               | Italy          | 2020-01-31 23:59:00+00 |         2 | [null] |    [null]
               | Italy          | 2020-01-31 08:15:00+00 |         2 |      0 |         0
               | Italy          | 2020-01-31 08:15:53+00 |         2 |      0 |         0
               | Italy          | 2020-02-07 17:53:02+00 |         3 |      0 |         0
               | Italy          | 2020-02-21 23:33:06+00 |        20 |      1 |         0
               | Italy          | 2020-02-22 23:43:02+00 |        62 |      2 |         1
               | Italy          | 2020-02-23 23:43:02+00 |       155 |      3 |         2
               | Italy          | 2020-02-24 23:43:01+00 |       229 |      7 |         1
               | Italy          | 2020-02-25 18:55:32+00 |       322 |     10 |         1
               | Italy          | 2020-02-26 23:43:03+00 |       453 |     12 |         3
               | Italy          | 2020-02-27 23:23:02+00 |       655 |     17 |        45
               | Italy          | 2020-02-28 20:13:09+00 |       888 |     21 |        46
               | Italy          | 2020-02-29 18:03:05+00 |      1128 |     29 |        46
               | Italy          | 2020-03-01 23:23:02+00 |      1694 |     34 |        83
               | Italy          | 2020-03-02 20:23:16+00 |      2036 |     52 |       149
(15 rows)
Enter fullscreen mode Exit fullscreen mode

我附近每周报告的确诊病例有多少？

如果我们想查看特定地点附近的病例该怎么办？

这需要我们使用数据库中存储的latitude地理位置longitude信息。为了使用位置信息，我们需要准备好用于地理空间查询的表。

需要注意的是：约翰·霍普金斯大学的数据集包含的具体地点信息有限。例如，意大利的所有病例都被标记为“意大利”，而中国和美国的病例则可能包含更具体的地区或城市信息。

我们可以使用 PostGIS PostgreSQL 扩展来帮助分析地理空间数据。（ Timescale Cloud已预装此扩展，您也可以手动安装到自托管版本中。）PostGIS 允许我们在 TimescaleDB 中按时间和位置对数据进行切片。

CREATE EXTENSION postgis;
Enter fullscreen mode Exit fullscreen mode

然后，运行该\dx命令psql以验证 PostGIS 是否已正确安装。您应该在扩展程序列表中看到 PostGIS 扩展程序，如下所示：

List of installed extensions
Name        | Version |   Schema   |                             Description
------------+---------+------------+---------------------------------------------------------------------
plpgsql     | 1.0     | pg_catalog | PL/pgSQL procedural language
postgis     | 2.5.3   | public     | PostGIS geometry, geography, and raster spatial types and functions
timescaledb | 1.6.0   | public     | Enables scalable inserts and complex queries for time-series data
(3 rows)
Enter fullscreen mode Exit fullscreen mode

我们数据库中的表格covid19_locations包含了约翰·霍普金斯大学数据集中的位置及其latitude坐标longitude。

例如，我们可以看到美国的所有地点，如下所示：

SELECT * FROM covid19_locations WHERE country_region = 'US' LIMIT 10;
Enter fullscreen mode Exit fullscreen mode

该查询的输出结果大致如下：

               province_state                | country_region | latitude | longitude 
---------------------------------------------+----------------+----------+-----------
 Unassigned Location (From Diamond Princess) | US             |  35.4437 |  139.6380
 King County, WA                             | US             |  47.5480 | -121.9836
 Santa Clara, CA                             | US             |  37.3541 | -121.9552
 Snohomish County, WA                        | US             |  48.0330 | -121.8339
 Cook County, IL                             | US             |  41.7377 |  -87.6976
 Fulton County, GA                           | US             |  33.8034 |  -84.3963
 Grafton County, NH                          | US             |  43.9088 |  -71.8260
 Hillsborough, FL                            | US             |  27.9904 |  -82.3018
 Providence, RI                              | US             |  41.8240 |  -71.4128
 Sacramento County, CA                       | US             |  38.4747 | -121.3542
(10 rows)
Enter fullscreen mode Exit fullscreen mode

我们需要修改covid19_locations表格以使其与 PostGIS 兼容。
首先，我们将添加案例位置的几何列：

ALTER TABLE covid19_locations
    ADD COLUMN location_geom geometry(POINT, 2163);
Enter fullscreen mode Exit fullscreen mode

接下来，我们需要将纬度和经度点转换为几何坐标（以便与 PostGIS 兼容）。

UPDATE covid19_locations
    SET location_geom = ST_Transform(ST_SetSRID(ST_MakePoint(longitude, latitude), 4326), 2163);
Enter fullscreen mode Exit fullscreen mode

最后，假设我们要检查美国西雅图附近的病例。西雅图位于（纬度，经度）（47.6062，-122.3321）。

我们的数据库已设置完毕，现在可以运行一个地理空间查询，返回自数据集建立以来，西雅图方圆 75 公里内每天新增的 COVID-19 确诊病例数。（TimescaleDB 基于 PostgreSQL，因此我们可以使用所有常用的 SQL 函数。）

在这种情况下，我们需要使用子查询来获取covid19_locations表中的信息，并使用更广泛的查询来获取所有与子查询中的位置相匹配的已确诊病例。

首先，我们编写子查询，查询covid19_locations表中西雅图 75 公里范围内的所有地点：

SELECT *
FROM covid19_locations
WHERE ST_Distance(location_geom, ST_Transform(ST_SetSRID(ST_MakePoint(-122.3321, 47.6062), 4326), 2163)) < 75000;
Enter fullscreen mode Exit fullscreen mode

我们应该得到类似这样的输出：

province_state       | country_region | latitude | longitude |                   location_geom
---------------------+----------------+----------+-----------+----------------------------------------------------
King County, WA      | US             |  47.5480 | -121.9836 | 010100002073080000A2B8C609FEC838C1004A6D25430F1F41
Snohomish County, WA | US             |  48.0330 | -121.8339 | 010100002073080000344A7104C26438C1768752D481082141
(2 rows)
Enter fullscreen mode Exit fullscreen mode

现在，我们需要在 covid19_ts 数据集中找到所有已确诊的病例，这些病例的 province_state 与我们第一次查询的结果相匹配。

SELECT time_bucket('1 day', observation_date) as day,
       SUM(confirmed) AS near_sea
FROM covid19_ts
WHERE province_state IN (
    SELECT province_state
    FROM covid19_locations
    WHERE ST_Distance(location_geom, ST_Transform(ST_SetSRID(ST_MakePoint(-122.3321, 47.6062), 4326), 2163)) < 75000
)
GROUP BY day
ORDER BY day;
Enter fullscreen mode Exit fullscreen mode

您的输出结果应该类似于这样：

          day           | near_sea 
------------------------+----------
 2020-02-29 00:00:00+00 |        1
 2020-03-01 00:00:00+00 |        2
 2020-03-02 00:00:00+00 |       18
 2020-03-03 00:00:00+00 |       27
 2020-03-04 00:00:00+00 |       39
(5 rows)
Enter fullscreen mode Exit fullscreen mode

使用 Grafana 按地理位置可视化确诊病例

我们有了数据集，我们有了查询语句，现在，让我们以 COVID-19 数据集为例，按地理位置可视化确诊病例总数。

为了创建可视化图表，我们将使用 Grafana。请按照我们的Grafana 入门教程，在 Timescale Cloud、您的桌面或使用 Grafana Cloud 上设置 Grafana，并使用您在本文中使用的数据集。

在 Grafana 中，创建一个新的仪表板并添加一个新的可视化效果。在可视化菜单中，搜索并选择“世界地图”面板类型。
要构建我们的可视化效果，我们需要将查询中的信息正确组织好：

  • 每天
  • 所有确诊病例的总和
  • 按province_state事件发生地点分组
  • 根据纬度和经度在地图上标出province_state
  • 按时间排序

为了构建此查询，我们使用 SQL INNER JOIN 语句将covid19_ts数据集和covid19_locations表中的信息合并在一起：

SELECT time_bucket('1 day', covid19_ts.observation_date) AS day,
       SUM(covid19_ts.confirmed) AS confirmed, 
       covid19_ts.province_state as location, 
       covid19_locations.latitude AS latitude, 
       covid19_locations.longitude AS longitude
FROM covid19_ts
INNER JOIN covid19_locations ON covid19_locations.province_state = covid19_ts.province_state 
WHERE $__timeFilter(covid19_ts.observation_date)
GROUP BY day,
         confirmed,
         location,
         latitude,
         longitude 
ORDER BY day;
Enter fullscreen mode Exit fullscreen mode

要在 Grafana 中输入此查询，请单击“编辑 SQL”并手动输入（或从上方复制粘贴）：

Grafana 用户界面中的 SQL 查询

然后在 WorldMap 配置中，我们将字段映射设置为与查询结果匹配：

将查询中的字段映射到 Grafana

完成后，我们就可以看到 COVID-19 疫情对 Grafana WorldMap 面板的影响：

在 Grafana 中可视化 COVID-19 的传播

我附近的 COVID-19 传播速度有多快？

如前所述，约翰·霍普金斯大学的数据集包含累积数据。例如，2020年3月2日的条目包含了当时所有已确诊的病例。假设我们想了解特定地点附近病例的发现率。

TimescaleDB 包含一项名为“连续聚合”的功能。连续聚合会按照用户指定的时间间隔自动重新计算查询，并将结果保存到表中。这样，数据库无需每次都运行聚合查询，而是可以定期在后台运行常用的聚合操作，用户只需查询聚合结果即可。对于常用的计算，连续聚合应该能够提升数据库性能和查询速度。

在本例中，我们希望持续汇总每日新增确诊病例数的变化。让我们来看一下这个持续汇总查询：

CREATE VIEW daily_change
WITH (timescaledb.continuous, timescaledb.refresh_lag = '-2 days')
AS
SELECT
  country_region,
  province_state,
  time_bucket('2 days', observation_date) as bucket,
  first(confirmed, observation_date) as yesterday,
  last(confirmed, observation_date) as today,
  last(confirmed, observation_date) - first(confirmed, observation_date) as change
FROM
  covid19_ts
GROUP BY country_region, province_state, bucket;
Enter fullscreen mode Exit fullscreen mode

此查询的第一行创建了一个名为 的连续聚合daily_change。然后，我们选择一个time_bucket间隔为两天的聚合，该间隔是使用该covid19_ts.observation_date字段计算的。

在我们的表格中，我们将创建一个yesterday“and today”列，以及一个change表示两者之间差值的列。

在正常情况下，对于大量数据，TimescaleDB 会在后台计算连续聚合，但如果您想立即看到结果，可以强制执行：

ALTER VIEW daily_change SET (timescaledb.refresh_interval = '1 hour');
ALTER VIEW daily_change SET (timescaledb.refresh_lag = '-2 days');
REFRESH MATERIALIZED VIEW daily_change;
Enter fullscreen mode Exit fullscreen mode

我们可以使用简单的 SQL 查询来运行这种连续聚合：

SELECT * FROM daily_change;
Enter fullscreen mode Exit fullscreen mode

该查询的结果应该类似于这样：

          day           | yesterday | today  | change |                  location                   
------------------------+-----------+--------+--------+---------------------------------------------
 2020-01-22 00:00:00+00 |         2 | [null] | [null] | 
 2020-01-22 00:00:00+00 |         1 |      9 |      8 | Anhui
 2020-01-22 00:00:00+00 |    [null] | [null] | [null] | Australia
 2020-01-22 00:00:00+00 |        14 |     22 |      8 | Beijing
 2020-01-22 00:00:00+00 |    [null] | [null] | [null] | Brazil
 2020-01-22 00:00:00+00 |         6 |      9 |      3 | Chongqing
 2020-01-22 00:00:00+00 |    [null] | [null] | [null] | Colombia
 2020-01-22 00:00:00+00 |         1 |      5 |      4 | Fujian
 2020-01-22 00:00:00+00 |    [null] |      2 | [null] | Gansu
 2020-01-22 00:00:00+00 |        26 |     32 |      6 | Guangdong
Enter fullscreen mode Exit fullscreen mode

我们还可以把连续聚合功能融入到其他查询中，例如我们之前用来查看华盛顿州西雅图附近确诊病例变化率的查询。

SELECT bucket, 
       yesterday, 
       today, 
       change, 
       province_state, 
       country_region
FROM daily_change
WHERE province_state IN (
    SELECT province_state
    FROM covid19_locations
    WHERE ST_Distance(location_geom, ST_Transform(ST_SetSRID(ST_MakePoint(-122.3321, 47.6062), 4326), 2163)) < 75000
)
GROUP BY bucket, 
         yesterday, 
         today,  
         change, 
         province_state, 
         country_region
ORDER BY bucket;
Enter fullscreen mode Exit fullscreen mode

我们的输出结果应该如下所示：

         bucket         | yesterday | today | change |    province_state    | country_region 
------------------------+-----------+-------+--------+----------------------+----------------
 2020-02-29 00:00:00+00 |         1 |     2 |      1 | Snohomish County, WA | US
 2020-03-02 00:00:00+00 |         4 |     6 |      2 | Snohomish County, WA | US
 2020-03-02 00:00:00+00 |        14 |    21 |      7 | King County, WA      | US
 2020-03-04 00:00:00+00 |         8 |     8 |      0 | Snohomish County, WA | US
 2020-03-04 00:00:00+00 |        31 |    31 |      0 | King County, WA      | US
(5 rows)
Enter fullscreen mode Exit fullscreen mode

结论

数据让我们深入了解周围的世界，而理解数据则使我们能够将这些见解传递给更多的人。我们鼓励您探索数据集，并在 Twitter 上使用 #Covid19Data 标签分享您的仪表盘和见解（如有任何问题或意见，请提及 @TimescaleDB）。

如果您在使用 TimescaleDB 或编写自己的可视化图表时需要帮助，请参考Hello, Timescale! 教程。此外，欢迎加入我们的 Slack 频道，向 Timescale 的全球用户社区提问并获​​得帮助（我们的工程团队也会在所有频道上活跃）。

最后，无论您身在何处，请采取谨慎措施，做好您和家人的安全准备。请遵循世界卫生组织的指导方针和指示。

文章来源：https://dev.to/tigerdata/charting-the-spread-of-covid-19-using-data-39gh
NextJS 应用路由中实现功能性...
使用 Netplan 配置 Ubu...