En esta entrega leeremos algunos datos del Patrimonio de la Humanidad de la Unesco en formato de valores separados por comas (CSV), los cargaremos en una base de datos PostgreSQL mediante el comando COPY y crearemos tablas correctamente normalizadas.
Acerca del conjunto de datos
Un sitio del Patrimonio Mundial es un hito o área protegida legalmente por una convención internacional administrada por la Organización de las Naciones Unidas para la Educación, la Ciencia y la Cultura (UNESCO). Los sitios del Patrimonio Mundial son designados por la UNESCO por tener importancia cultural, histórica, científica o de otro tipo. Se considera que los sitios contienen «patrimonio cultural y natural en todo el mundo considerado de valor excepcional para la humanidad».
Para ser seleccionado, un Sitio del Patrimonio Mundial debe ser un hito único que sea identificable geográfica e históricamente y que tenga un significado cultural o físico especial. Por ejemplo, los sitios del Patrimonio Mundial pueden ser ruinas antiguas o estructuras históricas, edificios, ciudades,[a] desiertos, bosques, islas, lagos, monumentos, montañas o áreas silvestres. Un Sitio del Patrimonio Mundial puede significar un logro notable de la humanidad y servir como evidencia de nuestra historia intelectual en el planeta, o puede ser un lugar de gran belleza natural.
El dataset puede descargarse en desde el siguiente link: Unesco Heritage Sites dataset. Los datos corresponden al año 2018. Sin embargo si desea contar con datos más actualizados puede descargarlos desde los siguientes sitios (dataset 2021 a la fecha de hoy):
Procedimiento
Una vez descargado el dataset procedemos a crear las tablas parea la carga de datos. Puedes nombrar la base de datos como gustes y los nombre de las tablas también puedes modificarlos, aunque esta nomenclatura permite una mejor comprensión de su contenido.
DROP TABLE unesco_raw;
CREATE TABLE unesco_raw (
name TEXT,
description TEXT,
justification TEXT,
year INTEGER,
longitude FLOAT,
latitude FLOAT,
area_hectares FLOAT,
category TEXT,
category_id INTEGER,
state TEXT,
state_id INTEGER,
region TEXT,
region_id INTEGER,
iso TEXT,
iso_id INTEGER
);Para cargar los datos CSV para esta asignación, use el siguiente comando COPY desde la lonea de comando de PpostgreSQL (psql) . Agregar HEADER hace que el cargador CSV omita la primera línea en el archivo CSV. El comando \copy debe ser una línea larga.
\copy unesco_raw(nombre,descripción,justificación,año,longitud,latitud,área_hectáreas,categoría,estado,región,iso) FROM 'whc-sites-2018-small.csv' WITH DELIMITER ',' CSV HEADER;Revisamos los datos.
Ahora procedemos a normalizar los datos con la ayuda de tablas adicionales que podemos llamar «look up tables».
La normalización es el proceso de seguir una serie de reglas (formas normales), para asegurar que nuestras relaciones estén ordenadas y regularizadas con el fin de mejorar dichas relaciones. Están enfocadas en evitar la redundancia de datos e inconsistencias en el diseño de nuestras tablas.
Lo primero que determinamos es que existe redundancia de datos en campos como «categoría» donde encontramos «Sitio cultural», «Sitio natural » y «Sitio mixto». Estos datos los podemos trasladar a otra tabla y crear una relación con la tabla «unesco_raw» para eliminar esta réplica de datos.
Caso similar podemos apreciar en los campos «state», «region» e «iso».
Entonces procedemos a crear tablas para estos datos que contendrán los datos respectivos de «category», «state», «region» e «iso» de forma única (campos UNIQUE) con su respentido identificados (id SERIAL).
CREATE TABLE category (
id SERIAL,
name VARCHAR(128) UNIQUE,
PRIMARY KEY(id)
);
CREATE TABLE state (
id SERIAL,
name VARCHAR(128) UNIQUE,
PRIMARY KEY(id)
);
CREATE TABLE region (
id SERIAL,
name VARCHAR(128) UNIQUE,
PRIMARY KEY(id)
);
CREATE TABLE iso (
id SERIAL,
name VARCHAR(128) UNIQUE,
PRIMARY KEY(id)
);Cargamos los datos de «unesco_raw» a las tablas que hemos creado, seleccionando los valores únicos (SELECT DISTINCT).
INSERT INTO category(name) SELECT DISTINCT category FROM
unesco_raw;
INSERT INTO state(name) SELECT DISTINCT state FROM unesco_raw;
INSERT INTO region(name) SELECT DISTINCT region FROM
unesco_raw;
INSERT INTO iso(name) SELECT DISTINCT iso FROM unesco_raw;Ahora actualizamos la tabla «unesco_raw» con los datos de las tablas que hemos creado, agregarndo la categoría de sitio por su identificador (id) para crear la relación. En este caso utilizaremos una consulta compuesta para obtener primero el identificador de categoría de la tabla «category» para luego actualizar el campo «category_id» en «unesco_raw».
Igual procedemos con los datos de las tablas «state», «regiosn» e «iso».
UPDATE unesco_raw SET category_id = (
SELECT category.id FROM category WHERE category.name = unesco_raw.category);
UPDATE unesco_raw SET state_id = (
SELECT state.id FROM state WHERE state.name = unesco_raw.state );
UPDATE unesco_raw SET region_id = (
SELECT region.id FROM region WHERE region.name = unesco_raw.region);
UPDATE unesco_raw SET iso_id = (
SELECT iso.id FROM iso WHERE iso.name = unesco_raw.iso);De esta forma ya contamos con los identificadores para las categoría, los estados, las regiones y las nomenclaturas ISO.
Para poder descartar la información redundante procedemos a crear una tabla final, definitiva que llamaremos simplemente «unesco» de la siguiente forma:
CREATE TABLE unesco (
name TEXT,
description TEXT,
justification TEXT,
year INTEGER,
longitude FLOAT,
latitude FLOAT,
area_hectares FLOAT,
category_id INTEGER,
state_id INTEGER,
region_id INTEGER,
iso_id INTEGER
);Copiamos toda la información exceptuando los datos de las columnas categoría, los estados, las regiones y las nomenclaturas ISO, e incluyendo los identificadores de las demás tablas creadas para establecer las respectivas relaciones.
INSERT INTO
unesco(
name,
description,
justification,
year,
longitude,
latitude,
area_hectares,
category_id,
state_id,
region_id,
iso_id
) select
name,
description,
justification,
year,
longitude,
latitude,
area_hectares,
category_id,
state_id,
region_id,
iso_id from unesco_raw;De esta forma obtendremos la información del dataset World Heritage List de una forma mejor estructurada, sin redundancia de datos, aprovechando las ventajas que nos ofrece una base de datos relacional como PostgreSQL.
Ahpora podemos eliminar la tabla «unesco_raw» usando DROP TABLE, y revisar la información obtenida con una consulta como la siguiente:
SELECT unesco.name, year, category.name, state.name, region.name, iso.name
FROM unesco
JOIN category ON unesco.category_id = category.id
JOIN iso ON unesco.iso_id = iso.id
JOIN state ON unesco.state_id = state.id
JOIN region ON unesco.region_id = region.id
ORDER BY state.name, unesco.name
LIMIT 3;El resultado sería el siguiente:
En una siguiente entrega utilizaremos estos datos almacenados en PostgreSQL para general un mapa interactivo con geolocalización de cada sitio de la Unesco.
