튜토리얼: 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 테이블에 저장된 총 목록 수를 구합니다. 공간 참조 시스템은 고유한 공간 참조 식별자 4326을 갖는 WGS(World Geodetic System) 84입니다.

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

 count
-------
 22248

몇 가지 추가 속성과 함께 WKT(Well-Known Text) 형식의 지오메트리 객체를 가져옵니다. 또한 이 우편 번호 데이터가 공간 참조 ID(SRID) 4326을 사용하는 WGS(World Geodetic System) 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 명령을 실행하여 브란덴부르크 문에서 500m 이내에 몇 개의 숙박 시설이 있는지 봅니다.

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)