Tutorial: Räumliche SQL Funktionen mit Amazon Redshift verwenden

Öffnen Sie den Abfrage-Editor von Amazon Redshift. Weitere Informationen zur Arbeit mit dem Abfrage-Editor finden Sie unter Querying a database using the query editor (Abfragen für Datenbanken mit dem Abfrage-Editor) im Amazon-Redshift-Verwaltungshandbuch.

Löschen Sie alle Tabellen, die in diesem Tutorial verwendet werden, wenn sie bereits in Ihrer Datenbank vorhanden sind. Weitere Informationen finden Sie unter Schritt 3: Bereinigen Ihrer Ressourcen.

Erstellen der accommodations-Tabelle, um die geografische Lage der einzelnen Unterkünfte (Längen- und Breitengrad), den Namen der Auflistung und andere Geschäftsdaten zu speichern.

Dieses Tutorial erforscht Zimmervermietungen in Berlin, Deutschland. Die Spalte shape speichert geografische Punkte des Standortes von Unterkünften. Die anderen Spalten enthalten Informationen über die Vermietung.

Um die accommodations Tabelle zu erstellen, führen Sie die folgende SQL Anweisung im Amazon Redshift Redshift-Abfrage-Editor aus.

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

Erstellen Sie die Tabelle zipcode im Abfrage-Editor, um Postleitzahlen von Berlin zu speichern.

Eine Postleitzahl wird in der Spalte wkb_geometry als Polygon definiert. In den restlichen Spalten werden zusätzliche Geo-Metadaten zur Postleitzahl beschrieben.

Um die zipcode Tabelle zu erstellen, führen Sie die folgende SQL Anweisung im Amazon Redshift Redshift-Abfrage-Editor aus.

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

Laden Sie die Tabellen mit Beispieldaten.

Die Beispieldaten für dieses Tutorial werden in einem Amazon S3 S3-Bucket bereitgestellt, der allen authentifizierten AWS Benutzern Lesezugriff ermöglicht. Stellen Sie sicher, dass Sie gültige AWS -Anmeldeinformationen angeben, die den Zugriff auf Amazon S3 gewähren.

Führen Sie die folgenden COPY Befehle aus, um Testdaten in Ihre Tabellen zu laden. Ersetzen Kontonummer mit deiner eigenen AWS Kontonummer. Das Segment der Anmeldedatenzeichenfolge, das in einfachen Anführungszeichen eingeschlossen ist, darf keine Leerzeichen oder Zeilenumbrüche enthalten.

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

Um zu prüfen, ob jede Tabelle korrekt geladen wurde, führen Sie die folgenden Befehle aus.

select count(*) from accommodations;

select count(*) from zipcode;

Die folgende Ergebnistabelle zeigt die Anzahl der Zeilen in jeder Tabelle mit Testdaten.

Abfrage, um die Gesamtzahl der Angebote zu erhalten, die in der Tabelle accommodations (siehe unten) gespeichert werden. Das räumliche Bezugssystem ist World Geodetic System (WGS) 84, das über die eindeutige Raumbezugskennung 4326 verfügt.

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

 count
-------
 22248

Ruft die Geometrieobjekte im bekannten Textformat (WKT) mit einigen zusätzlichen Attributen ab. Darüber hinaus können Sie überprüfen, ob diese Postleitzahldaten auch im World Geodetic System (WGS) 84 gespeichert sind, das die Raumbezugs-ID (SRID) 4326 verwendet. Geodaten müssen im selben Georeferenzsystem gespeichert werden, um interoperabel zu sein.

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)

Wählen Sie das Polygon von Berlin Mitte (10117), einem Bezirk von Berlin, im JSON Geoformat, seine Dimension und die Anzahl der Punkte in diesem Polygon aus.

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

Führen Sie den folgenden SQL Befehl aus, um zu sehen, wie viele Unterkünfte sich im Umkreis von 500 Metern vom Brandenburger Tor befinden.

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

count
------
  29

Holen Sie sich den ungefähren Standort des Brandenburger Tores aus Daten, die mit den Unterkünften gespeichert sind, die als in der Nähe aufgeführt sind, indem Sie die folgende Abfrage ausführen.

Diese Abfrage erfordert eine untergeordnete Auswahl. Es führt zu einer anderen Zählung, da der angeforderte Standort nicht mit der vorherigen Abfrage identisch ist, da er näher an den Unterkünften liegt.

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

Führen Sie die folgende Abfrage aus, um die Details aller Unterkünfte rund um das Brandenburger Tor anzuzeigen, sortiert nach Preis in absteigender Reihenfolge.

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)

Führen Sie die folgende Abfrage aus, um die teuerste Unterkunft mit ihrer Postleitzahl abzurufen.

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

Berechnen Sie den maximalen, minimalen oder mittleren Preis von Unterkünften mithilfe einer untergeordneten Abfrage.

Die folgende Abfrage führt den mittleren Preis der Unterkünfte nach Postleitzahl auf.

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)

Führen Sie die folgende Abfrage aus, um die Anzahl der in Berlin aufgeführten Unterkünfte abzurufen. Um die Hotspots zu finden, werden diese nach Postleitzahl gruppiert und nach der Menge des Angebots sortiert.

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)