Amazon Redshift non supporterà più la creazione di nuovi Python UDFs a partire dalla Patch 198. Python esistente UDFs continuerà a funzionare fino al 30 giugno 2026. Per ulteriori informazioni, consulta il [post del blog](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/). Le traduzioni sono generate tramite traduzione automatica. In caso di conflitto tra il contenuto di una traduzione e la versione originale in Inglese, quest'ultima prevarrà. # Tutorial: Utilizzo delle funzioni SQL spaziali con Amazon Redshift Questo tutorial mostra come utilizzare alcune delle funzioni SQL spaziali con Amazon Redshift. A tale scopo, viene eseguita una query su due tabelle utilizzando le funzioni SQL spaziali. Il tutorial utilizza i dati provenienti da set di dati pubblici che correlano i dati sulla posizione degli alloggi in affitto con i codici postali a Berlino, Germania. **Topics** + [Prerequisiti](#spatial-tutorial-prerequisites) + [Fase 1: Creazione di tabelle e caricamento dei dati di test](#spatial-tutorial-test-data) + [Fase 2: Query su dati spaziali](#spatial-tutorial-query) + [Fase 3: eliminazione delle risorse](#spatial-tutorial-clean-up) ## Prerequisiti Per questo tutorial, sono necessarie le seguenti risorse: + Un cluster e un database Amazon Redshift esistenti a cui è possibile accedere e aggiornare. Nel cluster esistente è possibile creare tabelle, caricare dati di esempio ed eseguire query SQL per dimostrare le funzioni spaziali. Il cluster deve avere almeno due nodi. Per informazioni su come creare un cluster, seguire la procedura riportata in [Guida alle operazioni di base di Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/gsg/). + Per utilizzare l'editor di query Amazon Redshift, assicurarsi che il cluster si trovi in una regione AWS che supporta l'editor di query. Per ulteriori informazioni, consulta [Esecuzione di query su un database con l'editor di query](https://docs.aws.amazon.com/redshift/latest/mgmt/query-editor.html) nella *Guida alla gestione di Amazon Redshift*. + AWS credenziali per il tuo cluster Amazon Redshift che gli consentono di caricare i dati di test da Amazon S3. Per informazioni su come accedere ad altri AWS servizi come Amazon S3, consulta [Autorizzazione di Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/mgmt/authorizing-redshift-service.html) all'accesso ai servizi. AWS + Il nome del ruolo AWS Identity and Access Management (IAM)`mySpatialDemoRole`, a cui è `AmazonS3ReadOnlyAccess` allegata la policy gestita per leggere i dati di Amazon S3. Per creare un ruolo con l'autorizzazione per caricare i dati da un bucket Amazon S3, consulta [Autorizzazione delle operazioni COPY, UNLOAD e CREATE EXTERNAL SCHEMA mediante ruoli IAM](https://docs.aws.amazon.com/redshift/latest/mgmt/copy-unload-iam-role.html) nella *Guida alla gestione di Amazon Redshift*. + Dopo aver creato il ruolo IAM `mySpatialDemoRole`, tale ruolo richiede un'associazione con il cluster Amazon Redshift. Per ulteriori informazioni su come creare tale associazione, consulta [Autorizzazione delle operazioni COPY, UNLOAD e CREATE EXTERNAL SCHEMA mediante ruoli IAM](https://docs.aws.amazon.com/redshift/latest/mgmt/copy-unload-iam-role.html) nella *Guida alla gestione di Amazon Redshift*. ## Fase 1: Creazione di tabelle e caricamento dei dati di test I dati di origine utilizzati in questo tutorial sono contenuti in file denominati `accommodations.csv` e `zipcodes.csv`. Il file `accommodations.csv` è costituito da dati open source provenienti da insideairbnb.com. Il file `zipcodes.csv` fornisce codici postali che sono dati open source dell'istituto nazionale di statistica di Berlino-Brandeburgo in Germania (Amt für Statistik Berlino-Brandeburgo). Entrambe le origini dati sono fornite con una licenza Creative Commons. I dati sono limitati alla regione di Berlino, Germania. Questi file si trovano in un bucket pubblico Amazon S3 da utilizzare con questo tutorial. Facoltativamente, è possibile scaricare i dati di origine dai seguenti link Amazon S3: + [Dati di origine per la tabella `accommodations`](https://s3.amazonaws.com/redshift-downloads/spatial-data/accommodations.csv). + [Dati di origine per la tabella `zipcode`](https://s3.amazonaws.com/redshift-downloads/spatial-data/zipcode.csv). Utilizzare la procedura seguente per creare tabelle e caricare i dati di test. **Come creare tabelle e caricare dati di test** 1. Aprire l'editor di query Amazon Redshift. Per ulteriori informazioni sull'utilizzo dell'editor di query, consulta [Esecuzione di query su un database con l'editor della query](https://docs.aws.amazon.com/redshift/latest/mgmt/query-editor.html) nella *Guida alla gestione di Amazon Redshift*. 1. Eliminare le tabelle utilizzate da questo tutorial se sono già presenti nel database. Per ulteriori informazioni, consulta [Fase 3: eliminazione delle risorse](#spatial-tutorial-clean-up). 1. Creare la tabella `accommodations` per memorizzare la posizione geografica di ogni alloggio (longitudine e latitudine), il nome dell'inserzione e altri dati aziendali. Questo tutorial esplora gli affitti di camere a Berlino, in Germania. La colonna `shape` riporta i punti geografici della posizione degli alloggi. Le altre colonne contengono informazioni sull'affitto. Per creare la tabella `accommodations`, eseguire la seguente istruzione SQL nell'editor di query 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 ); ``` 1. Creare la tabella `zipcode` nell'editor di query per memorizzare i codici postali di Berlino. Un *codice postale (o CAP)* è definito come un poligono nella colonna `wkb_geometry`. Le altre colonne descrivono metadati spaziali aggiuntivi relativi al codice postale. Per creare la tabella `zipcode`, eseguire la seguente istruzione SQL nell'editor di query 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) ); ``` 1. Caricare le tabelle mediante i dati di esempio. I dati di esempio per questo tutorial sono forniti in un bucket Amazon S3 che consente l'accesso in lettura a tutti gli utenti autenticati. AWS Assicurarsi di fornire credenziali AWS valide che consentano di accedere ad Amazon S3. Per caricare i dati di test nelle tabelle, emettere i seguenti comandi COPY. Sostituire *`account-number`* con il numero di account AWS . Il segmento della stringa delle credenziali racchiuso tra virgolette singole non deve contenere spazi o interruzioni di riga. ``` 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'; ``` 1. Verificare che ogni tabella sia caricata correttamente emettendo i comandi seguenti. ``` select count(*) from accommodations; ``` ``` select count(*) from zipcode; ``` I risultati seguenti mostrano il numero di righe di ogni tabella di dati di test. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/it_it/redshift/latest/dg/spatial-tutorial.html) ## Fase 2: Query su dati spaziali Dopo aver creato e caricato le tabelle, è possibile eseguire le query utilizzando le istruzioni SQL SELECT. Le query seguenti illustrano alcune delle informazioni che è possibile recuperare. È possibile scrivere molte altre query che utilizzano funzioni spaziali per soddisfare le proprie esigenze. **Come eseguire query su dati spaziali** 1. Eseguire le query per ottenere il conteggio del numero totale di risultati memorizzati nella tabella `accommodations`, come illustrato di seguito. Il sistema di riferimento territoriale è World Geodetic System (WGS) 84, che ha l'identificatore univoco di riferimento territoriale 4326. ``` SELECT count(*) FROM public.accommodations WHERE ST_SRID(shape) = 4326; ``` ``` count ------- 22248 ``` 1. Recuperare gli oggetti geometrici in formato WKT (well-known text) con alcuni attributi aggiuntivi. Inoltre, è possibile verificare se questi dati del codice postale sono memorizzati anche nel World Geodetic System (WGS) 84, che utilizza l'ID di riferimento spaziale (SRID) 4326. Perché possano essere utilizzati in maniera incrociata, i dati spaziali devono essere conservati nello stesso sistema di riferimento territoriale. ``` 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) ``` 1. Selezionare il poligono di Berlin Mitte (10117), un quartiere di Berlino, in formato GeoJSON, la sua dimensione e il numero di punti in questo poligono. ``` 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 ``` 1. Emettere il seguente comando SQL per visualizzare quante strutture ricettive si trovano nel raggio di 500 metri dalla Porta di Brandeburgo. ``` SELECT count(*) FROM public.accommodations WHERE ST_DistanceSphere(shape, ST_GeomFromText('POINT(13.377704 52.516431)', 4326)) < 500; ``` ``` count ------ 29 ``` 1. Ottenere la posizione approssimativa della Porta di Brandeburgo dai dati archiviati nelle strutture elencate come nelle vicinanze eseguendo la seguente query. Questa query richiede una sottoselezione. Porta a un conteggio diverso perché la posizione richiesta non è la stessa della query precedente perché è più vicina agli alloggi. ``` 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 ``` 1. Eseguire la seguente query per mostrare i dettagli di tutti gli alloggi intorno alla Porta di Brandeburgo, ordinati per prezzo in ordine decrescente. ``` 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) ``` 1. Eseguire la seguente query per recuperare la sistemazione più costosa con il suo codice postale. ``` 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,... ``` 1. Calcolare il prezzo massimo, minimo o medio degli alloggi utilizzando una sottoquery. La query seguente elenca il prezzo medio degli alloggi in base al codice postale. ``` 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) ``` 1. Eseguire la seguente query per recuperare il numero di alloggi elencati a Berlino. Per trovare gli hot spot, questi sono raggruppati per codice postale e ordinati in base alla quantità di fornitura. ``` 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) ``` ## Fase 3: eliminazione delle risorse Il cluster genera dei costi fino a che è in esecuzione. Una volta completato questo tutorial, sarà possibile eliminare il cluster di esempio. Se desideri conservare il cluster, ma recuperare l'archiviazione utilizzata dalle tabelle di dati di test, emettere i comandi seguenti per eliminare le tabelle. ``` drop table public.accommodations cascade; ``` ``` drop table public.zipcode cascade; ```