Amazon Redshift 將不再支援從修補程式 198 開始建立新的 Python UDFs。現有 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>

本教學課程示範如何將部分空間 SQL 函數與 Amazon Redshift 搭配使用。

若要這麼做，您可以使用空間 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)。
+ AWS Amazon Redshift 叢集的 憑證，允許它從 Amazon S3 載入測試資料。如需如何存取 Amazon S3 等其他 AWS 服務的資訊，請參閱[授權 Amazon Redshift 存取 AWS 服務](https://docs.aws.amazon.com/redshift/latest/mgmt/authorizing-redshift-service.html)。
+ 名為 的 AWS Identity and Access Management (IAM) 角色`mySpatialDemoRole`，其具有`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) 的開放原始碼資料。兩個資料來源皆經過創用 CC (Creative Commons) 授權。該資料僅限於德國柏林地區。這些檔案位於 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. 使用範例資料來載入資料表。

   本教學課程的範例資料是在 Amazon S3 儲存貯體中提供，允許所有已驗證 AWS 使用者的讀取存取權。請確定您已提供允許存取 Amazon S3 的有效 AWS 登入資料。

   若要將測試資料載入資料表，請執行下列 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_tw/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. 選取柏林行政區-柏林米特區 (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
   ```

1. 執行以下 SQL 命令以檢視布蘭登堡門周圍 500 公尺內有多少住所。

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

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

1. 透過執行以下查詢，從附近所列出住所中儲存的資料取得布蘭登堡門的粗略位置。

   此查詢需要子選取 (subselect)。這會產生不同的計數，因為要求的位置與以前的查詢不一樣，這更接近住所。

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