O Amazon Redshift não permitirá mais a criação de UDFs do Python a partir do Patch 198. As UDFs do Python existentes continuarão a funcionar normalmente até 30 de junho de 2026. Para ter mais informações, consulte a [publicação de blog ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/). # Tutorial: Uso de funções SQL espaciais com Amazon Redshift Este tutorial demonstra como usar algumas das funções SQL espaciais com o Amazon Redshift. Para fazer isso, você consulta duas tabelas usando funções SQL espaciais. O tutorial usa dados de conjuntos de dados públicos que correlacionam dados de localização de acomodações alugadas com códigos postais em Berlim, Alemanha. **Topics** + [Pré-requisitos](#spatial-tutorial-prerequisites) + [Etapa 1: Criar tabelas e carregar dados de teste](#spatial-tutorial-test-data) + [Etapa 2: Consultar dados espaciais](#spatial-tutorial-query) + [Etapa 3: Limpar os recursos](#spatial-tutorial-clean-up) ## Pré-requisitos Para este tutorial, você precisa dos seguintes recursos: + Um cluster e banco de dados existentes do Amazon Redshift que você pode acessar e atualizar. No cluster existente, você cria tabelas, carrega dados de amostra e executa consultas SQL para demonstrar funções espaciais. O cluster deve ter pelo menos dois nós. Para saber como criar um cluster, siga as etapas no [Guia de conceitos básicos do Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/gsg/). + Para usar o editor de consultas do Amazon Redshift, certifique-se de que seu cluster esteja em uma região da AWS que oferece suporte ao editor de consulta. Para obter mais informações, confira [Consultar um banco de dados usando o editor de consultas](https://docs.aws.amazon.com/redshift/latest/mgmt/query-editor.html) no *Guia de gerenciamento do Amazon Redshift*. + AWSCredenciais da para seu cluster do Amazon Redshift que permitem carregar dados de teste do Amazon S3. Para obter informações sobre como acessar outros serviços da AWS, como o Amazon S3, consulte [Autorizar o Amazon Redshift a acessar serviços da AWS](https://docs.aws.amazon.com/redshift/latest/mgmt/authorizing-redshift-service.html). + A função do AWS Identity and Access Management (IAM) chamada `mySpatialDemoRole`, que tem a política gerenciada `AmazonS3ReadOnlyAccess`anexada para ler dados do Amazon S3. Para criar um perfil com permissões para carregar dados de um bucket do Amazon S3, consulte “[Autorizar operações COPY, UNLOAD, CREATE EXTERNAL FUNCTION e CREATE EXTERNAL SCHEMA usando funções do IAM](https://docs.aws.amazon.com/redshift/latest/mgmt/copy-unload-iam-role.html)” no *Guia de gerenciamento de clusters do Amazon Redshift*. + Após criar a função do IAM `mySpatialDemoRole`, essa função precisa de uma associação com seu cluster do Amazon Redshift. Para obter mais informações sobre como criar essa associação, consulte “[Autorizar operações COPY, UNLOAD, CREATE EXTERNAL FUNCTION e CREATE EXTERNAL SCHEMA usando funções do IAM](https://docs.aws.amazon.com/redshift/latest/mgmt/copy-unload-iam-role.html)” no *Guia de gerenciamento de clusters do Amazon Redshift*. ## Etapa 1: Criar tabelas e carregar dados de teste Os dados de origem usados por este tutorial estão em arquivos chamados `accommodations.csv` e `zipcodes.csv`. O arquivo `accommodations.csv` são dados de código aberto de insideairbnb.com. O arquivo `zipcodes.csv` fornece códigos postais que são dados de código aberto do instituto nacional de estatística de Berlim-Brandemburgo, na Alemanha (Amt für Statistik Berlim-Brandenburg). Ambas as fontes de dados são fornecidas sob uma licença Creative Commons. Os dados estão limitados à região de Berlim, Alemanha. Esses arquivos estão localizados em um bucket público do Amazon S3 para serem usados com este tutorial. Opcionalmente, você pode baixar os dados de fonte dos seguintes links do Amazon S3: + [ Dados de origem para a tabela `accommodations`](https://s3.amazonaws.com/redshift-downloads/spatial-data/accommodations.csv). + [ Dados de origem para a tabela `zipcode`](https://s3.amazonaws.com/redshift-downloads/spatial-data/zipcode.csv). Use o procedimento a seguir para criar tabelas e carregar dados de teste. **Para criar tabelas e carregar dados de teste** 1. Abra o editor de consulta do Amazon Redshift. Para obter mais informações sobre como trabalhar com o editor de consultas, consulte “[Consultar um banco de dados usando o Query Editor](https://docs.aws.amazon.com/redshift/latest/mgmt/query-editor.html)” no *Guia de gerenciamento de clusters do Amazon Redshift*. 1. Descarte todas as tabelas usadas por este tutorial se elas já existirem em seu banco de dados. Para obter mais informações, consulte [Etapa 3: Limpar os recursos](#spatial-tutorial-clean-up). 1. Crie a tabela `accommodations` para armazenar a localização geográfica de cada acomodação (longitude e latitude), o nome da listagem e outros dados comerciais. Este tutorial explora o aluguel de quartos em Berlim, Alemanha. A coluna `shape` armazena pontos geográficos da localização das acomodações. As outras colunas contêm informações sobre o aluguel. Para criar a tabela `accommodations`, execute a instrução SQL a seguir no editor de consulta do 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. Crie a tabela `zipcode` no editor de consulta para armazenar os códigos postais de Berlim. O *código postal* é definido como um polígono na coluna `wkb_geometry`. O resto das colunas descrevem metadados espaciais adicionais sobre o código postal. Para criar a tabela `zipcode`, execute a instrução SQL a seguir no editor de consulta do 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. Carregue as tabelas usando dados de amostra. Os dados de amostra para este tutorial são fornecidos em um bucket do Amazon S3 que permite acesso de leitura a todos os usuários da AWS autenticados. Certifique-se de fornecer credenciais da AWS válidas que permitem o acesso ao Amazon S3. Para carregar dados de teste para suas tabelas, execute os comandos COPY a seguir. Substitua *`account-number`* pelo número da sua conta da AWS. O segmento da string de credenciais entre aspas simples não pode conter espaços ou quebras de linha. ``` 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. Verifique se cada tabela foi carregada corretamente executando os comandos a seguir. ``` select count(*) from accommodations; ``` ``` select count(*) from zipcode; ``` Os resultados a seguir mostram o número de linhas em cada tabela de dados de teste. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/pt_br/redshift/latest/dg/spatial-tutorial.html) ## Etapa 2: Consultar dados espaciais Depois que suas tabelas são criadas e carregadas, você pode consultá-las usando instruções SQL SELECT. As consultas a seguir demonstram algumas das informações que você pode recuperar. Você pode escrever muitas outras consultas que usam funções espaciais para satisfazer suas necessidades. **Para consultar dados espaciais** 1. Consulte para obter a contagem do número total de listagens armazenadas na tabela `accommodations`, conforme mostrado a seguir. O sistema de referência espacial é o Sistema Geodésico Mundial (WGS - World Geodetic System) 84, que possui o identificador de referência espacial único 4326. ``` SELECT count(*) FROM public.accommodations WHERE ST_SRID(shape) = 4326; ``` ``` count ------- 22248 ``` 1. Obtém os objetos de geometria em formato de texto bem-conhecido (WKT - Well-Known Text) com alguns atributos adicionais. Além disso, você pode validar se esses dados de código postal também são armazenados no Sistema Geodésico Mundial (WGS) 84, que usa o ID de referência espacial (SRID) 4326. Os dados geográficos devem ser armazenados no mesmo sistema de referência espacial para serem interoperáveis. ``` 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. Selecione o polígono de Berlin Mitte (10117), um bairro de Berlim, no formato GeoJSON, sua dimensão e o número de pontos neste polígono. ``` 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. Execute o seguinte comando SQL para ver quantas acomodações estão dentro de 500 metros do Portão de Brandemburgo. ``` SELECT count(*) FROM public.accommodations WHERE ST_DistanceSphere(shape, ST_GeomFromText('POINT(13.377704 52.516431)', 4326)) < 500; ``` ``` count ------ 29 ``` 1. Obtenha a localização aproximada do Portão de Brandemburgo a partir de dados armazenados nas acomodações que estão listadas como próximas, executando a seguinte consulta. Esta consulta requer uma subseleção. Isso leva a uma contagem diferente porque o local solicitado não é o mesmo que a consulta anterior porque está mais perto das acomodações. ``` 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. Execute a seguinte consulta para mostrar os detalhes de todas as acomodações ao redor do Portão de Brandemburgo, ordenadas por preço em ordem 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. Execute a consulta a seguir para recuperar a acomodação mais cara com o seu código 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,... ``` 1. Calcule o preço máximo, mínimo ou mediano das acomodações usando uma subconsulta. A consulta a seguir lista o preço mediano das acomodações por código 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) ``` 1. Execute a consulta a seguir para recuperar o número de acomodações listadas em Berlim. Para encontrar os hot spots, estes são agrupados por código postal e classificados pela quantidade de fornecimento. ``` 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) ``` ## Etapa 3: Limpar os recursos O cluster continua a acumular cobranças enquanto estiver em execução. Ao concluir este tutorial, você pode excluir seu cluster de amostra. Se você deseja manter o cluster, mas recuperar o armazenamento usado pelas tabelas de dados de teste, execute os comandos a seguir para excluir as tabelas. ``` drop table public.accommodations cascade; ``` ``` drop table public.zipcode cascade; ```