必要條件步驟 1：建立資料表並載入測試資料步驟 2：查詢空間資料步驟 3：清除您的資源

教學課程：將空間 SQL 函數與 Amazon Redshift 搭配使用

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

若要這麼做，您可以使用空間 SQL 函數來查詢兩個資料表。該教學課程會使用公共資料集中的資料，該資料將德國柏林的出租住所位置資料與郵遞區號建立關聯。

主題

必要條件
步驟 1：建立資料表並載入測試資料
步驟 2：查詢空間資料
步驟 3：清除您的資源

必要條件

在此教學課程中，您需執行下列資源：

您可以存取和更新的現有 Amazon Redshift 叢集和資料庫。在現有叢集中，您可以建立資料表、載入範例資料，以及執行 SQL 查詢以示範空間函數。您的叢集至少要有兩個節點。若要了解如何建立叢集，請按照 Amazon Redshift 入門指南中的步驟進行操作。
若要使用 Amazon Redshift 查詢編輯器，請確定您的叢集位於支援查詢編輯器的 AWS 區域中。如需詳細資訊，請參閱《Amazon Redshift 管理指南》中的使用查詢編輯器來查詢資料庫。
AWS Amazon Redshift 叢集的憑證，允許它從 Amazon S3 載入測試資料。如需如何存取 Amazon S3 AWS 等其他服務的詳細資訊，請參閱授權 Amazon Redshift 存取 AWS 服務。
名為的 AWS Identity and Access Management (IAM) 角色mySpatialDemoRole，其具有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) 的開放原始碼資料。兩個資料來源皆經過創用 CC (Creative Commons) 授權。該資料僅限於德國柏林地區。這些檔案位於 Amazon S3 公用儲存貯體中，可與本教學課程搭配使用。

您可以選擇性地從下列 Amazon S3 連結下載來源資料：

使用下列程序來建立資料集並載入測試資料。

建立資料表並載入測試資料

開啟 Amazon Redshift 查詢編輯器。如需使用查詢編輯器的相關資訊，請參閱《Amazon Redshift 管理指南》中的使用查詢編輯器來查詢資料庫。
刪除本教學課程使用的任何資料表 (如果這些資料表已經存在於您的資料庫中)。如需詳細資訊，請參閱步驟 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
);

在查詢編輯器中建立 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)
 );
```
使用範例資料來載入資料表。

本教學課程的範例資料提供於 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';
```

執行下列命令，可驗證每一個資料表是否正確載入。


select count(*) from accommodations;


select count(*) from zipcode;

下列結果會顯示每個測試資料資料表的列數。

資料表名稱	資料列
住所	22，248
zipcode	190

步驟 2：查詢空間資料

建立並載入資料表之後，您可以使用 SQL SELECT 陳述式來查詢這些資料表。下列查詢示範的是一些您可以擷取的資訊。您可以編寫許多其他使用空間函數來滿足您需求的查詢。

查詢空間資料

查詢以取得 accommodations 資料表中所儲存清單項目的總數，如下所示。空間參考系統是世界大地測量系統 (WGS) 84，具有唯一的空間參考識別碼 4326。
```
SELECT count(*) FROM public.accommodations WHERE ST_SRID(shape) = 4326;
```
```
 count
-------
 22248
```

擷取格式為熟知文字 (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)

選取柏林行政區-柏林米特區 (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

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


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



count
------
  29

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

此查詢需要子選取 (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
```

執行以下查詢以顯示布蘭登堡門周圍所有住所的詳細資訊 (按價格遞減排序)。


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)

執行以下查詢，透過郵遞區號擷取最昂貴的住所。


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

透過使用子查詢來計算出最高，最低或中等的住所價格。

下列查詢依郵遞區號列出了中等的住所價格。


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)

執行以下查詢以擷取柏林中列出的住所數量。為了找到熱點，這些住所會按郵遞區號進行分組，並按供應量排序。


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;

您的瀏覽器已停用或無法使用 Javascript。

您必須啟用 Javascript，才能使用 AWS 文件。請參閱您的瀏覽器說明頁以取得說明。

文件慣用形式

查詢空間資料

載入 Shapefile