通过 Amazon Redshift 使用空间数据

今天，Amazon Redshift 宣布对称为 GEOMETRY 的新本地数据类型提供支持。这种新数据类型支持提取、存储和查询二维地理数据，并且能够将空间函数应用于该数据。地理数据（也称为地理参照数据）是指与相对于地球的位置有某些关联的数据。坐标、海拔、地址、城市名称、邮政编码、行政和社会经济边界都是地理数据的示例。

GEOMETRY 类型可使我们轻松处理表列中的经纬度等坐标，然后使用空间函数转换它们或将它们与其他的地理数据类型结合。类型是抽象的，这意味着不能直接实例化，而且类型还是多形态的。此数据支持的实际类型（及表列中将使用的类型）包括点、线串、多边形、多点、多线串、多多边形和几何图形集合。除了在表中创建 GEOMETRY 类型的数据列之外，在新支持下，还可以使用现有的 COPY 命令从分隔的文本文件中提取地理数据。文件中的数据将以十六进制扩充熟知二进制 (EWKB) 格式，它是表示地理数据的标准格式。

为了显示这种新类型的实际应用，我设想了一个场景，我在德国柏林担任个人旅游协调员，我的客户为我提供了他们想要游览的景点列表。我的任务是在特定的预算范围内为此客户寻找处于这些景点合理中心位置的住宿。地理数据是解决此场景的理想选择。首先，代表这些景点的一组点组合形成一个或多个多边形，我可以用它们来限制我的住宿搜索。然后，在一个查询中，我们可以将表示这些多边形的数据与表示一组住宿位置的数据结合，以得到结果。从 CPU 方面看，这种空间查询实际上非常昂贵，但 Redshift 能够在不到一秒的时间内执行此查询。

场景数据示例
为显示我的场景的实际应用，我需要先查找有关柏林的各种地理数据。首先，我通过几个“前 X 个值得游览的景点”旅游网站获得了这个城市各个景点的地址和纬度/经度坐标。关于住宿，我在创作共用 1.0 通用“公共领域贡献宣言”的许可下使用 http://insideairbnb.com/get-the-data.html 中的 Airbnb 数据。然后，我在创作共用署名 3.0 德国版许可证 (CC BY 3.0 DE) 的许可下添加到此城市的邮政编码数据。此数据的提供商为 Amt für Statistik Berlin-Brandenburg。

当然，任何一个优秀的旅游协调员都要有一个包含互动地图的网站或应用程序，以便能够向客户显示匹配其标准的住宿位置。在现实生活中，我不是旅游协调员（在我的家人之外！），因此，在此博文中，我的关注点只是后端流程，也就是加载数据，并且最终使用 Redshift 控制台进行查询以满足客户要求。

创建 Redshift 集群
我的第一项任务是加载各种示例数据源到 Redshift 集群中的数据库表中。为执行此操作，我前往 Redshift 控制台控制面板并选择创建集群。随后，系统启动了一个向导，指导我设置新集群，从我要创建的节点类型和数量开始。

在集群详细信息中，我填写了新集群的名称、设置了主用户密码，并选择了一个 AWS Identity and Access Management (IAM) 角色，以在我稍后加载示例数据时，为 Redshift 授予在只读模式下访问 Amazon Simple Storage Service (S3) 中的其中一个存储桶的权限。将在我的默认 Amazon Virtual Private Cloud 中为该区域创建新集群，而且我还可以选择使用默认的节点类型和节点数量。您可以在管理指南中阅读有关创建集群的可用选项的更多信息。最后，我点击创建集群以开始此过程，此过程只需要几分钟时间。

加载示例数据
当集群即时可用时，我可以将示例数据加载到我的数据库中，因此我前往查询编辑器并使用弹出窗口连接到集群的默认数据库。

我的示例数据将来源于作为私有对象上传到 S3 存储桶并加载到三个表中的分隔文本文件。第一个表是住宿，将保留 Airbnb 数据。第二个是邮政编码，将保留该城市的邮政编码。最后一个表是景点，将保留可供我的客户选择的城市景点的坐标。为创建和加载住宿数据，我在查询编辑器中一次一个地将以下语句粘贴到选项卡中，然后运行它们。请注意，数据库中的架构具有访问控制语义，且下面的表明显示的公共前缀只是表示我在为使用中的数据库引用可供所有用户访问的公共架构。

为创建住宿表，我使用：

CREATE TABLE public.accommodations (
  id INTEGER PRIMARY KEY,
  shape GEOMETRY,
  name VARCHAR(100),
  host_name VARCHAR(100),
  neighbourhood_group VARCHAR(100),
  neighbourhood VARCHAR(100),
  room_type VARCHAR(100),
  price SMALLINT,
  minimum_nights SMALLINT,
  number_of_reviews SMALLINT,
  last_review DATE,
  reviews_per_month NUMERIC(8,2),
  calculated_host_listings_count SMALLINT,
  availability_365 SMALLINT
);

要从 S3 加载数据：

COPY public.accommodations
FROM 's3://my-bucket-name/redshift-gis/accommodations.csv'
DELIMITER ';'
IGNOREHEADER 1
CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/RedshiftDemoRole';

接下来，为邮政编码表重复此过程。

CREATE TABLE public.zipcode (
  ogc_field INTEGER,
  wkb_geometry GEOMETRY,
  gml_id VARCHAR,
  spatial_name VARCHAR,
  spatial_alias VARCHAR,
  spatial_type VARCHAR
);

COPY public.zipcode
FROM 's3://my-bucket-name/redshift-gis/zipcode.csv'
DELIMITER ';'
IGNOREHEADER 1
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftDemoRole';

最后，创建景点表并将数据加载到该表中。

CREATE TABLE public.berlin_attractions (
  name VARCHAR,
  address VARCHAR,
  lat DOUBLE PRECISION,
  lon DOUBLE PRECISION,
  gps_lat VARCHAR,
  gps_lon VARCHAR
);

COPY public.berlin_attractions
FROM 's3://my-bucket-name/redshift-gis/berlin-attraction-coordinates.txt'
DELIMITER '|'
IGNOREHEADER 1
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftDemoRole';

找到住宿的地方！
加载好数据后，现在我可以承担起我的旅游协调员角色并为客户选择一些属性，供他们选择在柏林的住宿时考虑。请记住，在真实世界中，这可能是 Web 或其他应用程序为客户提供的。我只是想再使用一次查询编辑器。

我的客户决定来一次以城市博物馆为重点的旅游，他们每晚的住宿费预算的 200 欧元。我在编辑器中打开一个新选项卡，粘贴并允许以下查询。

WITH museums(name,loc) AS
(SELECT name, ST_SetSRID(ST_Point(lon,lat),4326) FROM public.berlin_attractions
WHERE name LIKE '%Museum%')
SELECT a.name,a.price,avg(ST_DistanceSphere(m.loc,a.shape)) AS avg_distance
FROM museums m,public.accommodations a
WHERE a.price <= 200 GROUP BY a.name,a.price ORDER BY avg_distance
LIMIT 10;

查询找到了便于参观所有博物馆的最佳住宿位置，而且价格也在客户的预算内。这里的“最佳位置”指的是与所有选定博物馆的平均距离最小。在查询中，您可以看到一些可用的空间函数，包括可用于景点的经纬度 GEOMETRY 列的 ST_SetSRID 和 ST_Point 及用于确定距离的 ST_DistanceSphere。

这将产生如下结果。

将 Web 或本机应用程序前端包装起来，我们将得到一个新的基于地理数据的应用程序，我们可以用它来满足知道自己想要参观这个城市的哪些地方而且希望找到一个方便且在预算内的最佳住宿位置来实现其旅游目的的客户的要求。

我们来考虑另外一个场景。想象一下，我有个客户想要住在柏林市中心，但他不确定市中心有哪些景点或住宿，而且他的预算为每晚 150 欧元。我们如何回答这个问题？首先，我们需要知道我们所认为的柏林市中心的坐标，纬度 52.516667、经度 13.388889。使用邮政编码表，我们可以将该坐标位置转换为包围该城市区域的多边形。然后，我们的查询一定会获取该多边形内的所有景点，以及按到景点的平均距离排序的所有住宿位置（在预算内）。查询如下：

WITH center(geom) AS
(SELECT wkb_geometry FROM zipcode
  WHERE ST_Within(ST_SetSRID(ST_Point(13.388889, 52.516667), 4326), wkb_geometry)),
pois(name,loc) AS
(SELECT name, ST_SetSRID(ST_Point(lon,lat),4326) FROM public.berlin_attractions,center
  WHERE ST_Within(ST_SetSRID(ST_Point(lon,lat),4326), center.geom))
SELECT a.name,a.price,avg(ST_DistanceSphere(p.loc,a.shape))
  AS avg_distance, LISTAGG(p.name, ';') as pois
FROM pois p,public.accommodations a
WHERE a.price <= 150 GROUP BY a.name,a.price ORDER BY avg_distance
LIMIT 10;

当我在查询编辑器中运行此查询时，获得的结果如下。我可以在 pois 列中看到以邮政编码表示的区域中的景点列表。

这里会产生一些场景，在这些场景中，我们使用新的 GEOMETRY 类型和相关空间函数在 Amazon Redshift 中使用地理数据，我确信还会产生更多场景！现在，新的类型和功能已在所有 AWS 区域向所有客户推出，无需额外费用。

– Steve

亚马逊AWS官方博客

通过 Amazon Redshift 使用空间数据