Guía de PostGIS: Ejemplos básicos de consulta de datos geoespaciales

Los datos geoespaciales son cada vez más importantes en muchos campos, desde la planificación urbana hasta las ciencias medioambientales. En este artículo, demostraremos las consultas PostGIS básicas de PostgreSQL para trabajar con este tipo de datos.

Los datos geoespaciales, que contienen información sobre ubicaciones en la Tierra, requieren herramientas especializadas para su uso efectivo. PostGIS es una poderosa extensión de PostgreSQL que convierte una base de datos Postgres en un completo Sistema de Información Geográfica (SIG). Con PostGIS, puede almacenar objetos geográficos, ejecutar consultas espaciales y realizar análisis avanzados directamente en SQL. Esto lo convierte en una herramienta esencial para cualquiera que trabaje con datos basados en la localización.

La instalación de PostGIS le permite realizar operaciones espaciales que normalmente requieren software SIG especializado. Tanto si necesita calcular distancias, medir áreas o analizar relaciones espaciales, PostGIS le equipa con las herramientas necesarias para manejar datos espaciales complejos de forma eficiente.

Si desea profundizar sus conocimientos de PostGIS, considere la posibilidad de inscribirse en nuestro curso de PostGIS. Le llevará desde los fundamentos de los SIG hasta las técnicas avanzadas de PostGIS. Cuando lo haya completado, sabrá cómo aprovechar la potencia de PostGIS en sus proyectos.

En esta guía, le presentaremos los conceptos básicos de PostGIS. Comenzaremos con consultas espaciales sencillas y pasaremos a técnicas avanzadas como el análisis de proximidad y el análisis de redes. Si quiere aprender aún más sobre datos geoespaciales, consulte nuestro artículo Introducción a las bases de datos espaciales y a los datos geoespaciales.

Nuestros datos geoespaciales de muestra

En este artículo, trabajaremos con un conjunto de datos espaciales extraídos de San Francisco, California. Utilizaremos varias tablas:

• sf_tram_stops: Esta tabla contiene las ubicaciones de las paradas de tranvía de la ciudad; también almacena las coordenadas de ubicación y si es posible realizar transbordos entre líneas en esa parada. Tiene las siguientes columnas:
  • id - Un identificador único para cada parada.
  • coordinates - Las coordenadas de ubicación de la parada.
  • transfer_possible - Si un pasajero puede cambiar de tranvía en esa parada.
    
• sf_planning_districts: Esta tabla define los límites de los distritos de planificación de San Francisco; esta información es esencial para los análisis urbanos. Tiene las siguientes columnas:
  • id - Un identificador único para cada distrito de planificación.
  • name - Nombre del distrito.
  • boundaries - Los límites del distrito (como coordenadas de ubicación).
• sf_bicycle_routes: Esta tabla almacena información sobre las rutas ciclistas de la ciudad; la necesitará para analizar la infraestructura ciclista de SanFran. Tiene las siguientes columnas:
  • id - Un identificador único para cada ruta ciclista.
  • course - El recorrido de la ruta.
  • condition_rating - La clasificación de la ruta (basada en sus condiciones).
• sf_restaurants: Esta tabla contiene información sobre los restaurantes de San Francisco, incluidos sus nombres, ubicaciones, clasificaciones y tipos de cocina. Tiene las siguientes columnas:
  • id - Un identificador único para cada restaurante.
  • name - El nombre del restaurante.
  • food_type - Tipo de cocina que sirve el restaurante.
  • rating - Puntuación del restaurante.
  • coordinates - Las coordenadas de ubicación del restaurante.
• sf_sights: Esta tabla registra información sobre puntos de referencia y puntos de interés (POI) notables dentro de la ciudad. Tiene las siguientes columnas:
  • id - Un identificador único para cada punto de interés.
  • name - El nombre del PDI.
  • coordinates - Ubicación del PDI.
• sf_atms: Esta tabla almacena detalles de los cajeros automáticos de SanFran, incluyendo la ubicación de cada cajero y qué compañía lo opera. Tiene las siguientes columnas:
  • id - Un identificador único para cada cajero automático.
  • company - La empresa que opera el cajero.
  • coordinates - La ubicación del cajero.

Estos datos sirven de base para las consultas espaciales que vamos a explorar. Utilizaremos estas consultas para analizar la proximidad entre puntos de referencia y servicios, calcular áreas dentro de distritos de planificación y mucho más. Cada consulta demostrará cómo PostGIS le ayuda a extraer información significativa de los datos espaciales.

Consultas espaciales básicas con PostGIS

Una vez que sus datos geoespaciales están almacenados en PostgreSQL con PostGIS, ¡puede empezar a consultarlos! Las consultas espaciales le permiten seleccionar, filtrar y manipular datos basados en su ubicación, forma y relaciones espaciales. Empecemos a trabajar con datos geoespaciales.

Visualización de datos geoespaciales

En primer lugar, veamos qué aspecto tienen los datos geoespaciales. Aquí seleccionamos los ID de las paradas de tranvía y las coordenadas de las paradas donde los usuarios pueden hacer transbordo:

SELECT
  id, 
  coordinates
FROM sf_tram_stops
WHERE transfer_possible = true;

Este es el resultado:

Como puede ver, los datos geoespaciales de la columna de coordenadas no son realmente legibles. Para que fueran realmente utilizables, se necesitaría un software especializado para trazarlos en el mapa. Afortunadamente, nuestro curso PostGIS viene con un mapa incorporado; ahora puedes ver la ubicación de estas paradas de tranvía:

Convertir datos geoespaciales en texto

Tal vez desee ver las coordenadas de ubicación de las paradas de tranvía anteriores sin un mapa. Puede utilizar la siguiente consulta para convertir los datos en coordenadas legibles:

SELECT
  id, 
  ST_AsText(coordinates),
  ST_Y(coordinates),
  ST_X(coordinates)
FROM sf_tram_stops
WHERE transfer_possible = true;

Esta consulta utiliza la función PostGIS ST_AsText para obtener las coordenadas en un formato legible. Utiliza ST_Y y ST_X para extraer los valores de latitud y longitud. A continuación se muestra un resultado parcial:

Relaciones espaciales

PostGIS ofrece varias funciones para explorar las relaciones espaciales de los objetos geoespaciales. Repasémoslas rápidamente.

ST_Intersección

Esta función devuelve la parte compartida (es decir, la intersección) de dos geometrías. La siguiente consulta muestra todas las rutas ciclistas (o sus partes) dentro de los límites del distrito de Downtown:

SELECT
  sfbr.id,
  ST_Intersection(sfpd.boundaries, sfbr.course)
FROM sf_bicycle_routes sfbr
JOIN sf_planning_districts sfpd
  ON ST_Intersects(sfpd.boundaries, sfbr.course)
WHERE sfpd.name = 'Downtown';

La función ST_Intersection encuentra la zona en la que se solapan dos formas - en este caso, donde las rutas ciclistas cruzan los límites del distrito "Downtown". La función ST_Intersects comprueba si las rutas ciclistas y los límites del distrito se tocan o se cruzan, asegurándose de que sólo se incluyen los que lo hacen.

He aquí un resultado parcial en forma de texto:

Y como mapa:

ST_Contiene

La función ST_Contains comprueba si una geometría contiene completamente a otra. Para listar los distritos de planificación que contienen cajeros automáticos de la empresa Crown Financial, puede ejecutar esta consulta:

SELECT DISTINCT sfn.name
FROM sf_planning_districts sfn
JOIN sf_atms sfa
  ON ST_Contains(sfn.boundaries, sfa.coordinates)
WHERE sfa.company = 'Crown Financial Inc.';

Este es el resultado

ST_Within

La función ST_Within también comprueba si una geometría está completamente dentro de otra. Esta consulta localiza restaurantes con una puntuación superior a 4,0 dentro del distrito Noreste:

SELECT 
  sep.name,
  sep.coordinates
FROM sf_planning_districts spd
JOIN sf_restaurants sep
  ON ST_Within(sep.coordinates, spd.boundaries)
WHERE rating > 4.0
  AND spd.name = 'Northeast';

Esta consulta devuelve los nombres y las coordenadas de los restaurantes, lo que facilita su visualización en un mapa.

Cálculo de distancias y áreas

El cálculo de distancias y áreas es otro aspecto fundamental del trabajo con datos geoespaciales. Estos cálculos pueden ayudar a responder preguntas como "¿A qué distancia se encuentra este cajero automático de una determinada vista?" o "¿Cuál es la superficie de este distrito de planificación?".

Cálculo de distancias

La función ST_Distance devuelve la distancia entre dos objetos geoespaciales. Toma dos argumentos geométricos: ST_Distance(geometryA, geometryB).

Para visualizar todos los cajeros automáticos situados a menos de 300 metros de Fisherman's Wharf, puede utilizar la siguiente consulta:

SELECT   
  sa.id,
  sa.coordinates,
  ST_Distance(
    ST_Transform(sa.coordinates, 26910), 
    ST_Transform(ss.coordinates, 26910)) AS distance
FROM sf_sights ss
JOIN sf_atms sa
  ON ST_Distance(
     ST_Transform(sa.coordinates, 26910), 
     ST_Transform(ss.coordinates, 26910)) < 300
WHERE ss.name = 'Fisherman''s Wharf';

En esta consulta, la función ST_Transform convierte las coordenadas dadas en un sistema de coordenadas específico adecuado para los cálculos espaciales. El sistema de coordenadas garantiza que todas las operaciones espaciales (como el cálculo de distancias) sean precisas y se ajusten a las necesidades específicas de la zona geográfica y del proyecto.

En esta consulta utilizamos el sistema de coordenadas definido por el SRID (Spatial Reference System Identifier) 26910, que corresponde al sistema NAD83 / UTM zona 10N. Este sistema se utiliza habitualmente para calcular distancias con precisión en la zona de San Francisco.

¿Cómo puede encontrar el SRID correcto? Pruebe uno de estos sitios web:

• Spatial Reference Website: Utilice la pestaña EPSG.
• io: Escriba el nombre del país, el código o el nombre del sistema de coordenadas para buscar y obtener todos los sistemas de referencia de coordenadas posibles.

Al transformar las coordenadas, la consulta garantiza que el cálculo de la distancia entre el cajero automático y Fisherman's Wharf sea preciso. He aquí el resultado:

Cálculo de superficies

Para calcular la superficie de un distrito de planificación, utilice la función ST_Area. Ésta toma un argumento geométrico (ST_Area(geometry)) y devuelve el área de la forma resultante.

Esta es la consulta. Observe que volvemos a utilizar SRID = 26910 como sistema de coordenadas:

SELECT
  ST_Area(ST_Transform(boundaries, 26910))
FROM sf_planning_districts
WHERE name = 'Buena Vista';

Y éste es el resultado:

Con SRID = 26910, la unidad por defecto son los metros. Por lo tanto, el resultado de la ST_Area se presenta en metros cuadrados.

Análisis espacial avanzado

A medida que se familiarice con PostGIS, podrá utilizar sus capacidades avanzadas para realizar análisis espaciales en profundidad. Estas técnicas le permiten explorar relaciones espaciales, realizar análisis de proximidad e incluso llevar a cabo análisis de redes, haciendo que sus datos geoespaciales sean mucho más procesables.

Amortiguación y análisis de proximidad

La amortiguación crea zonas alrededor de las características espaciales. Esto resulta especialmente útil en los análisis de proximidad, en los que es necesario determinar la proximidad entre determinadas características. Por ejemplo, si desea identificar todos los restaurantes situados en un radio de 3.000 metros del "Palacio de Bellas Artes", puede utilizar la función ST_DWithin:

SELECT   
  sep.name,
  sep.rating,
  sep.food_type,
  sep.coordinates,
  ST_Distance(
    ST_Transform(sep.coordinates, 26910), 
    ST_Transform(ss.coordinates, 26910)) AS distance
FROM sf_sights ss
JOIN sf_restaurants sep
  ON ST_DWithin(
     ST_Transform(sep.coordinates, 26910), 
     ST_Transform(ss.coordinates, 26910), 
     3000)
WHERE ss.name = 'Palace of Fine Arts';

Y aquí tiene los resultados en forma de tabla y mapa:

Operaciones de superposición

En cartografía, las operaciones de superposición nos permiten ver cómo coinciden o interactúan distintos conjuntos de información geográfica. Imagina que tienes dos mapas, uno con todos los parques de una ciudad y otro con las zonas propensas a inundarse. Colocando un mapa encima del otro, puedes averiguar qué parques corren riesgo de inundación. Este proceso nos ayuda a comprender y visualizar dónde coinciden diferentes accidentes geográficos; hace que la planificación, el análisis y la toma de decisiones sean más eficaces.

La siguiente consulta utiliza la función ST_Union para fusionar los límites de todos los distritos de planificación "recreativa" en una forma geométrica unificada:

SELECT
  ST_Union(boundaries) 
FROM sf_planning_districts 
WHERE type = 'recreational';

De forma similar, puede utilizar ST_Intersection para conseguir lo mismo.

Análisis de redes

El análisis de redes consiste en examinar las rutas, identificar los caminos más cortos y mejorar la eficacia de los desplazamientos dentro de una red. Es fundamental para comprender y optimizar los sistemas de transporte y la conectividad. PostGIS - cuando se combina con pgRouting - ofrece herramientas robustas para este tipo de análisis.

La extensión pgRouting proporciona algoritmos avanzados para la optimización de rutas. Uno de los más conocidos es la función pgr_dijkstra, que calcula la ruta más corta o rápida entre dos puntos teniendo en cuenta factores como el tipo de carretera, las condiciones del tráfico y otras variables. Esta capacidad es inestimable para los sistemas de navegación, la planificación logística, la gestión del transporte y otros. Al aprovechar estas extensiones, los usuarios pueden mejorar la planificación de rutas, reducir los tiempos de viaje y mejorar la eficiencia general de la red.

Siga explorando los datos espaciales con PostGIS

PostGIS, con su perfecta integración en PostgreSQL, proporciona una potente plataforma para gestionar y analizar datos geoespaciales. A lo largo de este artículo, hemos cubierto los fundamentos de las consultas espaciales, nos hemos aventurado en el análisis espacial avanzado y hemos demostrado cómo PostGIS puede ayudarle a responder preguntas geográficas complejas con precisión y eficiencia.

Aprender PostGIS puede transformar su enfoque de los datos espaciales. Le facilitará la extracción de información valiosa, la optimización de los flujos de trabajo y la toma de decisiones basadas en datos. Tanto si se dedica a la planificación urbana, al análisis medioambiental, a las redes de transporte o a cualquier campo relacionado con los SIG, PostGIS le proporciona las herramientas necesarias para manejar datos geoespaciales con eficacia.

A medida que explore las posibilidades de PostGIS, descubrirá que mejora su capacidad para trabajar con datos geoespaciales y abre nuevas vías para la innovación y el descubrimiento. La combinación de las robustas capacidades de la base de datos PostgreSQL y las funciones geoespaciales de PostGIS le permiten afrontar con confianza incluso los retos espaciales más complejos.

Antes de irme, mencionaré de nuevo nuestro curso completo de PostGIS; es realmente una gran opción para obtener práctica en el mundo real con datos espaciales. Le guiará a través de todos los aspectos de PostGIS, desde los fundamentos hasta las técnicas avanzadas. Antes de que se dé cuenta, estará abordando con confianza proyectos de datos geoespaciales.

PostGIS es más que una extensión de PostgreSQL. Es una puerta de entrada para liberar todo el potencial de los datos geoespaciales dentro de sus sistemas de bases de datos. Al dominar las características de PostGIS, puede convertir los datos espaciales en información procesable que impulse el éxito en sus proyectos y para su empresa.