チュートリアル: 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)
 );

サンプルデータをテーブルにロードします。

このチュートリアルのサンプルデータは、認証済みのすべての AWS ユーザーに読み取りアクセスを許可する、Amazon S3 バケット内に用意されています。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;

以下の結果の表に示しているのは、テストテーブルの各テーブルでの行数です。

次のように、クエリを実行して、accommodations テーブルに保存されているリストの総数を取得します。空間参照系は世界測地系 (WGS) 84 であり、一意の空間参照識別子 4326 を持ちます。

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

 count
-------
 22248

いくつかの追加属性を使用して、ジオメトリオブジェクトを well-known text (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

次のクエリを実行して、近いと表示されている宿泊施設に保存されているデータから、ブランデンブルク門の大まかな位置を取得します。

このクエリには、複数の選択が必要です。リクエストされた場所が宿泊施設に近いため、以前のクエリと同じではないため、カウントが異なります。

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)