查询数据

当您加载数据集后，您可以开始构建一些查询来发现您的数据告诉您什么。在本节中，您将学习如何将 NYC 出租车数据集中的数据与来自 PostGIS 的地理空间数据相结合，以回答以下问题

为 PostGIS 设置您的数据集

要回答这些地理空间问题，您需要来自 NYC 出租车数据集的乘车计数数据，但您还需要一些地理空间数据来确定行程的起点。Timescale 与所有其他 PostgreSQL 扩展兼容，因此您可以使用 PostGIS 扩展按时间和位置切片数据。

加载扩展后，您可以更改您的超表，使其为地理空间查询做好准备。rides 表包含上车纬度和经度列，但它需要转换为几何坐标，以便与 PostGIS 良好地配合使用。

为 PostGIS 设置您的数据集

连接到包含 NYC 出租车数据集的 Timescale 数据库。
在 psql 提示符下，添加 PostGIS 扩展
```
CREATE EXTENSION postgis;
```
您可以通过运行 \dx 命令检查 PostGIS 是否出现在扩展列表中来检查 PostGIS 是否正确安装。

更改超表以添加用于乘车上车和下车位置的几何列


ALTER TABLE rides ADD COLUMN pickup_geom geometry(POINT,2163);
ALTER TABLE rides ADD COLUMN dropoff_geom geometry(POINT,2163);

将纬度和经度点转换为几何坐标，以便它们与 PostGIS 良好地配合使用。这可能需要一段时间，因为它需要更新两列中的所有数据


UPDATE rides SET pickup_geom = ST_Transform(ST_SetSRID(ST_MakePoint(pickup_longitude,pickup_latitude),4326),2163),
   dropoff_geom = ST_Transform(ST_SetSRID(ST_MakePoint(dropoff_longitude,dropoff_latitude),4326),2163);

2016 年元旦有多少次行程始发于时代广场？

当您为 PostGIS 数据设置好数据库后，您可以构建一个查询，以返回 2016 年元旦在时代广场始发的乘车次数，以 30 分钟为间隔。

查找 2016 年元旦有多少次行程始发于时代广场

注意

时代广场位于 (40.7589,-73.9851)。

连接到包含 NYC 出租车数据集的 Timescale 数据库。

在 psql 提示符下，使用此查询选择 2016 年 1 月 1 日在时代广场 400 米范围内上车的所有行程，并返回每 30 分钟间隔的乘车计数


SELECT time_bucket('30 minutes', pickup_datetime) AS thirty_min,
    COUNT(*) AS near_times_sq
FROM rides
WHERE ST_Distance(pickup_geom, ST_Transform(ST_SetSRID(ST_MakePoint(-73.9851,40.7589),4326),2163)) < 400
AND pickup_datetime < '2016-01-01 14:00'
GROUP BY thirty_min
ORDER BY thirty_min;

您收到的数据看起来有点像这样


thirty_min      | near_times_sq
---------------------+---------------
 2016-01-01 00:00:00 |            74
 2016-01-01 00:30:00 |           102
 2016-01-01 01:00:00 |           120
 2016-01-01 01:30:00 |            98
 2016-01-01 02:00:00 |           112

哪些行程在曼哈顿行驶超过 5 英里？

此查询尤其适合在地图上绘制。它查看了在曼哈顿市内超过 5 英里的行程。

在此查询中，您想要返回超过 5 英里的行程，但也要包括距离，以便您可以使用不同的视觉处理方式可视化更长的距离。该查询还包括一个 WHERE 子句来应用地理空间边界，查找时代广场 2 公里范围内的行程。最后，在 GROUP BY 子句中，提供 trip_distance 和位置变量，以便 Grafana 可以正确绘制数据。

查找在曼哈顿行驶超过 5 英里的行程

连接到包含 NYC 出租车数据集的 Timescale 数据库。

在 psql 提示符下，使用此查询查找曼哈顿超过 5 英里的行程


SELECT time_bucket('5m', rides.pickup_datetime) AS time,
       rides.trip_distance AS value,
       rides.pickup_latitude AS latitude,
       rides.pickup_longitude AS longitude
FROM rides
WHERE rides.pickup_datetime BETWEEN '2016-01-01T01:41:55.986Z' AND '2016-01-01T07:41:55.986Z' AND
  ST_Distance(pickup_geom,
              ST_Transform(ST_SetSRID(ST_MakePoint(-73.9851,40.7589),4326),2163)
  ) < 2000
GROUP BY time,
         rides.trip_distance,
         rides.pickup_latitude,
         rides.pickup_longitude
ORDER BY time
LIMIT 500;

您收到的数据看起来有点像这样


time         | value |      latitude      |      longitude
---------------------+-------+--------------------+---------------------
 2016-01-01 01:40:00 |  0.00 | 40.752281188964844 | -73.975021362304688
 2016-01-01 01:40:00 |  0.09 | 40.755722045898437 | -73.967872619628906
 2016-01-01 01:40:00 |  0.15 | 40.752742767333984 | -73.977737426757813
 2016-01-01 01:40:00 |  0.15 | 40.756877899169922 | -73.969779968261719
 2016-01-01 01:40:00 |  0.18 | 40.756717681884766 | -73.967330932617188
 ...

可选要在 Grafana 中可视化此内容，请创建一个新面板，然后选择 Geomap 可视化。选择 NYC 出租车数据集作为您的数据源，然后键入上一步中的查询。在 Format as 部分中，选择 Table。您的世界地图现在在纽约上方显示一个点，放大以查看可视化效果。
可选为了使此可视化更实用，请更改行程的显示方式。在选项面板中，在 Data layer 下，添加一个名为 Distance traveled 的图层，然后选择 markers 选项。在 Color 部分中，选择 value。您还可以在此处调整符号和大小。
可选选择一种配色方案，以便以不同的颜色显示不同的行程长度。在选项面板中，在 Standard options 下，将 Color scheme 更改为有用的 by value 范围。此示例使用 Blue-Yellow-Red (by value) 选项。