教程:将空间 SQL 函数与 结合使用 Amazon Redshift - Amazon Redshift
AWS 文档中描述的 AWS 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 AWS 服务入门

本文属于机器翻译版本。若本译文内容与英语原文存在差异,则一律以英文原文为准。

教程:将空间 SQL 函数与 结合使用 Amazon Redshift

本教程演示如何将一些空间 SQL 函数用于 Amazon Redshift。

为此,您可以使用空间 SQL 函数查询两个表。本教程使用来自公有数据集的数据,该数据集将出租用地的位置数据与德国柏林的邮政编码相关联。

Prerequisites

在本教程中,您需要以下资源:

  • 您可以访问和更新的现有 Amazon Redshift 集群和数据库。在现有集群中,您可以创建表、加载示例数据并运行 SQL 查询以演示空间函数。您的集群应拥有至少两个节点。要了解如何创建集群,请按照中的步骤操作Amazon Redshift 入门

  • 要使用Amazon Redshift查询编辑器,请确保您的集群位于支持查询编辑器的 AWS 区域中。有关更多信息,请参阅 中的使用查询编辑器查询数据库Amazon Redshift Cluster Management Guide

  • Amazon Redshift 集群的 AWS 凭证,允许它从 加载测试数据Amazon S3。有关如何创建新的访问密钥的信息,请参阅 中的https://docs.amazonaws.cn/IAM/latest/UserGuide/ManagingCredentials.html管理 IAM 用户的访问密钥IAM 用户指南

  • 名为 的 AWS Identity and Access Management (IAM) 角色mySpatialDemoRole,该角色将 托管策略AmazonS3ReadOnlyAccess附加到读取Amazon S3数据。要创建有权从 Amazon S3 存储桶加载数据的角色,请参阅 中的使用 IAM 角色https://docs.amazonaws.cn/redshift/latest/mgmt/copy-unload-iam-role.html授权 COPY、UNLOAD 和 CREATE EXTERNAL SCHEMA Amazon Redshift Cluster Management Guide 操作。

  • 在创建 IAM 角色 后mySpatialDemoRole,该角色需要与您的Amazon Redshift集群关联。有关如何创建该关联的更多信息,请参阅 中的使用 IAM 角色https://docs.amazonaws.cn/redshift/latest/mgmt/copy-unload-iam-role.html授权 COPY、UNLOAD 和 CREATE EXTERNAL SCHEMA Amazon Redshift Cluster Management Guide 操作。

步骤 1:创建表并加载测试数据

本教程使用的源数据位于名为 accommodations.csv 和 的文件中zipcodes.csv

文件accommodations.csv是来自 airbnb.com 的开源数据。zipcodes.csv 文件提供的邮政编码是德国柏林-伯林堡国家统计机构 (Amt fur Statistik Berlin-Brankengue) 的开源数据。两个数据源都是根据知识共享许可证提供的。数据仅限于德国柏林区域。这些文件位于 Amazon S3 公有存储桶中,以便在本教程中使用。

您可以选择从Amazon S3以下链接下载源数据:

可以使用以下过程创建表并加载测试数据。

创建表并加载测试数据

  1. 打开Amazon Redshift查询编辑器。有关使用查询编辑器的更多信息,请参阅 中的使用查询编辑器查询数据库Amazon Redshift Cluster Management Guide

  2. 删除本教程使用的任何表(如果这些表已存在于您的数据库中)。有关更多信息,请参阅步骤 3:清理资源

  3. 创建 accommodations 表以存储每个托管的地理位置(经度和纬度)、列表的名称和其他业务数据。

    本教程在德国柏林探索房间租赁。shape 列存储住区的地理位置。其他列包含有关租赁的信息。

    要创建accommodations表,请在Amazon Redshift查询编辑器中运行以下 SQL 语句。

    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 );
  4. 在查询编辑器中创建zipcode表以存储柏林邮政编码。

    邮政编码wkb_geometry 列中定义为多边形。其余的列描述有关邮政编码的其他空间元数据。

    要创建zipcode表,请在Amazon Redshift查询编辑器中运行以下 SQL 语句。

    CREATE TABLE public.zipcode ( ogc_field INTEGER PRIMARY KEY NOT NULL, wkb_geometry GEOMETRY, gml_id VARCHAR(256), spatial_name VARCHAR(256), spatial_alias VARCHAR(256), spatial_type VARCHAR(256) );
  5. 使用示例数据加载表。

    本教程的示例数据在 存储桶中提供,该Amazon S3存储桶允许所有经过身份验证的 AWS 用户进行读取访问。请确保您提供了允许访问 的有效 AWS 凭证Amazon S3。

    要将测试数据加载到您的表中,请运行以下 COPY 命令。Replace 账户编号 替换为您自己的 AWS 账号。用单引号括起来的凭证字符串分段不能包含任何空格或换行符。

    COPY public.accommodations FROM 's3://redshift-downloads-cn/spatial-data/accommodations.csv' DELIMITER ';' IGNOREHEADER 1 REGION 'cn-north-1' CREDENTIALS 'aws_iam_role=arn:aws:iam::account-number:role/mySpatialDemoRole';
    COPY public.zipcode FROM 's3://redshift-downloads-cn/spatial-data/zipcode.csv' DELIMITER ';' IGNOREHEADER 1 REGION 'cn-north-1' CREDENTIALS 'aws_iam_role=arn:aws:iam::account-number:role/mySpatialDemoRole';
  6. 通过运行以下命令验证每个表是否已正确加载。

    select count(*) from accommodations;
    select count(*) from zipcode;

    以下结果显示测试数据的每个表中的行数。

    表名称 行数
    适应 22,248
    邮政编码 190

步骤 2:查询空间数据

创建和加载表后,您可以使用 SQL SELECT 语句查询它们。以下查询演示了您可以检索的一些信息。您可以编写许多使用空间函数来满足您的需求的其他查询。

查询空间数据

  1. 查询 以获取accommodations存储在表中的总列表数的计数,如下所示。空间参考系统是世界大地测量系统 (WGS) 84,它具有唯一的空间参考标识符 4326。

    SELECT count(*) FROM public.accommodations WHERE ST_SRID(shape) = 4326;
    count ------- 22248
  2. 使用一些其他属性提取已知文本 (WKT) 格式的几何体对象。此外,您还可以验证此邮政编码数据是否也存储在世界大地测量系统 (WGS) 84 中,后者使用空间参考 ID (SRID) 4326。空间数据必须存储在相同的空间参考系统中才能互操作。

    SELECT ogc_field, spatial_name, spatial_type, ST_SRID(wkb_geometry), ST_AsText(wkb_geometry) FROM public.zipcode ORDER BY spatial_name;
    ogc_field spatial_name spatial_type st_srid st_astext --------------------------------------------------------------- 0 10115 Polygon 4326 POLYGON((...)) 4 10117 Polygon 4326 POLYGON((...)) 8 10119 Polygon 4326 POLYGON((...)) ... (190 rows returned)
  3. 选择柏林 Mitte (10117) 的多边形、柏林的山川GeoJSON 格式、其尺寸以及该多边形中的点数。

    SELECT ogc_field, spatial_name, ST_AsGeoJSON(wkb_geometry), ST_Dimension(wkb_geometry), ST_NPoints(wkb_geometry) FROM public.zipcode WHERE spatial_name='10117';
    ogc_field spatial_name spatial_type st_dimension st_npoint ----------------------------------------------------------------------------------------------- 4 10117 {"type":"Polygon", "coordinates":[[[...]]]} 2 331
  4. 运行以下 SQL 命令,查看距 Brandenberg Gate 500 米以内的房间数。

    SELECT count(*) FROM public.accommodations WHERE ST_DistanceSphere(shape, ST_GeomFromText('POINT(13.377704 52.516431)', 4326)) < 500;
    count ------ 29
  5. 通过运行以下查询,从附近列出的 调整中存储的数据获取 Brandenborld Gate 的粗略位置。

    此查询需要子选择。它会导致不同的计数,因为请求的位置与之前查询不同,因为它更接近调整。

    WITH poi(loc) as ( SELECT st_astext(shape) FROM accommodations WHERE name LIKE '%brandenburg gate%' ) SELECT count(*) FROM accommodations a, poi p WHERE ST_DistanceSphere(a.shape, ST_GeomFromText(p.loc, 4326)) < 500;
    count ------ 60
  6. 运行以下查询以显示位于 Brandenbink Gateway 周围的所有房间的详细信息(按价格降序排序)。

    SELECT name, price, ST_AsText(shape) FROM public.accommodations WHERE ST_DistanceSphere(shape, ST_GeomFromText('POINT(13.377704 52.516431)', 4326)) < 500 ORDER BY price DESC;
    name price st_astext ------------------------------------------------------------------------------------------------------- DUPLEX APARTMENT/PENTHOUSE in 5* LOCATION! 7583 300 POINT(13.3826510209548 52.5159819722552) DUPLEX-PENTHOUSE IN FIRST LOCATION! 7582 300 POINT(13.3799997083855 52.5135918444834) ... (29 rows returned)
  7. 运行以下查询,使用邮政编码检索最昂贵的房间。

    SELECT a.price, a.name, ST_AsText(a.shape), z.spatial_name, ST_AsText(z.wkb_geometry) FROM accommodations a, zipcode z WHERE price = 9000 AND ST_Within(a.shape, z.wkb_geometry);
    price name st_astext spatial_name st_astext ------------------------------------------------------------------------------------------------------------------------------------------------- 9000 Ueber den Dächern Berlins Zentrum POINT(13.334436985013 52.4979779501538) 10777 POLYGON((13.3318284987227 52.4956021172799,...
  8. 使用子查询计算最高、最低或中间的适应价格。

    以下查询按邮政编码列出了适应的中价。

    SELECT a.price, a.name, ST_AsText(a.shape), z.spatial_name, ST_AsText(z.wkb_geometry) FROM accommodations a, zipcode z WHERE ST_Within(a.shape, z.wkb_geometry) AND price = (SELECT median(price) FROM accommodations) ORDER BY a.price;
    price name st_astext spatial_name st_astext --------------------------------------------------------------------------------------------------------------------------------------------- 45 "Cozy room Berlin-Mitte" POINT(13.3864349535358 52.5292016386514) 10115 POLYGON((13.3658598465795 52.535659581048,... ... (723 rows returned)
  9. 运行以下查询以检索在柏林列出的房间数。要查找热点,请按邮政编码对这些热点进行分组并按供应量进行排序。

    SELECT z.spatial_name as zip, count(*) as numAccommodations FROM public.accommodations a, public.zipcode z WHERE ST_Within(a.shape, z.wkb_geometry) GROUP BY zip ORDER BY numAccommodations DESC;
    zip numaccommodations ---------------------------- 10245 872 10247 832 10437 733 10115 664 ... (187 rows returned)

步骤 3:清理资源

只要您的集群正在运行,它就会继续产生费用。完成本教程后,您可以删除示例集群。

如果要保留集群但恢复测试数据表使用的存储,请运行以下命令来删除表。

drop table public.accommodations cascade;
drop table public.zipcode cascade;