Postgresql / Postgis: Creación de una Tabla y asociarla a un Trigger:

En este mes, se verá como crear una Tabla con una Proyección y un Trigger para actualizar algunas columnas en ella.

Creación de una Tabla con una Proyección: Para crear una tabla se usaría una estructura en SQL:

  

Ejemplo:

 

Creación de un Trigger: Para crear un Trigger con su respectiva Función se usaría una estructura SQL:

Entre el BEGIN y el END de la Función del Trigger, se coloca las operaciones de la Función. Para ello se usa las variables New, Old (no son necesarios declararlos) para acceder a los valores de la Tabla asociada al Trigger  de la siguiente manera:

New.Columna

Podría explicarse (a mi entender), New es el Nuevo registro y Columna es la Columna del Nuevo registro a leer el Valor.

Ejemplo:

 

Para más información, pueden leer el siguiente enlace: http://www.postgresql.org/docs/9.1/static/sql-createtrigger.html

Ejemplo de Aplicación:

Este es un ejemplo de aplicación que está dividido en dos partes:

La primera parte será la creación de una Tabla de Puntos que se llamara CCPP_PERU y en ella estarán guardados unos centros poblados del Perú. Esta tabla tendrá una proyección Geográfica en WGS84, teniendo como columnas:

Nombre: nombre de los Centros Poblados, de tipo character varying(35).

long_84: Longitud de los Centros Poblados, de tipo double precision

lat_84: Latitud de los Centros Poblados, de tipo double precision,

dept: nombre del departamento que pertenece el Centros Poblados, de tipo character varying(35).

prov :nombre de la provincia que pertenece el Centro Poblado, de tipo character varying(35).

dist: nombre del distrito que pertenece el Centro Poblado, de tipo character varying(35),

 

gid: PRIMARY KEY de la Tabla CCPP_PERU serial NOT NULL,

the_geom: columna donde estarán los puntos de los Centros Poblados, de tipo geometry.

 

Las Columnas que serán actualizadas por el Trigger serán: dept, prov, dist, the_geom.

 

La segunda parte es la creación del Trigger  con su respectiva Función. Este Trigger permitirá actualizar las columnas dept, prov, dist, the_geom. Para actualizar las columnas dept, prov, dist se usara la Tabla DISTRITOS_PERU donde estarán tres columnas con esta información y dicha tabla estará en la proyección Geográfica en PSAD56. En este enlace pueden descargar un SHP para que lo agreguen en su Base de datos para este ejemplo.

 

Estructura de la Tabla DISTRITOS_PERU

 

Código SQL para la creación de la Tabla:

 

CREATE TABLE "CCPP_PERU"
(
  "nombre" character varying(35), 
  "long_84" double precision,
  "lat_84" double precision,
  "dept" character varying(35),
  "prov" character varying(35),
  "dist" character varying(35),
  "gid" serial NOT NULL,
  "the_geom" geometry,
  CONSTRAINT "CCPP_PERU_pkey" PRIMARY KEY ("gid"),
  CONSTRAINT "enforce_dims_the_geom" CHECK (st_ndims("the_geom") = 2),
  CONSTRAINT "enforce_srid_the_geom" CHECK (st_srid("the_geom") = 4326)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "CCPP_PERU" OWNER TO postgres;

CREATE INDEX "CCPP_PERU_the_geom_gist"
  ON "CCPP_PERU"
  USING gist
  ("the_geom");

Código SQL para la creación del  Trigger:

--INICIO DE LA FUNCION 
CREATE OR REPLACE FUNCTION actualizadepprovdist() Returns "trigger" as $BODY$ 
 DECLARE
 r record; --DECLARO UNA VARIABLE DE TIPO RECORD
  BEGIN
       If (New.Long_84 <>0 and New.Lat_84 <>0) Then --VERIFICACION SI LOS VALORES EN long_84, lat_84 SON DIFERENTES DE CERO (0)
  NEW.the_geom := St_SetSRID(St_MakePoint(New.long_84, New.lat_84),4326); --CREO UNA GEOMETRIA CON LOS DATOS DE LAS COLUMNAS long_84, lat_84

  Execute 'Create View mivista as Select "long_84", "lat_84" , "nombdpto", "nombprov", "nombdist" from "CCPP_PERU", "DISTRITOS_PERU" Where Not St_IsEmpty(St_Intersection (St_SetSRID(St_MakePoint(' ||  New.long_84 ||', ' || New.lat_84 ||'),4326) , St_Transform("DISTRITOS_PERU".the_geom,4326))) and "CCPP_PERU".gid = ' || New.gid ;
  --CREO UNA VISTA LLAMADA mivista DONDE SELECCIONO LAS COLUMNAS long_84, lat_84 DE LA TABLA CCPP_PERU, 
  --LAS COLUMNAS nombdpto, nombprov, nombdist USANDO EL COMANDO EXECUTE

  For r in Select * From mivista --SELECCIONO TODOS LOS RECORDS (ES UN DECIR YA QUE SOLO HAY UN SOLO RECORD) DE LA VISTA mivista
  Loop --ESTE LOOP RECORRE CADA REGISTRO DE LA VISTA mivista
   NEW.dept := r.nombdpto; --ASIGNO EL VALOR DE LA COLUMNA nombdpto HACIA LA COLUMNA dept
   NEW.prov := r.nombprov; --ASIGNO EL VALOR DE LA COLUMNA nombprov HACIA LA COLUMNA prov
   NEW.dist := r.nombdist; --ASIGNO EL VALOR DE LA COLUMNA nombdist HACIA LA COLUMNA dist
  
  End Loop;--FIN DEL LOOP
  Execute 'Drop View mivista';  --BORRO LA VISTA mivista CON EXECUTE
  
       End If;

       RETURN NEW; --RETORNO DE LOS VALORES HACIA EL REGISTRO CREADO
  END;

$BODY$ 
LANGUAGE plpgsql; --SE DECLARA EL TIPO DE LENGUAJE DE LA FUNCION: plpgsql 
--FIN DE LA FUNCION 

--INICIO DEL TRIGGER
CREATE TRIGGER actualizadepprovdist
BEFORE INSERT OR UPDATE ON "CCPP_PERU"
    FOR EACH ROW EXECUTE PROCEDURE actualizadepprovdist();
--FIN DEL TRIGGER

 

La mayoría del código para la creación del Trigger esta explicado. Pero se explicara la Línea:
 

Execute 'Create View mivista as Select "long_84", "lat_84" , "nombdpto", "nombprov", "nombdist" from "CCPP_PERU", "DISTRITOS_PERU" Where Not St_IsEmpty(St_Intersection (St_SetSRID(St_MakePoint(' ||  New.long_84 ||', ' || New.lat_84 ||'),4326) , St_Transform("DISTRITOS_PERU".the_geom,4326))) and "CCPP_PERU".gid = ' || New.gid ;

Explicación:

Con el comando Execute ejecutamos alguna consulta SQL que sea necesaria en la Función del Trigger.

Este Trigger debe leer las coordenadas de las columnas long_84, lat_84 que están en el registro recién insertado en la tabla CCPP_PERU (usando New.Long_84 y New.Lat_84) para intersectarlo con la tabla DISTRITOS_PERU y leer las columnas nombdpto, nombprov, nombdist. Para ello, una forma seria creando una Vista o Tabla (en este ejemplo se usó una Vista) temporal donde estén las columnas long_84, lat_84 de la tabla CCPP_PERU y nombdpto, nombprov, nombdist de la tabla DISTRITOS_PERU:

Create View mivista as Select "long_84", "lat_84" , "nombdpto", "nombprov", "nombdist" from "CCPP_PERU", "DISTRITOS_PERU"

Para parametrizar más la selección, usamos la condición Where donde indicamos propiamente la Intersección, pero creamos (otra vez) un punto desde la columna long_84, lat_84:

St_MakePoint(' ||  New.long_84 ||', ' || New.lat_84 ||')

Donde || sirven para concatenar textos o números, y le asignamos la Proyección Geográfica en WGS84:

St_SetSRID(St_MakePoint(' ||  New.long_84 ||', ' || New.lat_84 ||'),4326)

Y proyectamos la table DISTRITOS_PERU de PSAD56 a WGS84 en la Proyección Geográfica:

St_Transform("DISTRITOS_PERU".the_geom,4326)

Con estos tres pasos, usamos la Intersección:

St_Intersection(geometria1,geometria2)

Como el resultado de St_Intersection(geometria1,geometria2) es un booleano Le decimos que si no es Vacío, nos de el resultado:

Not St_IsEmpty(St_Intersection(geometria1,geometria2))

Además, para asegurar que solo nos dé un registro, le indicamos que solo sea cuando el valor de la columna gid de la tabla CCPP_PERU y del New.gid sean iguales:

and "CCPP_PERU".gid = ' || New.gid

 

Parte del código para la creación de la Vista se ha extendido para fines informativos, pudiéndose acortar el código.

Para ver si se ejecuta el Trigger, escribimos la longitud y latitud (o viceversa) en las columnas long_84, lat_84 en el último registro de la tabla (el que tiene un asterisco en la columna “0”) y presionando luego el botón Refresh de la tabla, veremos que se actualiza las columnas dept, prov, dist, the_geom (la columna nombre es para que el usuario escriba el nombre del Centro Poblado y la columna gid se actualiza cada vez que se ingresa un nuevo registro).

 

 

Esperando que sea de utilidad. Sera hasta el otro mes.