• Saltar a la navegación principal
  • Saltar al contenido principal
  • Saltar al pie de página
logo web Geoinnova

Geoinnova

Consultoría y Formación en SIG y Medio Ambiente

  • Geoinnova
    • Asociación
    • Equipo
    • Visor de proyectos Geoinnova
    • Geolibrería
    • Alianzas Estratégicas
  • Consultoría
    • Consultoría y Desarrollo en Sistemas de Información Geográfica
    • Consultora Ambiental
    • Turismo sostenible
    • Planificación urbana y ordenación del territorio
    • Planes Urbanos de Actuación Municipal – PUAM
    • Geomarketing
  • Servicios
    • Planero Web
    • QElectricGIS
    • Plugins
  • Formación
    • Todos los cursos
    • Webinars
    • Máster y Diplomas
    • Cursos de ArcGIS
    • Cursos de QGIS
    • Cursos de Teledetección
    • Cursos de Medioambiente
  • Medio Ambiente
    • Consultora Ambiental
    • Estudios ambientales para proyectos de energías renovables
    • Estudios de Impacto e Integración Paisajística
    • Evaluación Ambiental Estratégica de planes y programas
    • Estudio de Impacto Ambiental para proyectos
    • Cálculo huella de carbono
    • Educación Ambiental
    • Cursos de Medioambiente
  • Coworking
  • Blog
    • SIG
    • Medio Ambiente
    • Teledetección
    • Programación y Desarrollo SIG
    • Corporativo
  • Contacto
    • Soporte de Clientes – Tickets
Programación y Desarrollo SIG SIG

Cómo conectar PostgreSQL a fuentes de datos externas

04/03/2021 Deja un comentario

Una característica muy interesante de PostgreSQL y que no es común en otros Sistemas Gestores de Bases de Datos, es la capacidad de acceder a diferentes almacenes de datos de fuentes remotas mediante una gran variedad de wrappers o contenedores de datos externos disponibles. Desde nuestro servidor Postgres podemos consultar directamente otras bases de datos o archivos externos (CSV, JSON, hojas de cálculo, etc.), sin necesidad de replicar datos a través de un proceso ETL (Extract, Transform and Load), y todo ello como si lo hiciésemos localmente.

Esta propiedad tan útil llamada Foreign Data Wrapper (FDW) crea tablas foráneas dentro una base de datos PostgreSQL que actúan a modo de proxies para alguna otra fuente de datos. Cuando realizamos una consulta en una tabla foránea, el wrapper preguntará a la fuente de datos externa y devolverá los resultados como si procedieran de una tabla propia. Esto aumenta la productividad al poder, por ejemplo, consultar una base de datos SQL Server o realizar un JOIN entre una tabla MySQL y una colección MongoDB desde tu propia instancia de PostgreSQL. El uso de FDW es también muy interesante cuando hay que gestionar grandes bases de datos federadas distribuidas en varias instancias.

Cómo usar FDW para conectar dos bases de datos PostgreSQL remotas

  1. Habilitamos la extensión fwd en la base de datos desde la que queremos acceder (en nuestro caso la local) y creamos opcionalmente un nuevo esquema para tenerlo todo más organizado:
CREATE EXTENSION postgres_fdw;
 CREATE SCHEMA esquema_destino;
  1. Opcionalmente nos aseguramos de eliminar la configuración al servidor remoto si ya la tuviésemos creada previamente.
DROP SERVER IF EXISTS servidor_bd_remota CASCADE;
  1. Configuramos el acceso al servidor remoto:
CREATE SERVER servidor_bd_remota 
 FOREIGN DATA WRAPPER postgres_fdw
 OPTIONS (host 'xx.xx.xx.xx', dbname 'foo', port '5432');
  1. Para consultar la base de datos remota es necesario saber qué usuario podrá realizar esta operación. Básicamente se trata de asignar un usuario local de nuestro servidor a un usuario del servidor remoto y para ello se crea una asignación de usuarios. En este caso el usuario es el mismo desde el que estamos operando (CURRENT_USER), pero podría ser postgres o cualquier otro:
CREATE USER MAPPING FOR CURRENT_USER SERVER servidor_bd_remota 
 OPTIONS (USER 'remote_user', password 'admin');
  1. Importamos el esquema remoto de tablas en el esquema de la base de datos local:
IMPORT FOREIGN SCHEMA esquema_remoto 
FROM SERVER servidor_bd_remota INTO esquema_destino;
  1. Finalmente ya podemos consultar esta tabla externa como si estuviese en nuestra base de datos:
SELECT * FROM tabla_foranea;

Si quieres un ejemplo sencillo con el que poder practicar aquí tienes el código para acceder mediante FDW a la base de datos de RNAcentral, una base de datos de acceso público de solo-lectura de secuencias de ácido ribonucleico (ARN).

Todo lo que hemos visto hasta ahora esta muy bien, pero sería muy interesante si pudiésemos utilizar estos contenedores de datos externos para acceder a geodatos.

FWD desde una perspectiva geográfica

Si nos fijamos en los tipos de contenedores de datos externos publicados en la web de PostgreSQL podemos ver que existen varios geowrappers, y entre ellos llama la atención uno nativo que permite acceder a la gran mayoría de fuentes de datos geoespaciales que habitualmente usamos: GDAL/OGR

Si desconoces qué es GDAL solo cabe decir que es la navaja suiza de los GIS. Esta librería es uno de los pilares sobre el que se fundamenta gran parte del desarrollo de software geoespacial actual, tanto de servidor como de escritorio, por lo que se la considera como un proyecto prioritario de software libre para la industria.

La librería se conoce en su conjunto simplemente como GDAL pero integra dos partes bien diferenciadas: OGR para manejar datos vectoriales y GDAL para la parte de datos ráster. De ahí que también se la llame GDAL/OGR.

Para visualizar el potencial que tiene este wrapper de GDAL en PostgreSQL vamos a ver un ejemplo en el que poder consultar los datos espaciales vectoriales facilitados por un servicio WFS directamente como si fuera una tabla nativa de Postgres.

CREATE EXTENSION postgis;
 CREATE EXTENSION ogr_fdw;
 DROP SERVER IF EXISTS fdw_wfs_server;
 CREATE SERVER fdw_wfs_server
   FOREIGN DATA WRAPPER ogr_fdw 
   OPTIONS ( 
     datasource 'WFS:https://servais.enaire.es/einsignia/services/Aero_VIGOR_SRV/MapServer/WFSServer',
     format 'WFS',
     config_options 'GDAL_HTTP_UNSAFESSL=YES'); -- para que no se moleste en verificar el certificado SSL
 -- A partir de PostgreSQL 9.5 ya es posible crear automáticamente la estructura de la
 -- tabla externa en nuestro servidor sin tener que definir las columnas manualmente:
 IMPORT FOREIGN SCHEMA "Aero_VIGOR_SRV:Aerodromos" 
 FROM SERVER fdw_wfs_server INTO public;

Aquí vemos cómo se crea un objeto de servidor externo mediante CREATE SERVER y se configura el acceso al servicio WFS público facilitado por el gestor de navegación aérea ENAIRE.

A continuación se importa el esquema de la capa de aeródromos, lo que crea una tabla foránea llamada aero_vigor_srv_aerodromos en el esquema public de nuestra base de datos Postgres local.

  • Explorador de objetos de pgAdmin
Tabla foránea en el explorador de objetos de pgAdmin

Ya solo nos queda realizar una consulta SQL a la tabla externa para obtener y analizar los datos remotos del servicio WFS.

SELECT * 
FROM aero_vigor_srv_aerodromos 
WHERE activity = 'Aeromodelismo';
  • Resultados del análisis
Resultados de la consulta contra una tabla foránea con datos de una fuente WFS

Las bases de datos relacionales como PostgreSQL han dominado durante mucho tiempo el almacenamiento y el acceso de datos, pero a veces necesitamos acceder desde nuestras aplicaciones a datos que están en un formato diferente. Como hemos visto los Foreign Data Wrappers nos proporcionan una capa de abstracción que nos facilita poder consultar esos datos de forma transparente, sin importar de donde proceden, cómo están estructurados y sin la necesidad de almacenar su contenido directamente en nuestra base de datos.


GDAL a su vez dota a PostgreSQL de una segunda capa de abstracción adicional que «traduce» los formatos geoespaciales que el Sistema Gestor de Bases de Datos no entiende, posibilitando así poder consultarlos mediante SQL, la lengua franca de las bases de datos.

Etiquetas: postgis, postgresql

Apúntate a nuestra newsletter semanal

Consentimiento*
*
Este campo es un campo de validación y debe quedar sin cambios.

Información legal

Cumplimos el RGPD (Reglamenteo General de Protección de Datos) y esta en la primera información que debes saber. Responsable: Asociación Geoinnova. Finalidad: Apuntarte en nuestra base de datos para poder enviarte nuestra newsletter y cualquier otra información de interes. Legitimidad: Nos das tu consentimiento para comunicarnos contigo. Derechos: Tienes derecho a acceder, rectificar, limitar y suprimir tus datos. Para una información más ampliada entra a nuestra política de privacidad.

Artículos relacionados

Cómo acceder a una base de datos remota mediante SSH en QGIS
21/12/2021
Creación de base de datos con PostGIS desde la consola psql
Gestión de usuarios y permisos de una base de datos geográfica PostgreSQL/PostGIS
08/02/2021
Instalación de PostGIS 3.0
Cómo instalar PostGIS 3.0 en Windows
06/02/2022

Emilio Gomez Fernandez

Emilio Gómez Fernández es GIS Project Manager en Alter Geosistemas. Es geógrafo con amplia experiencia profesional en el ámbito de la geomática y las tecnologías afines. Ha dirigido y participado en numerosos proyectos de consultoría e ingeniería en las fases de planificación, ejecución y control relacionados con el medio ambiente, la ordenación del territorio, los sistemas de transporte y la accesibilidad, los servicios urbanos y la divulgación del patrimonio. Colabora con Geoinnova en proyectos de formación y de desarrollo GIS.

Interacciones con los lectores

¿Quieres comentarnos algo? Adelante! Cancelar la respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Footer

Menú Legal

  • Aviso Legal
  • Política de privacidad
  • Política de cookies
  • Términos y condiciones

Otras páginas de interes

  • Promociones y descuentos en formación

Contacto

Calle Luis Morondo Urra nº 8 Bajo A
31006 Pamplona (Navarra)
info@geoinnova.org
+34 948 117 137

Horario de atención

Lunes a Viernes - 09:00 a 14:00 y de 16:00 a 19:00.
Fines de semana y Festivos - Exclusivamente por mail.
Horario de Verano (01 de julio al 30 de agosto): De lunes a viernes de 8 a 15h.
Entidad adherida a Confianza Online Certificado de inscripción en el Registro de huella de carbono, compensación y proyectos de absorción

© 2022 · Desarrollada por Geoinnova en colaboración con OikosMSP

Gestionar el Consentimiento de las Cookies
Desde Geoinnova utilizamos cookies propias y de terceros para fines técnicos, analíticos y de marketing, para con ello poder ofrecerte una mejor experiencia de usuario.
Funcional Siempre activo
El almacenamiento o acceso técnico es estrictamente necesario para el propósito legítimo de permitir el uso de un servicio específico explícitamente solicitado por el abonado o usuario, o con el único propósito de llevar a cabo la transmisión de una comunicación a través de una red de comunicaciones electrónicas.
Preferencias
El almacenamiento o acceso técnico es necesario para la finalidad legítima de almacenar preferencias no solicitadas por el abonado o usuario.
Estadísticas
El almacenamiento o acceso técnico que es utilizado exclusivamente con fines estadísticos. El almacenamiento o acceso técnico que es utilizado exclusivamente con fines estadísticos anónimos. Sin una requerimiento, el cumplimiento voluntario por parte de su proveedor de servicios de Internet, o los registros adicionales de un tercero, la información almacenada o recuperada sólo para este propósito no se puede utilizar para identificarlo.
Marketing
El almacenamiento o acceso técnico es necesario para crear perfiles de usuario para enviar publicidad, o para rastrear al usuario en un sitio web o en varios sitios web con fines de marketing similares.
Administrar opciones Gestionar los servicios Gestionar proveedores Leer más sobre estos propósitos
Preferencias
{title} {title} {title}