Tutorial: Uso de funciones SQL espaciales con Amazon Redshift

Abra el editor de consultas de Amazon Redshift. Para obtener más información acerca de cómo trabajar con el editor de consultas, visite Consulta de una base de datos mediante el editor de consultas en la Guía de administración de Amazon Redshift.

Elimine todas las tablas utilizadas en este tutorial si ya existen en la base de datos. Para obtener más información, consulte Paso 3: Eliminar los recursos.

Cree la tabla accommodations para almacenar la ubicación geográfica de cada alojamiento (longitud y latitud), el nombre del listado y otros datos comerciales.

En este tutorial, se analiza el alquiler de habitaciones en Berlín, Alemania. En la columna shape, se almacenan los puntos geográficos de la ubicación de los alojamientos. Las otras columnas contienen información sobre el alquiler.

Para crear la tabla accommodations, ejecute la siguiente instrucción SQL en el editor de consultas de 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
);

Cree la tabla zipcode en el editor de consultas para almacenar los códigos postales de Berlín.

Un código postal se define como un polígono en la columna wkb_geometry. En el resto de las columnas, se describen metadatos espaciales adicionales sobre el código postal.

Para crear la tabla zipcode, ejecute la siguiente instrucción SQL en el editor de consultas de 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)
 );

Cargue las tablas con datos de muestra.

Los datos de muestra para este tutorial se encuentran en un bucket de Amazon S3 que permite el acceso de lectura a todos los usuarios de AWS autenticados. Asegúrese de proporcionar credenciales de AWS válidas que permiten el acceso a Amazon S3.

Para cargar datos de prueba en las tablas, ejecute los siguientes comandos COPY. Sustituya account-number por su número de cuenta de AWS. El segmento de la cadena de credenciales que está delimitado entre comillas simples no debe contener espacios ni saltos de línea.

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

Ejecute los siguientes comandos para comprobar que cada tabla se haya cargado de forma correcta.

select count(*) from accommodations;

select count(*) from zipcode;

Los siguientes resultados muestran la cantidad de filas en cada tabla de los datos de prueba.

Realice una consulta para obtener el recuento de la cantidad total de listados almacenados en la tabla accommodations, como se muestra a continuación. El sistema de referencia espacial es el sistema geodésico mundial (WGS) 84, que tiene el identificador de referencia espacial único 4326.

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

 count
-------
 22248

Recupere los objetos de geometría en formato de texto conocido (WKT) con algunos atributos adicionales. Además, puede comprobar si estos datos de código postal también se almacenan con el sistema geodésico mundial (WGS) 84, que utiliza el ID de referencia espacial (SRID) 4326. Los datos espaciales deben almacenarse en el mismo sistema de referencia espacial para que sean interoperables.

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)

Seleccione el polígono de Berlin-Mitte (10117), un distrito de Berlín, en formato GeoJSON, su dimensión y la cantidad de puntos en este 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

Ejecute el siguiente comando SQL para ver cuántos alojamientos hay a menos de 500 metros de la Puerta de Brandenburgo.

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

count
------
  29

Obtenga la ubicación aproximada de la Puerta de Brandenburgo a partir de los datos almacenados en los alojamientos que se mencionan como cercanos mediante la ejecución de la siguiente consulta.

Esta consulta requiere una subselección. Esto implica un recuento diferente, ya que la ubicación solicitada no es la misma que la consulta anterior porque está más cerca de los alojamientos.

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

Ejecute la siguiente consulta para mostrar los detalles de todos los alojamientos en los alrededores de la Puerta de Brandenburgo, ordenados por precio en orden descendente.

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)

Ejecute la siguiente consulta para recuperar el alojamiento más costoso con su 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,...

Calcule el precio máximo, mínimo o medio de los alojamientos con una subconsulta.

Con la siguiente consulta, se muestra el precio medio de los alojamientos 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)

Ejecute la siguiente consulta para recuperar la cantidad de alojamientos que se muestran para Berlín. Los puntos calientes se agrupan por código postal y se ordenan por la cantidad de suministro, por lo que pueden encontrarse de esta manera.

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)