教程：将空间 SQL 函数与 Amazon Redshift 配合使用 - Amazon Redshift
先决条件 步骤 1：创建表并加载测试数据步骤 2：查询空间数据步骤 3：清理资源

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

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

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

先决条件

在此教程中，您需要以下资源：

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

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

  • Amazon Redshift 集群的 AWS 凭证，以允许其从 Amazon S3 加载测试数据。有关如何访问其他 AWS 服务（如 Amazon S3）的信息，请参阅授权 Amazon Redshift 访问 AWS 服务。

  • 名为 mySpatialDemoRole 的 AWS 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.csv 和 zipcodes.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. 为这些表加载示例数据。

    本教程的示例数据位于为所有经过身份验证的 AWS 用户提供读取访问权限的 Amazon S3 桶中。确保您提供了有效的 AWS 凭证，以允许访问 Amazon S3 。

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

    COPY public.accommodations 
FROM 's3://redshift-downloads/spatial-data/accommodations.csv' 
DELIMITER ';' 
IGNOREHEADER 1 REGION 'us-east-1'
CREDENTIALS 'aws_iam_role=arn:aws:iam::account-number:role/mySpatialDemoRole';

    COPY public.zipcode 
FROM 's3://redshift-downloads/spatial-data/zipcode.csv' 
DELIMITER ';' 
IGNOREHEADER 1 REGION 'us-east-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;