Comenzaremos a configurar nuestro gestor de base de datos PgAdmin 4
para crear una conexion damos clock derecho soble el icono servidores(Servers) => click en registro(Register) => click en servidor(Server...)
Utilice el cuadro de diálogo Servidor para describir una conexión a un servidor. Nota: debe asegurarse de que el archivo pg_hba.conf del servidor desde el que se está conectando permita conexiones desde el host del cliente.
Utilice los campos de la pestaña General para identificar el servidor:
Utilice el campo Nombre para agregar un nombre descriptivo para el servidor; el nombre especificado se mostrará en el Explorador de objetos .
Utilice el cuadro de lista desplegable en el campo Grupo de servidores para seleccionar el nodo principal del servidor; el servidor se mostrará en el control del Explorador de objetos dentro del grupo especificado.
Utilice el selector de color en el campo Fondo para especificar el color de fondo del servidor.
Utilice el selector de color en el campo Primer plano para especificar el color de primer plano para el servidor.
¿ Si el Connect ahora? Si la casilla de verificación está marcada, el cliente intentará conectarse al servidor al finalizar el diálogo; este es el valor predeterminado
¿ Si el compartido? El interruptor se mueve a Sí , entonces ese servidor se puede compartir con todos los demás usuarios. Esta opción está disponible sólo para usuarios administradores. Para obtener más información sobre los usuarios, consulte el cuadro de diálogo Gestión de usuarios . Los usuarios pueden acceder a los servidores compartidos con algunas restricciones; las siguientes operaciones en servidores compartidos no están permitidas:
Eliminar el servidor
Cambiar el nombre del servidor
Cambiar el nombre del servidor de grupo
Cambio de host, puerto y base de datos de mantenimiento.
Tenga en cuenta que una vez que se comparte el servidor, su icono cambia en el explorador de objetos.
Utilice el campo Nombre de usuario compartido para completar el nombre de usuario de la conexión del servidor compartido. De forma predeterminada, tomará el nombre de usuario del servidor que se comparte.
Proporcione un comentario sobre el servidor en el campo Comentarios .
Haga clic en la pestaña Conexión para continuar.
Utilice los campos de la pestaña Conexión para configurar una conexión:
Especifique la dirección IP del host del servidor o el nombre de dominio completo en el campo Nombre/dirección del host . Si proporciona un socket de dominio Unix, el nombre del directorio debe comenzar con “/”.
Ingrese el número de puerto de escucha del host del servidor en el campo Puerto . El valor predeterminado es 5432 .
Utilice el campo Base de datos de mantenimiento para especificar el nombre de la base de datos inicial a la que se conectará el cliente. Si va a utilizar objetos pgAgent o adminpack, el esquema pgAgent y los objetos adminpack deben instalarse en esa base de datos.
Utilice el campo Nombre de usuario para especificar el nombre de una función que se utilizará al autenticarse con el servidor.
¿ Cuándo se realiza la autenticación Kerberos? está configurado en True , pgAdmin intentará conectar el servidor PostgreSQL utilizando la autenticación Kerberos.
Utilice el campo Contraseña para proporcionar una contraseña que se proporcionará al autenticarse con el servidor.
Marque la casilla junto a ¿Guardar contraseña? para indicarle a pgAdmin que guarde la contraseña para usarla en el futuro. Utilice Borrar contraseña guardada para eliminar la contraseña guardada.
Utilice el campo Rol para especificar el nombre de un rol que tiene privilegios que se transmitirán al cliente después de la autenticación con el servidor. Esta selección le permite conectarse como una función y luego asumir los permisos de esta función especificada una vez establecida la conexión. Tenga en cuenta que el rol de conexión debe ser miembro del rol especificado.
Utilice el campo Servicio para especificar el nombre del servicio. Para obtener más información.
al terminar de colocar los datos de configuracion , se presentara asi
da doble click en la conexion que ballas a trabajar y te solicitara nuevamente la contraseña de usuario
se muestra el arbol de la base de datos
Puede expandir los nodos en el control de árbol para ver los objetos de la base de datos que residen en un servidor seleccionado. El control de árbol se expande para mostrar una vista jerárquica:
Utilice el signo más (+) a la izquierda de un nodo para expandir un segmento del control de árbol.
Haga clic en el signo menos (-) a la izquierda de un nodo para cerrarlo.
También puede arrastrar y soltar ciertos objetos en la herramienta de consulta, lo que puede ahorrar tiempo al escribir nombres largos de objetos. El texto que contiene el nombre del objeto estará completamente calificado con esquema. Se agregarán comillas dobles si es necesario. Para funciones y procedimientos, el nombre de la función junto con los nombres de los parámetros se pegarán en la herramienta de consulta.
Casts
La función CAST en PostgreSQL se utiliza para convertir un valor de un tipo de datos a otro. Hay dos formas de CAST un valor en PostgreSQL. La primera es usar la sintaxis CAST (expresión AS tipo_objetivo), donde especifica una expresión que puede ser una constante, una columna de tabla o una expresión que evalúa a un valor. Luego, especifique el tipo de datos objetivo al que desea convertir el resultado de la expresión. La segunda forma es usar la sintaxis expresión::tipo, que es específica de PostgreSQL y no cumple con el estándar SQL.
Catalogs
Los catálogos en una base de datos son tablas que contienen información relevante sobre las opciones finales de un usuario en una aplicación. Los catálogos son indispensables para un buen diseño de la base de datos, ya que permiten una rápida obtención de los datos requeridos al momento de realizar una consulta, reducen el tiempo en programación y permiten al usuario final la posibilidad de realizar una modificación de manera dinámica a las opciones del software
Event triggers
Los triggers de eventos en PostgreSQL son similares a los triggers regulares, pero se definen a nivel de base de datos en lugar de a nivel de tabla. Los triggers de eventos se pueden definir para que se disparen antes o después de cualquier sentencia INSERT, UPDATE o DELETE, y pueden ejecutarse una sola vez por comando SQL (trigger a nivel de sentencia) o por cada línea afectada por un comando SQL (trigger a nivel de registro).
Extensions
Las extensiones son librerías que se agregan funcionalidades especiales, existen tres formas diferentes de extensiones para PostgreSQL.
1.-Extensiones disponibles en el Servidor PostgreSQL: Este grupo de extensiones son un grupo de extensiones habilitadas en nuestro servidor, están listas para poder ser instaladas y utilizadas cuando así lo necesitemos.
2.-Extensiones PostgreSQL de la web oficial: Existen otras extensiones además de las que tenemos disponibles en nuestro Servidor PostgreSQL. Estas extensiones las podemos encontrar en la web oficial de Postgres, en software catalogue – postgresql extensions y se implementan con unos sencillos pasos. Todas las extensiones que disponemos en la web oficial son Open Source, es decir, son gratis.
3.-Extensiones PostgreSQL encontradas en Internet: Estas no son las únicas extensiones de las que podemos disponer y utilizar en nuestro Servidor PostgreSQL, hay muchas otras extensiones disponibles en internet.
Foreign Data wrappers
Los Foreign Data Wrappers (FDW) son una característica clave de PostgreSQL que permite acceder y consultar datos almacenados en fuentes de datos externas como si fueran tablas nativas de PostgreSQL. Los FDW son bibliotecas que pueden comunicarse con una fuente de datos externa, ocultando los detalles de la conexión a la fuente de datos y la obtención de datos de ella. PostgreSQL incluye varios FDW integrados, como postgres_fdw para acceder a otras bases de datos PostgreSQL y file_fdw para leer datos de archivos.
Languages
permite escribir funciones definidas por el usuario en lenguajes procedurales1. Estos lenguajes permiten ejecutar comandos SQL mediante un lenguaje de sentencias imperativas y uso de funciones, dando mucho más control automático que las sentencias SQL básicas1. Actualmente, hay cuatro lenguajes procedurales disponibles en la distribución estándar de PostgreSQL: PL/pgSQL, PL/Tcl, PL/Perl y PL/Python
Publications
Es un grupo de tablas cuyos cambios de datos se replican a través de la replicación lógica . Una publicación se define en un servidor de replicación físico y es un conjunto de cambios generados a partir de una tabla o un grupo de tablas ,ada publicación existe en una sola base de datos.
Schema
El esquema de búsqueda de PostgreSQL es una lista de esquemas en los que se buscarán los objetos de la base de datos. El primer esquema en la ruta de búsqueda de esquema se llama esquema actual. Cuando se crea un nuevo objeto sin especificar el nombre del esquema, PostgreSQL también utiliza el esquema actual para el nuevo objeto1. La función current_schema() devuelve el esquema actual. Para ver la ruta de búsqueda de esquema actual, se utiliza el comando SHOW search_path; en la herramienta psql.
Subcriptions
son una forma de replicación lógica en la que los cambios en una base de datos se envían a otra base de datos. Para crear una suscripción, se utiliza el comando CREATE SUBSCRIPTION. Este comando agrega una nueva suscripción de replicación lógica.
Creacion de una base de datos espacial con comandos
CREATE DATABASE geoserver_preprod
WITH
OWNER = user_geo_preprod
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
TABLESPACE = pg_default
CONNECTION LIMIT = -1
IS_TEMPLATE = False;
La expresión CREATE DATABASE geoserver_preprod se utiliza para crear una nueva base de datos en PostgreSQL con el nombre "geoserver_preprod". Aquí está la explicación de cada parte de esta expresión:
WITH: Inicia una lista de opciones y configuraciones específicas para la creación de la base de datos.
OWNER = user_geo_preprod: Especifica el propietario de la base de datos. En este caso, la base de datos será propiedad del usuario llamado "user_geo_preprod".
ENCODING = 'UTF8': Establece la codificación de caracteres de la base de datos. En este caso, se utiliza la codificación UTF-8, que es comúnmente utilizada para admitir una amplia gama de caracteres internacionales.
LC_COLLATE = 'en_US.UTF-8': Define la configuración de ordenación (collation) para la base de datos. En este ejemplo, se establece en "en_US.UTF-8", indicando una configuración de ordenación en inglés de los Estados Unidos con codificación UTF-8.
LC_CTYPE = 'en_US.UTF-8': Define la configuración de tipo de caracteres (ctype) para la base de datos. Al igual que LC_COLLATE, se establece en "en_US.UTF-8".
TABLESPACE = pg_default: Indica el espacio de tabla (tablespace) en el cual se almacenarán los objetos de la base de datos. En este caso, se utiliza el espacio de tabla predeterminado llamado "pg_default".
CONNECTION LIMIT = -1: Establece el límite de conexiones para la base de datos. Un valor de -1 significa que no hay límite en la cantidad de conexiones simultáneas permitidas.
IS_TEMPLATE = False: Indica si la base de datos se utilizará como una plantilla. En este caso, se establece en "False", lo que significa que la base de datos no se utilizará como una plantilla.
En resumen, la expresión CREATE DATABASE geoserver_preprod ... crea una nueva base de datos llamada "geoserver_preprod" con ciertas configuraciones, incluyendo el propietario, la codificación de caracteres, la configuración de ordenación, el espacio de tabla, el límite de conexiones y la indicación de si es o no una plantilla. Estas configuraciones son importantes para adaptar la base de datos a las necesidades específicas del entorno y las aplicaciones que la utilizarán.
Creacion de extenciones para postgis
CREATE EXTENSION IF NOT EXISTS address_standardizer
SCHEMA public
VERSION "3.2.6";
SCHEMA public
VERSION "3.2.6";
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch
SCHEMA public
VERSION "1.1";
CREATE EXTENSION IF NOT EXISTS plpgsql
SCHEMA pg_catalog
VERSION "1.0";
CREATE EXTENSION IF NOT EXISTS postgis
SCHEMA public
VERSION "3.2.6";
CREATE EXTENSION IF NOT EXISTS postgis_raster
SCHEMA public
VERSION "3.2.6";
CREATE EXTENSION IF NOT EXISTS postgis_sfcgal
SCHEMA public
VERSION "3.2.6";
CREATE EXTENSION IF NOT EXISTS postgis_topology
SCHEMA topology
VERSION "3.2.6";
CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder
SCHEMA tiger
VERSION "3.2.6";
Para verificar los resultados, puede ejecutar la consulta SQL que se muestra en el siguiente ejemplo, que enumera las extensiones y sus propietarios.
SELECT n.nspname AS "Name",
pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;
List of schemas
Name | Owner
--------------+-----------
public | postgres
tiger | rdsadmin
tiger_data | rdsadmin
topology | rdsadmin
(4 rows)
Transfiera la propiedad de las extensiones.
Use las declaraciones de ALTER SCHEMA para transferir la propiedad de los esquemas al rol gis_admin.
ALTER SCHEMA tiger OWNER TO gis_admin;
ALTER SCHEMA
ALTER SCHEMA tiger_data OWNER TO gis_admin;
ALTER SCHEMA
ALTER SCHEMA topology OWNER TO gis_admin;
ALTER SCHEMA
Si desea confirmar el cambio de propiedad, ejecute la siguiente consulta SQL. O bien, puede utilizar el metacomando \dn de la línea de comandos psql.
SELECT n.nspname AS "Name",
pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;
List of schemas
Name | Owner
--------------+---------------
public | postgres
tiger | gis_admin
tiger_data | gis_admin
topology | gis_admin
(4 rows)
Transfiera la propiedad de los objetos de PostGIS
Use la siguiente función para transferir la propiedad de los objetos de PostGIS al rol gis_admin. Ejecute la siguiente declaración desde el símbolo del sistema psql para crear la función.
CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$;
CREATE FUNCTION
A continuación, ejecute la siguiente consulta para ejecutar la función exec que, a su vez, ejecuta las instrucciones y altera los permisos.
SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO gis_admin;')
FROM (
SELECT nspname, relname
FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname in ('tiger','topology') AND
relkind IN ('r','S','v') ORDER BY relkind = 'S')
s;
Pruebe las extensiones
Para evitar tener que especificar el nombre del esquema, añada el esquema tiger a la ruta de búsqueda usando el siguiente comando.
SET search_path=public,tiger;
SET
Pruebe el esquema tiger usando la siguiente instrucción SELECT.
SELECT address, streetname, streettypeabbrev, zip
FROM normalize_address('1 Devonshire Place, Boston, MA 02109') AS na;
address | streetname | streettypeabbrev | zip
---------+------------+------------------+-------
1 | Devonshire | Pl | 02109
(1 row)
Para obtener más información sobre esta extensión, consulte Tiger Geocoder en la documentación de PostGIS.
Pruebe el acceso al esquema topology usando la siguiente instrucción SELECT. Esto llama a la función createtopology para registrar un nuevo objeto de topología (my_new_topo) con el identificador de referencia espacial especificado (26986) y la tolerancia predeterminada (0,5). Para obtener más información, visite CreateTopology en la documentación de PostgreSQL.
SELECT topology.createtopology('my_new_topo',26986,0.5);
createtopology
----------------
1
(1 row)
Actualice la extensión de PostGIS
Cada nueva versión de PostgreSQL admite una o más versiones de la extensión de PostGIS compatibles con esa versión. La actualización del motor de PostgreSQL a una nueva versión no actualiza automáticamente la extensión de PostGIS. Antes de actualizar el motor de PostgreSQL, normalmente se actualiza PostGIS a la versión más reciente disponible para la versión actual de PostgreSQL. Para obtener más información, consulte Versiones de extensión PostGIS.
Después de actualizar el motor de PostgreSQL, vuelva a actualizar la extensión de PostGIS a la versión compatible con la versión del motor de PostgreSQL recién actualizada. Para obtener más información sobre la actualización del motor PostgreSQL, consulte Cómo realizar una actualización de versión principal.
Puede comprobar si hay disponibles actualizaciones de la versión de la extensión PostGIS en su instancia de base de datos de RDS para PostgreSQL en cualquier momento. Para ello, ejecute el siguiente comando. Esta función está disponible con PostGIS 2.5.0 y versiones posteriores.
SELECT postGIS_extensions_upgrade();
Si su aplicación no es compatible con la última versión de PostGIS, puede instalar una versión anterior de PostGIS que esté disponible en su versión principal de la siguiente manera.
CREATE EXTENSION postgis VERSION "2.5.5";
Si desea actualizar a una versión específica de PostGIS desde una versión anterior, también puede utilizar el siguiente comando.
ALTER EXTENSION postgis UPDATE TO "2.5.5";
Dependiendo de la versión desde la que se actualice, es posible que tenga que volver a utilizar esta función. El resultado de la primera ejecución de la función determina si se necesita una función de actualización adicional. Por ejemplo, eso es lo que ocurre para la actualización de PostGIS 2 a PostGIS 3. Para obtener más información, consulte Actualización de PostGIS 2 a PostGIS 3.
Si actualizó esta extensión para prepararse para una actualización de la versión principal del motor de PostgreSQL, puede continuar con otras tareas preliminares. Para obtener más información, consulte Cómo realizar una actualización de versión principal.
Versiones de extensión PostGIS
Le recomendamos que instale las versiones de todas las extensiones, como PostGIS, como se indica en Versiones para PostgreSQL. Para obtener una lista de las versiones que están disponibles en su versión, utilice el siguiente comando.
SELECT * FROM pg_available_extension_versions WHERE name='postgis';
Actualización de PostGIS 2 a PostGIS 3
A partir de la versión 3.0, la funcionalidad de trama de PostGIS es una extensión separada, postgis_raster. Esta extensión tiene su propia ruta de instalación y actualización. Esto elimina del núcleo docenas de funciones, tipos de datos y otros artefactos necesarios para el procesamiento de imágenes de trama desde la extensión postgis principal. Esto significa que si su caso de uso no requiere procesamiento de tramas, no es necesario que instale la extensión postgis_raster.
En el siguiente ejemplo de actualización, el primer comando de actualización extrae la funcionalidad de trama en la extensión postgis_raster. Luego, se requiere un segundo comando de actualización para actualizar postgres_raster a la nueva versión.
Para actualizar de PostGIS 2 a PostGIS 3
Identifique la versión predeterminada de PostGIS que está disponible para la versión de PostgreSQL en su Instancia de base de datos RDS para PostgreSQL. Para ello, ejecute la siguiente consulta.
SELECT * FROM pg_available_extensions
WHERE default_version > installed_version;
name | default_version | installed_version | comment
---------+-----------------+-------------------+------------------------------------------------------------
postgis | 3.1.4 | 2.3.7 | PostGIS geometry and geography spatial types and functions
(1 row)
Identifique las versiones de PostGIS instaladas en cada base de datos en la instancia de base de datos de RDS para PostgreSQL. En otras palabras, consulte la base de datos de cada usuario de la siguiente manera.
SELECT
e.extname AS "Name",
e.extversion AS "Version",
n.nspname AS "Schema",
c.description AS "Description"
FROM
pg_catalog.pg_extension e
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace
LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid
AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
WHERE
e.extname LIKE '%postgis%'
ORDER BY
1;
Name | Version | Schema | Description
---------+---------+--------+---------------------------------------------------------------------
postgis | 2.3.7 | public | PostGIS geometry, geography, and raster spatial types and functions
(1 row)
Esta falta de correspondencia entre la versión predeterminada (PostGIS 3.1.4) y la versión instalada (PostGIS 2.3.7) significa que debe actualizar la extensión de PostGIS.
ALTER EXTENSION postgis UPDATE;
ALTER EXTENSION
WARNING: unpackaging raster
WARNING: PostGIS Raster functionality has been unpackaged
Ejecute la siguiente consulta para comprobar que la funcionalidad ráster ahora está en su propio paquete.
SELECT
probin,
count(*)
FROM
pg_proc
WHERE
probin LIKE '%postgis%'
GROUP BY
probin;
probin | count
--------------------------+-------
$libdir/rtpostgis-2.3 | 107
$libdir/postgis-3 | 487
(2 rows)
El resultado muestra que aún hay una diferencia entre las versiones. Las funciones de PostGIS son de la versión 3 (postgis-3), mientras que las funciones ráster (rtpostgis) son de la versión 2 (rtpostgis-2.3). Para completar la actualización, vuelva a ejecutar el comando de actualización, como se indica a continuación.
postgres=> SELECT postgis_extensions_upgrade();
Puede ignorar los mensajes de advertencia sin problemas. Vuelva a ejecutar la siguiente consulta para comprobar que la actualización se ha completado. La actualización se completa cuando en PostGIS y en todas las extensiones relacionadas deja de aparecer una marca que indica que deben actualizarse.
SELECT postgis_full_version();
Utilice la siguiente consulta para ver el proceso de actualización completado y las extensiones empaquetadas por separado, y compruebe que las versiones coinciden.
SELECT
e.extname AS "Name",
e.extversion AS "Version",
n.nspname AS "Schema",
c.description AS "Description"
FROM
pg_catalog.pg_extension e
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace
LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid
AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
WHERE
e.extname LIKE '%postgis%'
ORDER BY
1;
Name | Version | Schema | Description
----------------+---------+--------+---------------------------------------------------------------------
postgis | 3.1.5 | public | PostGIS geometry, geography, and raster spatial types and functions
postgis_raster | 3.1.5 | public | PostGIS raster types and functions
(2 rows)
El resultado muestra que la extensión de PostGIS 2 se ha actualizado a PostGIS 3 y que ambas, postgisy la extensión postgis_raster ya separada, son de la versión 3.1.5.
Los tipos de datos principales que PostGIS introduce son:
Geometry: Este tipo de dato representa objetos geométricos planos en un espacio euclidiano bidimensional. Puede almacenar puntos, líneas y polígonos, entre otros.
Los datos Geometry se pueden clasificar en varios tipos básicos según la forma geométrica que representan. Aquí hay una clasificación general de los datos Geometry:
Punto (Point): Representa una ubicación específica en el espacio 2D con coordenadas X e Y.
Línea (LineString):Representa una secuencia de segmentos de línea conectados en el espacio 2D.
Polígono (Polygon):Representa una superficie cerrada en el espacio 2D con un borde exterior y, opcionalmente, agujeros en el interior.
Colección de Puntos (MultiPoint):Representa una colección de puntos.
Colección de Líneas (MultiLineString): Representa una colección de líneas.
Colección de Polígonos (MultiPolygon):Representa una colección de polígonos.
Colección de Geometrías (GeometryCollection):Representa una colección de geometrías de diferentes tipos.
Estos tipos de datos Geometry permiten representar una amplia variedad de objetos geométricos en un sistema de coordenadas bidimensional. Cada tipo tiene sus propias características y puede utilizarse para modelar diferentes aspectos del mundo geoespacial.
Geography: Similar a Geometry, pero está diseñado para almacenar datos geográficos en lugar de geométricos. La principal diferencia es que Geography utiliza coordenadas geográficas (latitud y longitud) en lugar de coordenadas cartesianas.
Esto significa que Geography tiene en cuenta la curvatura de la Tierra y permite trabajar con datos en un espacio tridimensional. Aquí hay una clasificación de los tipos de datos Geography en PostGIS:
Geografía Puntual (Point):Representa una ubicación específica en el espacio 2D, utilizando coordenadas geográficas (latitud y longitud).
Geografía de Línea (LineString):Representa una secuencia de segmentos de línea conectados en el espacio 2D, utilizando coordenadas geográficas.
Geografía de Polígono (Polygon): Representa una superficie cerrada en el espacio 2D con un borde exterior y, opcionalmente, agujeros en el interior, utilizando coordenadas geográficas.
Colección de Geografías Puntuales (MultiPoint): Representa una colección de puntos, cada uno con sus propias coordenadas geográficas.
Colección de Geografías de Líneas (MultiLineString): Representa una colección de líneas, cada una definida por coordenadas geográficas.
Colección de Geografías de Polígonos (MultiPolygon): Representa una colección de polígonos, cada uno definido por coordenadas geográficas.
Colección de Geografías (GeographyCollection): Representa una colección de geometrías de diferentes tipos, cada una definida por coordenadas geográficas.
Estos tipos de datos Geography permiten representar y trabajar con datos geográficos del mundo real en sistemas de información geográfica. Al utilizar coordenadas geográficas, PostGIS facilita el manejo de la superficie curva de la Tierra en cálculos espaciales y análisis geográficos.
Box2D:En el contexto de PostGIS, el tipo de datos Box2D se utiliza para representar cuadros delimitadores bidimensionales (Bounding Box 2D), que son rectángulos que encierran una región en el espacio bidimensional. Estos cuadros delimitadores son comúnmente utilizados para describir la extensión espacial de un conjunto de datos geográficos. A continuación, se presenta una descripción y ejemplos de cómo se clasifican los datos Box2D en PostGIS:
Definición de una tabla con datos Box2D:
Puedes definir una tabla que incluya una columna de tipo Box2D de la siguiente manera:
CREATE TABLE areas (
id SERIAL PRIMARY KEY,
nombre VARCHAR(100),
limite BOX2D
);
En esta tabla llamada areas, hay una columna llamada limite que utiliza el tipo de datos Box2D para almacenar información sobre la extensión espacial de una determinada área.
Inserción de datos en la tabla:
INSERT INTO areas (nombre, limite)
VALUES ('Área A', 'BOX(30 10, 40 40)');
En este ejemplo, se está insertando un área llamada 'Área A' con un cuadro delimitador definido por las coordenadas extremas (30, 10) y (40, 40).
Consulta utilizando datos Box2D:
Puedes realizar consultas espaciales basadas en los cuadros delimitadores. Por ejemplo, para encontrar todas las áreas que intersectan con un cuadro delimitador específico:
SELECT *
FROM areas
WHERE limite && 'BOX(35 15, 45 35)'::BOX2D;
Esto devolvería todas las áreas cuyos cuadros delimitadores (limite) tienen una intersección con el cuadro delimitador especificado.
Creacion de tablas
Puedes crear una tabla en PostGIS utilizando el comando CREATE TABLE en PostgreSQL, y luego especificar las columnas, incluyendo aquellas que utilizarán tipos de datos espaciales de PostGIS como Geometry o Geography. Aquí tienes un ejemplo simple de cómo puedes hacerlo:
CREATE TABLE public.manzanas(
codigo serial NOT NULL PRIMARY KEY UNIQUE,
zona varchar(6),
viviendas nuemeric,
geom geometry(Polygon,4326)
);
El codigo serial NOT NULL PRIMARY KEY UNIQUE: Se crea una columna llamada "codigo" con el tipo de datos serial. Este tipo de datos genera automáticamente valores únicos y secuenciales para cada fila. NOT NULL especifica que la columna no puede contener valores nulos. PRIMARY KEY establece la columna como clave primaria, lo que significa que contiene valores únicos que identifican de manera única cada fila. UNIQUE también indica que los valores en esta columna deben ser únicos.
zona varchar(6): Se crea una columna llamada "zona" con el tipo de datos varchar(6). Esto significa que la columna puede contener cadenas de texto de hasta 6 caracteres.
viviendas numeric: Se crea una columna llamada "viviendas" con el tipo de datos numeric. Esto es un tipo numérico que puede almacenar números decimales.
geom geometry(Polygon, 4326): Se crea una columna llamada "geom" con el tipo de datos geometry(Polygon, 4326). Esta columna se utilizará para almacenar geometrías de tipo polígono (Polygon) en un sistema de coordenadas geográficas (4326, que es el sistema WGS 84).
En resumen, la expresión CREATE TABLE public.manzanas(...) crea una tabla llamada "manzanas" con cuatro columnas: "codigo" como clave primaria autoincremental, "zona" para almacenar códigos de zona, "viviendas" para almacenar información numérica sobre viviendas, y "geom" para almacenar geometrías poligonales en un sistema de coordenadas geográficas. Estas columnas proporcionan una estructura básica para almacenar información sobre manzanas urbanas, incluidos códigos, zonas, datos numéricos y geometría espacial.
Otro ejemplo mas detallado de como crear la tabla :
CREATE TABLE IF NOT EXISTS public."Puertos"
(
id integer NOT NULL DEFAULT nextval('"Puertos_id_seq"'::regclass),
geom geometry(PointZ,4326),
"Name" character varying COLLATE pg_catalog."default",
"ID" integer,
"X" double precision,
"Y" double precision,
CONSTRAINT "Puertos_pkey" PRIMARY KEY (id)
)
La expresión CREATE TABLE IF NOT EXISTS public."Puertos" se utiliza para crear una nueva tabla llamada "Puertos" en el esquema público ("public") de una base de datos PostgreSQL. Aquí está la explicación de cada parte:
CREATE TABLE: Es una declaración en SQL que se utiliza para crear una nueva tabla en una base de datos.
IF NOT EXISTS: Esta cláusula le dice a PostgreSQL que solo debe crear la tabla si aún no existe. Si ya existe una tabla con el mismo nombre, esta declaración no tiene ningún efecto y PostgreSQL no generará un error.
public."Puertos": Aquí se especifica el nombre completo de la tabla que se va a crear. public es el nombre del esquema en el que se creará la tabla (en PostgreSQL, las tablas se organizan en esquemas), y "Puertos" es el nombre de la tabla.
La expresión id integer NOT NULL DEFAULT nextval (' "Puertos_id_seq" ' :: regclass ) se utiliza comúnmente al definir una columna llamada "id" en una tabla en PostgreSQL. Vamos a desglosar cada parte de esta expresión:
id integer: Define una columna llamada "id" con un tipo de datos integer (entero de 32 bits).
NOT NULL: Indica que la columna "id" no puede contener valores nulos, es decir, siempre debe tener un valor.
DEFAULT nextval('"Puertos_id_seq"'::regclass): Establece un valor predeterminado para la columna "id". En este caso, el valor predeterminado se obtiene llamando a la función nextval() para la secuencia llamada "Puertos_id_seq".
nextval('"Puertos_id_seq"'::regclass): Utiliza la función nextval() para obtener el próximo valor de la secuencia con nombre "Puertos_id_seq". La notación '"Puertos_id_seq"' se utiliza para indicar que el nombre de la secuencia está entre comillas dobles, lo que podría ser necesario si el nombre de la secuencia contiene mayúsculas, caracteres especiales o espacios.
La expresión geom geometry(PointZ, 4326) se utiliza al definir una columna llamada "geom" en una tabla en PostgreSQL con el soporte de PostGIS. Vamos a desglosar cada parte de esta expresión:
geom: Es el nombre de la columna. Puedes cambiar esto por el nombre que desees para tu columna.
geometry(PointZ, 4326): Esta parte define el tipo de datos de la columna "geom" utilizando el tipo de datos geometry proporcionado por PostGIS.
PointZ: Indica que la columna almacenará objetos geométricos de tipo "PointZ". En el contexto de PostGIS, esto significa que se trata de puntos tridimensionales, ya que la "Z" se refiere a la coordenada de elevación.
4326: Es el código EPSG (Sistema de Referencia Espacial de Coordenadas) que especifica la proyección espacial o sistema de coordenadas utilizado. En este caso, el código "4326" se refiere al sistema de coordenadas geográficas WGS 84, que es comúnmente utilizado para representar coordenadas de latitud y longitud en grados decimales.
Entonces, la expresión geom geometry(PointZ, 4326) indica que la columna "geom" almacenará puntos tridimensionales con coordenadas geográficas en el sistema de referencia WGS 84. Esto es útil cuando necesitas representar ubicaciones en un espacio tridimensional, como coordenadas de latitud, longitud y elevación.
La expresión "Name" character varying COLLATE pg_catalog."default" se utiliza para definir una columna llamada "Name" en una tabla en PostgreSQL. Aquí hay una explicación de cada parte de esta expresión:
"Name": Es el nombre de la columna. El uso de comillas dobles permite incluir mayúsculas, minúsculas y espacios en el nombre de la columna. Sin embargo, es una práctica común evitar nombres de columna con mayúsculas y espacios para evitar problemas de sintaxis y convenciones de nomenclatura.
character varying: Indica el tipo de datos de la columna. En este caso, se está utilizando el tipo de datos character varying, que es una cadena de caracteres variable. Es similar al tipo de datos VARCHAR en otros sistemas de bases de datos.
COLLATE pg_catalog."default": Define la configuración de ordenación (collation) para la columna. La cláusula COLLATE se utiliza para especificar cómo se ordenarán y compararán los datos de texto en la columna. En este caso, se está utilizando la configuración de ordenación predeterminada del catálogo de PostgreSQL (pg_catalog) con el nombre "default".
La expresión CONSTRAINT "Puertos_pkey" PRIMARY KEY (id) se utiliza al definir una restricción de clave primaria en una tabla en PostgreSQL. Aquí está la explicación de cada parte de esta expresión:
CONSTRAINT "Puertos_pkey": Define una restricción (constraint) y le asigna un nombre. En este caso, la restricción se llama "Puertos_pkey". Los nombres de las restricciones son opcionales, pero asignarles un nombre explícito puede ser útil para referenciarlas posteriormente o para darles un nombre más significativo.
PRIMARY KEY: Especifica que la restricción es una clave primaria. Una clave primaria es un conjunto de columnas que identifican de manera única cada fila en una tabla.
(id): Indica la columna o conjunto de columnas que forman la clave primaria. En este caso, la clave primaria se basa en la columna "id".
Entonces, la expresión CONSTRAINT "Puertos_pkey" PRIMARY KEY (id) se traduce como "La restricción llamada 'Puertos_pkey' establece que la columna 'id' es la clave primaria de la tabla". Esta restricción garantiza que los valores en la columna "id" serán únicos para cada fila en la tabla y que no pueden ser nulos. La clave primaria proporciona una forma de identificar de manera única cada fila en la tabla, lo que es esencial para mantener la integridad de los datos y facilitar las relaciones entre tablas en la base de datos.
Nombre "geom" para la columna
La elección del nombre "geom" para el campo que almacena datos geométricos en una base de datos, como en el caso de geom geometry(Polygon, 4326), no tiene una razón técnica específica y puede variar según las convenciones o preferencias del desarrollador o de la comunidad.
El uso del nombre "geom" es una convención común en muchos entornos y bibliotecas relacionadas con sistemas de información geográfica (SIG) y bases de datos espaciales. La elección del nombre a menudo se basa en la abreviatura de la palabra "geometría". En contextos de SIG y bases de datos espaciales, el término "geom" es corto, fácil de recordar y se ha vuelto casi una especie de estándar informal.
Por ejemplo, en PostGIS, una extensión de PostgreSQL para soporte espacial, es común ver el uso de "geom" para representar columnas que almacenan geometrías espaciales. Sin embargo, vale la pena señalar que no hay una regla estricta y puedes elegir el nombre que consideres más apropiado para tu caso particular. La coherencia en la nomenclatura puede facilitar la comprensión del esquema de la base de datos y las consultas, pero en última instancia, la elección del nombre es una cuestión de preferencia y conveniencia.
Ejemplo de creacio de campos geometricos
A continuación, te proporcionaré un ejemplo de cómo puedes crear campos geométricos de tipo Point, LineString y Polygon en una tabla de PostGIS.
Supongamos que quieres crear una tabla llamada "lugares" con tres campos geométricos para almacenar información sobre diferentes tipos de lugares:
CREATE TABLE lugares (
id SERIAL PRIMARY KEY,
nombre VARCHAR(100),
ubicacion_point GEOMETRY(Point, 4326),
ruta_linestring GEOMETRY(LineString, 4326),
area_polygon GEOMETRY(Polygon, 4326)
);
En este ejemplo:
id SERIAL PRIMARY KEY: Crea una columna llamada "id" que es una clave primaria autoincremental.
nombre VARCHAR(100): Crea una columna llamada "nombre" para almacenar nombres de lugares como texto.
ubicacion_point GEOMETRY(Point, 4326): Crea una columna llamada "ubicacion_point" para almacenar puntos en el sistema de coordenadas geográficas WGS 84 (4326).
ruta_linestring GEOMETRY(LineString, 4326): Crea una columna llamada "ruta_linestring" para almacenar líneas (LineString) en el sistema de coordenadas WGS 84.
area_polygon GEOMETRY(Polygon, 4326): Crea una columna llamada "area_polygon" para almacenar polígonos en el sistema de coordenadas WGS 84.
Luego, puedes insertar datos en la tabla utilizando las funciones de PostGIS. Aquí tienes un ejemplo de cómo insertar un lugar, una ruta y un área:
-- Insertar un lugar (punto)
INSERT INTO lugares (nombre, ubicacion_point)
VALUES ('Punto de Interés', ST_GeomFromText('POINT(-74.005972 40.712776)', 4326));
-- Insertar una ruta (línea)
INSERT INTO lugares (nombre, ruta_linestring)
VALUES ('Ruta Escénica', ST_GeomFromText('LINESTRING(-74.005972 40.712776, -73.985428 40.748817, -73.939615 40.744234)', 4326));
-- Insertar un área (polígono)
INSERT INTO lugares (nombre, area_polygon)
VALUES ('Parque Nacional', ST_GeomFromText('POLYGON((-74.005972 40.712776, -73.985428 40.748817, -73.939615 40.744234, -74.005972 40.712776))', 4326));
Estos son ejemplos básicos y puedes ajustarlos según tus necesidades específicas. PostGIS ofrece muchas funciones y capacidades avanzadas para trabajar con datos geoespaciales en PostgreSQL.
Principales funciones espaciales en PostGIS
Funciones para cálculo de atributos geométricos
1. ST_Area. Devuelve el área de un polígono o multipolígono. Debe pasarse la columna que contiene la geometría como parámetro. Para geometrías no poligonales retorna valores de 0. Las unidades de medida corresponden a las que contiene el sistema de referencia de coordenadas de la geometría.
SELECT ST_Area(geom) AS area
FROM datos.poligonos;
2. ST_Perimeter. Devuelve la longitud del perímetro de un polígono o multipolígono. Debe pasarse la columna que contiene la geometría como parámetro. Para geometrías no poligonales retorna valores de 0. Las unidades de medida corresponden a las que contiene el sistema de referencia de coordenadas de la geometría.
SELECT ST_Perimeter(geom) AS perímetro
FROM datos.poligonos;
3. ST_Length. Devuelve la longitud bidimensional de una geometría de tipo línea o multilínea. Debe pasarse la columna que contiene la geometría como parámetro. Para geometrías no poligonales retorna valores de 0. Las unidades de medida corresponden a las que contiene el sistema de referencia de coordenadas de la geometría.
SELECT ST_Length(geom) AS longitud
FROM datos.lineas;
Funciones de PostGIS para construcción o creación de geometrías
4. ST_MakePoint. Permite crear objetos de tipo punto de 2 dimensiones (valores x, y), 3 dimensiones (x, y, z) i 4 dimensiones (x, y, z, m).
SELECT ST_MakePoint(10 15);
SELECT ST_MakePoint(-3.7018, 40.3185);
5. ST_MakeLine. Permite crear una geometría de tipo línea en base a puntos. Existen diversas metodologías.
La más simple:
SELECT ST_MakeLine(geom) AS linea
FROM datos.puntos;
De forma ordenada en base a un campo numérico que identifique el orden del trazado de la línea:
SELECT ST_MakeLine(id_puntos.geom) AS linea
FROM (
SELECT puntos.id, geom
FROM datos.puntos
ORDER BY puntos.id
) AS id_puntos ;
O bien mediante un array de puntos:
SELECT ST_MakeLine(
ARRAY[
ST_MakePoint(5,2),
ST_MakePoint(4,5),
ST_MakePoint(8,10)
]
);
6. ST_MakePolygon. Permite construir una geometría de tipo polígono en base a una geometría de tipo línea. La geometría lineal debe ser cerrada, es decir, que su nodo inicial y final sean coincidentes.
SELECT ST_MakePolygon(
ST_GeomFromText(
'LINESTRING(10 10, 15 10, 20 15, 10 15, 10 10)'
)
);
7. ST_GeomFromText. Devuelve un objeto geométrico en base a una expresión en formato WKT (Well Known Text) que lo defina.
SELECT ST_GeomFromText('POINT(20 20)')
SELECT ST_GeomFromText('LINESTRING(-5 10, 10 10, 10 15, 20 40, -10 20)')
SELECT ST_GeomFromText('POLYGON((10 10, 15 10, 10 15, 5 5, 10 10))')
Si quieres conocer más acerca del formato de codificación de geometrías Well Known Text puedes echar un vistazo a este artículo en el que hablamos sobre ello.
8. AddGeometryColumn y DropGeometryColumn. Permiten crear o eliminar la columna que contiene el objeto geográfico de una tabla. Debe especificarse el esquema de la tabla, el nombre del campo, el SRID o sistema de coordenadas, el tipo de geometría y sus dimensiones.
SELECT AddGeometryColumn('datos', 'nuevospuntos', 'geom', 25830, 'POINT', 2)
SELECT DropGeometryColumn('datos', 'nuevospuntos', 'geom')
Funciones de PostGIS para análisis espacial
9. ST_Buffer. Devuelve una geometría que representa todos aquellos puntos comprendidos dentro de un radio determinado desde un objeto geométrico dado. Los cálculos se basarán en el SRID de la geometría.
SELECT ST_Buffer(geom, 750)
FROM datos.lineas;
st_buffer postgis
10. ST_SymDifference. Devuelve una geometría que representa las partes de los objetos de la tabla A y la tabla B que no se interescan. Recibe el nombre de diferencia simétrica porque da exactamente lo mismo pasar como primer argumento una u otra tabla, es decir, ST_SymDifference(A,B) = ST_SymDifference(B,A).
SELECT ST_SymDifference(
parques.geom,
ST_Buffer(papeleras.geom, 500))
FROM
datos.poligonos as parques,
datos.puntos as papeleras;
11. ST_Intersection. Devuelve la geometría que representa los puntos comunes entre dos geometrías de entrada. Es importante, en este caso, recordar que al tratarse de puntos en común entre ambas capas, se evalúan A y B de la misma manera, es decir, ST_Intersection(A, B) = ST_Intersection(B, A)
SELECT ST_Intersection(
parques.geom,
ST_Buffer(papeleras.geom, 500))
FROM
datos.poligonos as parques,
datos.puntos as papeleras;
12. ST_Union. Devuelve una geometría que representa la unión de dos tablas con datos espaciales.
SELECT ST_Union(lineas.geom, poligonos.geom)
FROM datos.lineas, datos.poligonos;
13. ST_Centroid. Devuelve el centroide calculado a partir de las geometrías de entrada.
SELECT ST_Centroid(poligonos.geom)
FROM datos.poligonos;
14. ST_Envelope. Devuelve una geometría que representa el bounding box de las geometrías que contiene la tabla.
SELECT ST_Envelope(lineas.geom)
FROM datos.lineas;
Funciones booleanas para relaciones entre geometrías
15. ST_Contains. Retorna un valor booleano True para todas aquellas geometrías de B contenidas en A que si, y solo si, ninguno de sus puntos se encuentran fuera de los límites de A. En caso contrario retorna el valor booleano False.
SELECT ST_Contains(poligonos.geom, lineas.geom)
FROM datos.lineas, datos.poligonos;
16. ST_Intersects. Retorna el valor booleano True si las geometrías de A y B comparten algún punto en común. Es decir, si se sobreponen en algún punto del espacio.
SELECT ST_Intersects(poligonos.geom, lineas.geom)
FROM datos.lineas, datos.poligonos;
17. ST_Overlaps. Retorna el valor booleano True si la geometría B se sobrepone a la geometría A, pero B sobrepasa los límites de A. Es decir, si ocupan parte del mismo espacio en las mismas dimensiones pero sin llegar a contenerse la una a la otra.
SELECT ST_Overlaps(poligonos_a.geom, poligonos_b.geom)
FROM datos.poligonos_a, datos.poligonos_b;
18. ST_Touches. Devuelve True si las geometrías de A y B tienen algún punto de sus bordes / límites en común. Para puntos, aplica si sus coordenadas coinciden con algún punto situado en el límite de un polígono.
SELECT ST_Touches(poligonos.geom, lineas.geom)
FROM datos.lineas, datos.poligonos;
19. ST_Covers. Retorna True si ningun punto de B se encuentra fuera de A, es decir, si el área que ocupa A es capaz de cubrir todos los elementos geométricos por completo de B.
SELECT ST_Covers(poligonos.geom, lineas.geom)
FROM datos.lineas, datos.poligonos;
En este punto no nos podemos olvidar de una de las principales funciones de análisis de relación espacial entre tablas: el join espacial. En el siguiente artículo damos todos los detalles para conocer en profundidad cómo llevar a cabo operaciones de join espacial en PostGIS.
Funciones relativas a sistemas de coordenadas y posición
23. ST_Translate. Permite desplazar o mover la geometría, tanto en 2 dimensiones como en 3 dimensiones. Debemos pasar como argumentos la geometría y los valores de desplazamiento en cada eje.
SELECT ST_SetSRID(ST_Point(-3.7018, 40.3185), 4326);
22. ST_Transform. Permite reproyectar las geometrías a un sistema de referencia de coordenadas definido. En nuestro caso reproyectamos del SRID original (EPSG: 25830) de los polígonos de la tabla al EPSG:4326:
SELECT ST_Translate(geom, -0.05, 0.25)
FROM datos.poligonos;
21. ST_SRID. Permite consultar cuál es el SRID de una geometría o conjunto de geometrías. Devuelve una columna en la que muestra el valor del código EPSG del SRID (en nuestro caso devuelve: 25830 que se corresponde al ETRS89 UTM 30N):
SELECT ST_Transform(geom, 4326)
FROM datos.poligonos;
20. ST_SetSRID. Permite establecer / asignar el SRID o Sistema de Referencia de Coordenadas de una geometría o conjunto de geometrías. En este caso estamos asignando el EPSG:4326 (WGS 84) al punto con sus coordenadas definidas:
SELECT ST_SRID(geom)
FROM datos.poligonos;
Funciones para verificación de geometrías
24. GeometryType. Permite conocer el tipo de geometría de un objeto. Retorna un valor que define la tipología dentro de PostGIS, como: ST_Point, ST_Linestring, ST_Polygon, ST_Multipoint…
SELECT ST_GeometryType(geom) as tipo
FROM datos.poligonos;
25. ST_IsValid. Permite conocer si una geometría es válida. Retorna un valor booleano por cada geometría.
SELECT ST_IsValid(geom) as validez
FROM datos.poligonos;
Si por ejemplo quisiéramos conocer cuántas geometrías de una tabla son válidas e inválidas podríamos realizar una consulta del estilo:
SELECT ST_IsValid(geom) as validez,
(select count(ST_IsValid(geom)) as numero)
FROM datos.poligonos
GROUP BY validez
Funciones definidas por el usuario
Supongamos que deseas crear una función que devuelva la longitud total de una línea dada. Aquí está cómo podrías definir esa función:
CREATE OR REPLACE FUNCTION CalcularLongitud(linea geometry(LineString, 4326))
RETURNS float AS $$
DECLARE
longitud_total float;
BEGIN
SELECT ST_Length(linea::geography) INTO longitud_total;
RETURN longitud_total;
END;
$$ LANGUAGE plpgsql;
En este ejemplo:
CREATE OR REPLACE FUNCTION CalcularLongitud(linea geometry(LineString, 4326)) RETURNS float AS $$: Define una función llamada "CalcularLongitud" que toma una línea como parámetro y devuelve un valor de tipo float.
DECLARE longitud_total float;: Declara una variable local llamada "longitud_total" de tipo float.
BEGIN: Marca el inicio del bloque de código de la función.
SELECT ST_Length(linea::geography) INTO longitud_total;: Calcula la longitud total de la línea convirtiendo la geometría a un tipo geográfico y asigna el resultado a la variable "longitud_total".
RETURN longitud_total;: Devuelve el valor calculado como resultado de la función.
END;: Marca el final del bloque de código de la función.
$$ LANGUAGE plpgsql;: Indica que la función está escrita en PL/pgSQL, el lenguaje de procedimientos almacenados de PostgreSQL.
Luego, puedes llamar a esta función como cualquier otra función de PostgreSQL:
-- Llamar a la función con una línea específica
SELECT CalcularLongitud('LINESTRING(-73.986951 40.748817, -74.005972 40.712776)'::geometry);
El siguiente ejemplo simple se llama "aeropuertos" y está escrita en el lenguaje de procedimientos almacenados PL/pgSQL. Aquí está el desglose de la definición de la función:
CREATE OR REPLACE FUNCTION public.aeropuertos(
p_id integer)
RETURNS TABLE(dato geometry)
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
ROWS 1000
AS $BODY$
BEGIN
RETURN QUERY
SELECT ST_Force2D(ST_Transform(geom, 4326)) As geom FROM public."Aeropuertos"
WHERE id > p_id;
END
$BODY$;
CREATE OR REPLACE FUNCTION public.aeropuertos(p_id integer) RETURNS TABLE(dato geometry): Define una función llamada "aeropuertos" que toma un parámetro de entrada p_id de tipo entero y devuelve una tabla con una columna llamada "dato" de tipo geometría.
LANGUAGE 'plpgsql': Indica que el cuerpo de la función está escrito en el lenguaje de procedimientos almacenados PL/pgSQL.
COST 100: Especifica el costo estimado de ejecución de la función. Este valor es utilizado por el planificador de consultas para tomar decisiones sobre la ejecución de la función.
VOLATILE PARALLEL UNSAFE: Establece las propiedades de la función. En este caso, la función se considera volátil (puede devolver resultados diferentes con la misma entrada) y no segura para la ejecución en paralelo.
ROWS 1000: Indica el número aproximado de filas que se espera que la función devuelva. Esto también ayuda al planificador de consultas a tomar decisiones sobre la ejecución.
AS $BODY$ ... $BODY$: Contiene el cuerpo de la función, escrito en PL/pgSQL. Aquí, el cuerpo comienza con un bloque BEGIN y termina con un bloque END. Dentro del bloque, hay una consulta RETURN QUERY que selecciona geometrías de la tabla "Aeropuertos" que tienen un "id" mayor que el parámetro de entrada p_id. La geometría se transforma para forzarla a ser 2D y se proyecta en el sistema de coordenadas geográficas (4326).
En resumen, esta función realiza una consulta en la tabla "Aeropuertos" y devuelve geometrías que cumplen con cierta condición (cuando el "id" es mayor que el parámetro de entrada). La geometría se transforma para ser 2D y se proyecta en el sistema de coordenadas geográficas (WGS 84).
Trigger
Un trigger (o desencadenador) en PostgreSQL y PostGIS es un conjunto de instrucciones que se ejecutan automáticamente en respuesta a ciertos eventos en una tabla o vista. En el contexto de PostGIS, los triggers son a menudo utilizados para realizar acciones específicas relacionadas con datos geoespaciales cuando se realizan ciertas operaciones en una tabla.
Aquí hay un ejemplo básico para ilustrar cómo se crea un trigger en PostGIS:
Supongamos que tienes una tabla llamada "lugares" que contiene información sobre lugares, incluida una columna geométrica "geom" que almacena la geometría espacial. Quieres asegurarte de que, cada vez que se inserta o actualiza un registro en la tabla "lugares", también se actualiza automáticamente una columna llamada "area" con el área del lugar.
-- Crear la tabla lugares
CREATE TABLE lugares (
id SERIAL PRIMARY KEY,
nombre VARCHAR(100),
geom GEOMETRY(Point, 4326),
area DOUBLE PRECISION
);
-- Crear la función para calcular el área
CREATE OR REPLACE FUNCTION calcular_area() RETURNS TRIGGER AS $$
BEGIN
NEW.area := ST_Area(NEW.geom);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Crear el trigger que ejecutará la función
CREATE TRIGGER trigger_actualizar_area
BEFORE INSERT OR UPDATE ON lugares
FOR EACH ROW
EXECUTE FUNCTION calcular_area();
En este ejemplo:
Tabla "lugares": Se crea una tabla llamada "lugares" con una columna geométrica "geom" y una columna "area" para almacenar el área del lugar.
Función "calcular_area": Se crea una función llamada "calcular_area" que calcula el área del lugar y actualiza la columna "area" en el nuevo registro. Esta función se define con RETURNS TRIGGER, lo que significa que se puede utilizar como un trigger.
Trigger "trigger_actualizar_area": Se crea un trigger llamado "trigger_actualizar_area" que se ejecutará antes de realizar una inserción (INSERT) o actualización (UPDATE) en la tabla "lugares". Este trigger invoca la función "calcular_area" para actualizar automáticamente la columna "area" con el área del lugar.
Cuando se realiza una inserción o actualización en la tabla "lugares", el trigger ejecuta la función "calcular_area" automáticamente, asegurando que la columna "area" se mantenga actualizada con el área correspondiente a la geometría proporcionada.
Este es un ejemplo básico y los triggers en PostGIS pueden ser más complejos dependiendo de los requisitos específicos del proyecto. Pueden ejecutarse antes o después de ciertos eventos y realizar diversas acciones en respuesta a esos eventos.
url
- https://docs.qgis.org/3.16/es/docs/training_manual/spatial_databases/index.html
- https://www.geomapik.com/analisis-gis/postgis-analisis-espacial-funciones/