Tutoriel : Utilisation de SQL fonctions spatiales avec Amazon Redshift

Ouvrez l’éditeur de requêtes Amazon Redshift. Pour plus d’informations sur l’utilisation de l’éditeur de requêtes, consultez Interrogation d’une base de données à l’aide de l’éditeur de requêtes dans le Guide de la gestion du cluster Amazon Redshift.

Supprimez toutes les tables utilisées par ce didacticiel si elles existent déjà dans votre base de données. Pour plus d'informations, consultez Étape 3 : Nettoyer vos ressources.

Créez la table accommodations pour stocker l’emplacement géographique de chaque hébergement (longitude et latitude), le nom de la liste et d’autres données métier.

Ce didacticiel parcourt les locations de chambres à Berlin, en Allemagne. La colonne shape stocke les points géographiques de l’emplacement des logements. Les autres colonnes contiennent des informations sur la location.

Pour créer la accommodations table, exécutez l'SQLinstruction suivante dans l'éditeur de requêtes 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
);

Créez la table zipcode dans l’éditeur de requêtes pour stocker les codes postaux de Berlin.

Un code postal est défini en tant que polygone dans la colonne wkb_geometry. Les autres colonnes décrivent les métadonnées spatiales supplémentaires sur le code postal.

Pour créer la zipcode table, exécutez l'SQLinstruction suivante dans l'éditeur de requêtes 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)
 );

Chargez les tables à l’aide des exemples de données.

Les exemples de données de ce didacticiel sont fournis dans un compartiment Amazon S3 qui permet un accès en lecture à tous les AWS utilisateurs authentifiés. Assurez-vous que vous fournissez les informations d’identification AWS valides qui permettent d’accéder à Amazon S3.

Pour charger les données de test dans vos tables, exécutez les COPY commandes suivantes. Remplacez numéro de compte avec votre propre numéro de AWS compte. Le segment de la chaîne d’informations d’identification entre guillemets simples ne peut pas comporter d’espaces ou de sauts de ligne.

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

Vérifiez que chaque table s’est chargée correctement en exécutant les commandes suivantes.

select count(*) from accommodations;

select count(*) from zipcode;

Les résultats suivants indiquent le nombre de lignes dans chaque tableau de données de test.

Interrogez les données pour obtenir le nombre total d’éléments stockés dans la table accommodations, comme illustré ci-dessous. Le système de référence spatiale est le World Geodetic System (WGS) 84, qui possède l'identifiant de référence spatiale unique 4326.

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

 count
-------
 22248

Récupérez les objets de géométrie au format text (WKT) connu avec quelques attributs supplémentaires. En outre, vous pouvez vérifier si ces données de code postal sont également stockées dans le World Geodetic System (WGS) 84, qui utilise l'ID de référence spatiale (SRID) 4326. Les données spatiales doivent être stockées dans le même système de référence spatiale pour être interopérables.

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)

Sélectionnez le polygone de Berlin Mitte (10117), un arrondissement de Berlin, au JSON format géographique, sa dimension et le nombre de points de ce polygone.

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

Exécutez la SQL commande suivante pour voir combien d'hébergements se trouvent à moins de 500 mètres de la porte de Brandebourg.

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

count
------
  29

Obtenez l’emplacement approximatif de la porte de Brandebourg à partir des données stockées dans les hébergements répertoriés comme étant à proximité en exécutant la requête suivante.

Cette requête nécessite une sous-sélection. Le nombre obtenu n’est pas le même parce que l’emplacement interrogé diffère de la requête précédente, car il est plus proche des hébergements.

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

Exécutez la requête suivante pour afficher les détails de tous les hébergements autour de la porte de Brandebourg, classés par prix dans l’ordre décroissant.

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)

Exécutez la requête suivante pour récupérer l’hébergement le plus cher avec son code postal.

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

Calculez le prix maximum, minimum ou médian des hébergements à l’aide d’une sous-requête.

La requête suivante répertorie le prix médian des logements par code postal.

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)

Exécutez la requête suivante pour récupérer le nombre d’hébergements répertoriés à Berlin. Les points chauds sont regroupés par code postal et triés par quantité d’informations fournies.

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)