从补丁 198 开始，Amazon Redshift 将不再支持创建新的 Python UDF。现有的 Python UDF 将继续正常运行至 2026 年 6 月 30 日。有关更多信息，请参阅[博客文章](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/)。

# 教程：将空间 SQL 函数与 Amazon Redshift 配合使用
<a name="spatial-tutorial"></a>

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

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

**Topics**
+ [先决条件](#spatial-tutorial-prerequisites)
+ [步骤 1：创建表并加载测试数据](#spatial-tutorial-test-data)
+ [步骤 2：查询空间数据](#spatial-tutorial-query)
+ [步骤 3：清理资源](#spatial-tutorial-clean-up)

## 先决条件
<a name="spatial-tutorial-prerequisites"></a>

在此教程中，您需要以下资源：
+ 您可以访问和更新的现有 Amazon Redshift 集群和数据库。在现有集群中，您可以创建表、加载示例数据并运行 SQL 查询以演示空间函数。您的集群应拥有至少两个节点。要了解如何创建集群，请按照 [Amazon Redshift 入门指南](https://docs.aws.amazon.com/redshift/latest/gsg/)中的步骤操作。
+ 要使用 Amazon Redshift 查询编辑器，请确保您的集群位于支持查询编辑器的 AWS 区域。有关更多信息，请参阅《Amazon Redshift 管理指南》**中的 [使用查询编辑器查询数据库](https://docs.aws.amazon.com/redshift/latest/mgmt/query-editor.html)。
+ Amazon Redshift 集群的 AWS 凭证，以允许其从 Amazon S3 加载测试数据。有关如何访问其他 AWS 服务（如 Amazon S3）的信息，请参阅[授权 Amazon Redshift 访问 AWS 服务](https://docs.aws.amazon.com/redshift/latest/mgmt/authorizing-redshift-service.html)。
+ 名为 `mySpatialDemoRole` 的 AWS Identity and Access Management (IAM) 角色，它具有附加的托管策略 `AmazonS3ReadOnlyAccess`，用于读取 Amazon S3 数据。要创建具有从 Amazon S3 桶加载数据的权限的角色，请参阅《Amazon Redshift 管理指南》**中的[使用 IAM 角色授权 COPY、UNLOAD 和 CREATE EXTERNAL SCHEMA 操作](https://docs.aws.amazon.com/redshift/latest/mgmt/copy-unload-iam-role.html)。
+ 创建 IAM 角色 `mySpatialDemoRole` 后，该角色需要与您的 Amazon Redshift 集群关联。有关如何创建该关联的更多信息，请参阅《Amazon Redshift 管理指南》**中的[使用 IAM 角色授权 COPY、UNLOAD 和 CREATE EXTERNAL SCHEMA 操作](https://docs.aws.amazon.com/redshift/latest/mgmt/copy-unload-iam-role.html)。

## 步骤 1：创建表并加载测试数据
<a name="spatial-tutorial-test-data"></a>

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

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

您可以选择从以下 Amazon S3 链接下载源数据：
+ [`accommodations` 表的源数据](https://s3.amazonaws.com/redshift-downloads/spatial-data/accommodations.csv)。
+ [`zipcode` 表的源数据](https://s3.amazonaws.com/redshift-downloads/spatial-data/zipcode.csv)。

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

**要创建表并加载测试数据**

1. 打开 Amazon Redshift 查询编辑器。有关使用查询编辑器的更多信息，请参阅《Amazon Redshift 管理指南》**中的[使用查询编辑器查询数据库](https://docs.aws.amazon.com/redshift/latest/mgmt/query-editor.html)。

1. 删除本教程使用的任何表（如果它们已存在于数据库中）。有关更多信息，请参阅 [步骤 3：清理资源](#spatial-tutorial-clean-up)。

1. 创建 `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
   );
   ```

1. 在查询编辑器中创建 `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)
    );
   ```

1. 为这些表加载示例数据。

   本教程的示例数据位于为所有经过身份验证的 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';
   ```

1. 通过运行以下命令验证每个表都已正确加载。

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

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

   以下结果显示每个测试数据表的行数。    
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_cn/redshift/latest/dg/spatial-tutorial.html)

## 步骤 2：查询空间数据
<a name="spatial-tutorial-query"></a>

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

**要查询空间数据**

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

   ```
   SELECT count(*) FROM public.accommodations WHERE ST_SRID(shape) = 4326;
   ```

   ```
    count
   -------
    22248
   ```

1. 获取已知文本 (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)
   ```

1. 以 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
   ```

1. 运行以下 SQL 命令，查看距离勃兰登堡门 500 米范围内的住宿地数量。

   ```
   SELECT count(*) 
   FROM public.accommodations 
   WHERE ST_DistanceSphere(shape, ST_GeomFromText('POINT(13.377704 52.516431)', 4326)) < 500;
   ```

   ```
   count
   ------
     29
   ```

1. 通过运行以下查询，从存储在列为附近的住宿地数据中获取勃兰登堡门的粗略位置。

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

   ```
   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
   ```

1. 运行以下查询以显示勃兰登堡门周围所有住宿地的详细信息，按价格降序排列。

   ```
   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)
   ```

1. 运行以下查询以检索最昂贵的住宿地及其邮政编码。

   ```
   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,...
   ```

1. 使用子查询计算住宿的最高、最低或中位价格。

   以下查询按邮政编码列出住宿的中位价格。

   ```
   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)
   ```

1. 运行以下查询以检索柏林列出的住宿数量。要查找热门点，请按邮政编码对这些热点进行分组，并按供应量排序。

   ```
   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：清理资源
<a name="spatial-tutorial-clean-up"></a>

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

如果您希望保留集群，但恢复测试数据表使用的存储空间，请执行以下命令以删除表。

```
drop table public.accommodations cascade;
```

```
drop table public.zipcode cascade;
```