Uso de la Extensión PostGIS en PostgreSQL

Introducción

En este laboratorio, aprenderá a utilizar la extensión PostGIS para añadir capacidades espaciales a una base de datos PostgreSQL. PostGIS transforma su base de datos en una base de datos espacial, permitiéndole almacenar y consultar datos definidos en un espacio geométrico.

Comenzará creando una nueva base de datos y habilitando la extensión PostGIS. Luego, creará una tabla con una columna GEOMETRY para almacenar datos de ubicación. Finalmente, insertará datos para varias ciudades y realizará consultas espaciales para calcular distancias y encontrar datos dentro de un área específica.

Crear una Base de Datos Espacial y Habilitar PostGIS

En este paso, creará una nueva base de datos y habilitará la extensión PostGIS dentro de ella. Es una buena práctica utilizar una base de datos dedicada para datos espaciales para mantenerla organizada.

Primero, abra una terminal y conéctese al servidor PostgreSQL utilizando la terminal interactiva psql. Se conectará como el superusuario predeterminado postgres.

sudo -u postgres psql

Verá el prompt de psql, que se ve como postgres=#.

A continuación, cree una nueva base de datos llamada spatial_db. Todos los comandos SQL en psql deben terminar con un punto y coma (;).

CREATE DATABASE spatial_db;

Debería ver el siguiente mensaje de confirmación:

CREATE DATABASE

Ahora, conéctese a su base de datos recién creada. El comando \c se utiliza para conectarse a una base de datos diferente.

\c spatial_db

Su prompt cambiará para indicar que ahora está conectado a spatial_db:

You are now connected to database "spatial_db" as user "postgres".
spatial_db=#

Con la base de datos lista, habilite la extensión PostGIS. Este comando carga todos los tipos, funciones y tablas espaciales de PostGIS en su base de datos.

CREATE EXTENSION postgis;

La salida confirmará que la extensión fue creada:

CREATE EXTENSION

Para verificar que PostGIS está instalado correctamente, puede comprobar su versión.

SELECT PostGIS_full_version();

Esto mostrará información detallada de la versión de PostGIS y sus bibliotecas relacionadas, confirmando una configuración exitosa. Ahora puede proceder al siguiente paso. No salga del shell psql.

Crear una Tabla Espacial e Insertar Datos

Ahora que tiene una base de datos espacial, puede crear tablas para almacenar datos geográficos. En este paso, creará una tabla cities e insertará las coordenadas de algunas ciudades importantes.

Todavía debería estar en el shell psql, conectado a la base de datos spatial_db.

Primero, cree una tabla llamada cities. Esta tabla tendrá un id y name estándar, junto con una columna location de tipo GEOMETRY.

Entendiendo GEOMETRY(Point, 4326)

• GEOMETRY: Este es el tipo de dato fundamental de PostGIS para almacenar características espaciales.
• Point: Esto especifica que la columna de geometría solo almacenará puntos. Otros tipos incluyen LINESTRING y POLYGON.
• 4326: Este es el Identificador del Sistema de Referencia Espacial (SRID). El SRID 4326 corresponde a WGS 84, el sistema de coordenadas estándar utilizado por GPS y Google Maps.

Ejecute el siguiente comando para crear la tabla:

CREATE TABLE cities (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    location GEOMETRY(Point, 4326)
);

Verá la confirmación:

CREATE TABLE

A continuación, inserte datos en la tabla cities. Utilizará la función ST_GeomFromText para convertir representaciones de texto bien conocidas (Well-Known Text - WKT) de puntos en objetos de geometría. El formato es POINT(longitud latitud).

INSERT INTO cities (name, location) VALUES
    ('New York', ST_GeomFromText('POINT(-74.0060 40.7128)', 4326)),
    ('London', ST_GeomFromText('POINT(-0.1278 51.5074)', 4326)),
    ('Tokyo', ST_GeomFromText('POINT(139.6917 35.6895)', 4326));

La salida indica que se insertaron tres filas:

INSERT 0 3

Para verificar que los datos se insertaron correctamente, puede consultar la tabla. Utilice la función ST_AsText para convertir el objeto de geometría de nuevo a un formato de texto legible por humanos.

SELECT id, name, ST_AsText(location) FROM cities;

La salida debería mostrar los datos que acaba de insertar:

 id |   name   |          st_astext
----+----------+-----------------------------
  1 | New York | POINT(-74.006 40.7128)
  2 | London   | POINT(-0.1278 51.5074)
  3 | Tokyo    | POINT(139.6917 35.6895)
(3 rows)

Ha creado con éxito una tabla espacial y la ha poblado con datos.

Realizar Consultas y Análisis Espaciales

El verdadero poder de PostGIS reside en su capacidad para realizar consultas espaciales. En este paso, utilizará funciones de PostGIS para calcular la distancia entre dos puntos y para encontrar qué puntos caen dentro de un área determinada.

Todavía debería estar en el shell psql, conectado a la base de datos spatial_db.

Primero, calculemos la distancia entre Nueva York y Londres. Para cálculos de distancia precisos en la superficie de la Tierra, es mejor convertir el tipo geometry al tipo geography. El tipo geography tiene en cuenta la curvatura de la Tierra, y ST_Distance devolverá el resultado en metros.

SELECT ST_Distance(
    (SELECT location::geography FROM cities WHERE name = 'New York'),
    (SELECT location::geography FROM cities WHERE name = 'London')
);

La consulta devolverá la distancia del círculo máximo en metros. El resultado será un número grande, aproximadamente 5.57 millones de metros.

   st_distance
-----------------
 5570299.7888681
(1 row)

A continuación, realicemos un análisis para encontrar cuáles de nuestras ciudades se encuentran a menos de 200 kilómetros de Londres. Para hacer esto, primero creará un buffer circular alrededor de la ubicación de Londres y luego verificará qué ubicaciones de ciudades se cruzan con ese buffer.

• ST_Buffer: Crea una geometría que representa todos los puntos cuya distancia a la geometría de origen es menor o igual a una distancia especificada. La distancia está en metros cuando se usa con el tipo geography.
• ST_Intersects: Devuelve true si dos geometrías se "cruzan espacialmente", es decir, si comparten algún espacio.

WITH london_buffer AS (
  SELECT ST_Buffer(location::geography, 200000) AS geom FROM cities WHERE name = 'London'
)
SELECT c.name
FROM cities c, london_buffer lb
WHERE ST_Intersects(c.location::geography, lb.geom);

Esta consulta utiliza una Expresión Común de Tabla (CTE) para crear un buffer de 200,000 metros (200 km) alrededor de Londres. Luego encuentra todas las ciudades que se cruzan con este buffer. Como era de esperar, solo Londres mismo está dentro de esta área.

  name
--------
 London
(1 row)

Ahora ha realizado un análisis espacial básico. Puede salir del shell psql escribiendo \q.

\q

Limpiar la Base de Datos

Es una buena práctica limpiar su entorno después de completar su trabajo. En este paso, eliminará la base de datos spatial_db. Eliminar una base de datos la remueve a ella misma, junto con todos los objetos que contiene, como tablas y extensiones.

Debería estar de vuelta en el prompt normal de la terminal (labex@vm:~$).

Para eliminar una base de datos, debe estar conectado a una base de datos diferente. Conéctese a la base de datos predeterminada postgres.

sudo -u postgres psql

Ahora, ejecute el comando DROP DATABASE.

DROP DATABASE spatial_db;

Verá un mensaje de confirmación:

DROP DATABASE

Puede verificar que la base de datos ha sido eliminada listando todas las bases de datos disponibles con el comando \l.

\l

Verá que spatial_db ya no está en la lista.

Finalmente, salga del shell psql.

\q

Resumen

En este laboratorio, aprendió a utilizar la extensión PostGIS para agregar soporte de datos espaciales a PostgreSQL. Creó con éxito una base de datos dedicada, habilitó la extensión PostGIS, definió una tabla con una columna de geometría, insertó datos de ubicación y realizó consultas espaciales para calcular distancias y encontrar intersecciones. Estas habilidades proporcionan una base sólida para construir aplicaciones conscientes de la ubicación con PostgreSQL.