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

開啟 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命令。Replace (取代) 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;

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

查詢以取得 accommodations 資料表中所儲存清單項目的總數，如下所示。空間參考系統為 World Geodetic System （WGS） 84，其具有唯一的空間參考識別碼 4326。

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

 count
-------
 22248

使用一些其他屬性，以眾所周知的文字 （WKT） 格式擷取幾何物件。此外，您可以驗證此郵遞區號資料是否也儲存在 World Geodetic System （WGS） 84 中，該系統使用空間參考 ID （SRID） 4326。空間資料必須儲存在相同的空間參考系統中才能互通。

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)

選取柏林 Mitte （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)