Tutorial: Menggunakan fungsi SQL spasial dengan Amazon Redshift

Buka editor kueri Amazon Redshift. Untuk informasi selengkapnya tentang bekerja dengan editor kueri, lihat Menanyakan database menggunakan editor kueri di Panduan Manajemen Pergeseran Merah Amazon.

Jatuhkan tabel apa pun yang digunakan oleh tutorial ini jika sudah ada di database Anda. Untuk informasi selengkapnya, lihat Langkah 3: Bersihkan sumber daya Anda.

Buat accommodations tabel untuk menyimpan lokasi geografis setiap akomodasi (bujur dan lintang), nama daftar, dan data bisnis lainnya.

Tutorial ini mengeksplorasi penyewaan kamar di Berlin, Jerman. shapeKolom menyimpan titik-titik geografis dari lokasi akomodasi. Kolom lainnya berisi informasi tentang sewa.

Untuk membuat accommodations tabel, jalankan pernyataan SQL berikut di editor kueri Amazon Redshift.

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

Buat zipcode tabel di editor kueri untuk menyimpan kode pos Berlin.

Kode pos didefinisikan sebagai poligon di kolom. wkb_geometry Sisa kolom menjelaskan metadata spasial tambahan tentang kode pos.

Untuk membuat zipcode tabel, jalankan pernyataan SQL berikut di editor kueri Amazon Redshift.

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

Muat tabel menggunakan data sampel.

Data sampel untuk tutorial ini disediakan dalam bucket Amazon S3 yang memungkinkan akses baca ke semua pengguna yang diautentikasi AWS . Pastikan Anda memberikan AWS kredensi valid yang mengizinkan akses ke Amazon S3.

Untuk memuat data uji ke tabel Anda, jalankan perintah COPY berikut. Ganti account-number dengan nomor AWS akun Anda sendiri. Segmen string kredensial yang diapit tanda kutip tunggal tidak dapat berisi spasi atau jeda baris apa pun.

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

Verifikasi bahwa setiap tabel dimuat dengan benar dengan menjalankan perintah berikut.

select count(*) from accommodations;

select count(*) from zipcode;

Hasil berikut menunjukkan jumlah baris di setiap tabel data uji.

Query untuk mendapatkan hitungan jumlah total listing yang disimpan dalam accommodations tabel, seperti yang ditunjukkan berikut. Sistem referensi spasial adalah World Geodetic System (WGS) 84, yang memiliki pengenal referensi spasial unik 4326.

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

 count
-------
 22248

Ambil objek geometri dalam format teks terkenal (WKT) dengan beberapa atribut tambahan. Selain itu, Anda dapat memvalidasi jika data kode pos ini juga disimpan di World Geodetic System (WGS) 84, yang menggunakan ID referensi spasial (SRID) 4326. Data spasial harus disimpan dalam sistem referensi spasial yang sama agar dapat dioperasikan.

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)

Pilih poligon Berlin Mitte (10117), sebuah wilayah Berlin, dalam format GeoJSON, dimensinya, dan jumlah titik dalam poligon ini.

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

Jalankan perintah SQL berikut untuk melihat berapa banyak akomodasi yang berada dalam jarak 500 meter dari Gerbang Brandenburg.

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

count
------
  29

Dapatkan lokasi kasar Gerbang Brandenburg dari data yang disimpan di akomodasi yang terdaftar di dekatnya dengan menjalankan kueri berikut.

Query ini membutuhkan subselect. Ini mengarah ke hitungan yang berbeda karena lokasi yang diminta tidak sama dengan kueri sebelumnya karena lebih dekat ke akomodasi.

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

Jalankan kueri berikut untuk menunjukkan detail semua akomodasi di sekitar Gerbang Brandenburg, dipesan berdasarkan harga dalam urutan menurun.

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)

Jalankan kueri berikut untuk mengambil akomodasi paling mahal dengan kode posnya.

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

Hitung harga maksimum, minimum, atau median akomodasi dengan menggunakan subquery.

Kueri berikut mencantumkan harga rata-rata akomodasi berdasarkan kode pos.

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)

Jalankan kueri berikut untuk mengambil jumlah akomodasi yang tercantum di Berlin. Untuk menemukan hot spot, ini dikelompokkan berdasarkan kode pos dan diurutkan berdasarkan jumlah pasokan.

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)