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

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

本教程演示了如何在 Amazon Redshift 中使用某些空间 SQL 函数。

为此,请使用空间 SQL 函数查询两个表。本教程使用公共数据集中的数据,这些数据集将租赁住宿的位置数据与德国柏林的邮政编码相关联。

Prerequisites

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

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

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

  • Amazon Redshift 集群的 Amazon 凭证,以允许其从 Amazon S3 加载测试数据。有关如何创建新访问密钥的信息,请参阅 IAM 用户指南中的管理 IAM 用户的访问密钥。

  • 名为 mySpatialDemoRole 的 Amazon Identity and Access Management (IAM) 角色,它具有附加的托管策略 AmazonS3ReadOnlyAccess,用于读取 Amazon S3 数据。要创建具有从 Amazon S3 存储桶加载数据的权限的角色,请参阅 Amazon Redshift 集群管理指南中的使用 IAM 角色授权 COPY、UNLOAD 和 CREATE EXTERNAL SCHEMA 操作

  • 创建 IAM 角色 mySpatialDemoRole 后,该角色需要与您的 Amazon Redshift 集群关联。有关如何创建该关联的更多信息,请参阅 Amazon Redshift 集群管理指南中的使用 IAM 角色授权 COPY、UNLOAD 和 CREATE EXTERNAL SCHEMA 操作

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

本教程使用的源数据是名为 accommodations.csvzipcodes.csv 的文件。

accommodations.csv 文件是来自 insideairbnb.com 的开源数据。zipcodes.csv 文件提供的邮政编码是德国柏林-勃兰登堡国家统计研究所 (Amt für Statistik Berlin-Brandenburg) 的开源数据。这两个数据源均根据知识共享许可证提供。数据仅限于德国柏林区域。这些文件位于 Amazon S3 公有存储桶中,可用于本教程。

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

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

要创建表并加载测试数据

  1. 打开 Amazon Redshift 查询编辑器。有关使用查询编辑器的更多信息,请参阅 Amazon Redshift 集群管理指南中的使用查询编辑器查询数据库

  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 用户提供读取访问权限的 Amazon S3 存储桶中。确保您提供了有效的 Amazon 凭证,以允许访问 Amazon S3。

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

    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;

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

    表名称 行数
    住宿 22248
    邮政编码 190

步骤 2:查询空间数据

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

要查询空间数据

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

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

    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. 以 GeoJSON 格式选择柏林米特 (10117)(柏林的一个区)的面、其维度和此面中的点数。

    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 命令,查看距离勃兰登堡门 500 米范围内的住宿地数量。

    SELECT count(*) FROM public.accommodations WHERE ST_DistanceSphere(shape, ST_GeomFromText('POINT(13.377704 52.516431)', 4326)) < 500;
    count ------ 29
  5. 通过运行以下查询,从存储在列为附近的住宿地数据中获取勃兰登堡门的粗略位置。

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

    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. 运行以下查询以显示勃兰登堡门周围所有住宿地的详细信息,按价格降序排列。

    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;