3rd Oct 2024 Lectura de 11 minutos Guía de PostGIS: Ejemplos básicos de consulta de datos geoespaciales Michał Wasiluk PostgreSQL consultas de SQL Índice Nuestros datos geoespaciales de muestra Consultas espaciales básicas con PostGIS Visualización de datos geoespaciales Convertir datos geoespaciales en texto Relaciones espaciales ST_Intersección ST_Contiene ST_Within Cálculo de distancias y áreas Cálculo de distancias Cálculo de superficies Análisis espacial avanzado Amortiguación y análisis de proximidad Operaciones de superposición Análisis de redes Siga explorando los datos espaciales con PostGIS 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: idcoordinates 10101000020E610000030D80DDB16995EC0742497FF90CE4240… 40101000020E61000000DAB7823F3985EC010AFEB17ECCE4240… 50101000020E6100000FDD98F1491995EC07AC7293A92CF4240… … 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: idst_astextst_yst_x 1POINT(-122.39202 37.6138)37.6138-122.39202 2POINT(-122.38984 37.61658)37.61658-122.38984 3POINT(-122.39948 37.62165)37.62165-122.39948 … 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: idst_intersection 4090102000020E61000000C000000438F3471DF9A5EC058F13A2… 4410102000020E6100000100000007451E9429B995EC0EF7A00F.. 4120102000020E610000009000000ED6FEAA8999A5EC06469EB2.. … 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 name Downtown Northeast 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. namecoordinates Fast Duck0101000020E6100000B9FC87F4DB995EC02E73BA2C26E64240 Red Curry0101000020E6100000569FABADD8995EC0E1455F419AE54240 The Saloon0101000020E6100000D52137C30D9A5EC0431CEBE236E64240 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: idcoordinatesdistance 1320101000020E6100000FC00A436719A5EC009FEB7921DE74240189.1839552624224 1330101000020E61000000D897B2C7D9A5EC033A7CB6262E74240277.9680083048927 1350101000020E61000000E15E3FC4D9A5EC0650113B875E74240216.11656973079064 1360101000020E6100000910A630B419A5EC033FE7DC685E74240283.89507791796825 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: st_area 2617829.8666631826 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: nameratingfood_typecoordinatesdistance Olive2.10Greek0101000020E6100000C9AB730CC89A5EC0527E52EDD3E542402764.17980825426 La fragranza4.86Italian0101000020E61000008811C2A38D9D5EC038F3AB3940E442402361.1920187470487 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. Tags: PostgreSQL consultas de SQL