Triggers

From GeoBox

Criação de uma base de dados

Comece por criar uma nova base de dados, chamada cadastro.

Só é necessário preencher:

• o nome da base de dados: cadastro
• escolher o modelo: template_postgis

A escolha do modelo é muito importante.

Criação de uma entidade edifício

Definir uma nova entidade predio, com as características seguintes. Deve-se definir desde logo uma chave primária.

CREATE TABLE predio
(
  id serial NOT NULL,
  nome CHARACTER VARYING(40),
  proprietario CHARACTER VARYING(40),
  codpostal CHARACTER VARYING(9),
  area DOUBLE PRECISION,
  valor DOUBLE PRECISION,
  geometria geometry,
  CONSTRAINT predio_pk PRIMARY KEY (id)
);

Adicionar metainformação

Sempre que se cria uma tabela com colunas do tipo geometry, deve-se adicionar na tabela geometry_columns a meta-informação respectiva.

Pode-se acrescentar a informação através da interface de visualização de dados do pgAdmin III, ou através da seguinte instrução:

INSERT INTO geometry_columns
VALUES ('',  'public',  'predio',  'geometria',  2,  3763,  'POLYGON')

Constrições adicionais

Podem-se (e deve-se) acrescentar algumas restrições que garantam a consistência e integridade dos dados geográficos.

Restrição que impõe que todas as geometria tenham definido o sistema de coordenadas 3763.

ALTER TABLE predio
  ADD CONSTRAINT enforce_srid_geometria CHECK (st_srid(geometria) = 3763);

ALTER TABLE predio
  ADD CONSTRAINT enforce_geotype_geometria CHECK (geometrytype(geometria) = 'POLYGON'::text OR geometria IS NULL);

ALTER TABLE predio
  ADD CONSTRAINT enforce_dims_geometria CHECK (st_ndims(geometria) = 2);

Por fim, pode-se acrescentar mais uma constrição que garanta que todos os polígonos são topologicamente válidos.

ALTER TABLE predio
  ADD CONSTRAINT enforce_is_valid CHECK (st_isvalid(geometria));

Edição

INSERT INTO predio (nome, proprietario, geometria)
VALUES ('Nenúfers', 'Ana', st_geomfromtext('POLYGON((-41400 165700,-40996 165798,-40865 165592,-41168 165392,-41400 165700))', 3763))

Edição com o uDig, Q-GIS ou gvSIG

Acrescente mais prédios com uma ferramenta SIG desktop.

Edição c/ SIG Desktop

Cálculo da área e do valor

SELECT nome, st_area(geometria) AS area, st_area(geometria)*25 AS valor 
FROM predio

Actualizar a tabela com os valores calculados

UPDATE predio
SET area = st_area(geometria),
valor = st_area(geometria) * 25

Triggers

Os triggers são despoletados sempre que se insere, modifica ou apaga alguma linha de uma tabela.

Neste exercício vamos criar fazer com que, sempre que se insere ou modifica um prédio, seja imediatamente calculada a respectiva área e valor, assumindo-se hipoteticamente que cada metro quadrado custa 480,00 €.

Para definir um trigger, é necessário definir duas coisas. O trigger propriamente dito e uma função de trigger. A função de trigger é onde ocorre todo o processamento; o trigger propriamente dito, é que associa a uma tabela a função de trigger, e em que condições a mesma deve ser despoletada.

Primeiro passo

Criar a função de trigger que calcula a área e o valor do prédio.

CREATE OR REPLACE FUNCTION calcula_dados_predio()
  RETURNS TRIGGER AS
$BODY$
   BEGIN      
       NEW.area := st_area(NEW.geometria); 
       NEW.valor := NEW.area * 480;
       RETURN NEW;
   END;
 $BODY$
   LANGUAGE 'plpgsql' VOLATILE

Segundo passo

Definir o trigger, associando a função de trigger anterior à tabela predio, sempre que se insere ou modifica um prédio.

O trigger pode ser definido através do código seguinte.

CREATE TRIGGER actualiza_valor_predio
  BEFORE INSERT OR UPDATE
  ON predio
  FOR EACH ROW
  EXECUTE PROCEDURE calcula_dados_predio();

Alternativamente, preenchendo o diálogo "Novo Trigger" na opção "Novo Objecto", sobre a tabela predio, pode-se definir o mesmo trigger, como é ilustrado na imagem seguinte.

Outro trigger

Existe uma tabela com os polígonos dos códigos postais. Por isso, é possível descobrir automaticamente o código postal de cada predio.

SELECT nome, proprietario, cp4
FROM predio, cttshapefile
WHERE st_contains( the_geom, geometria )

Caso o prédio esteja em mais do que um código postal, é necessário alterar a query anterior.

SELECT nome, proprietario, cp4, st_area(st_intersection(the_geom, geometria))
FROM predio, cttshapefile
WHERE st_intersects( the_geom, geometria )

Criar o trigger

CREATE OR REPLACE FUNCTION preenche_cp()
  RETURNS TRIGGER AS
$BODY$
BEGIN      
SELECT cp4 INTO NEW.codpostal
FROM cttshapefile
WHERE st_contains(the_geom, NEW.geometria);
RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE

CREATE TRIGGER trigger_cp
  BEFORE INSERT OR UPDATE
  ON predio
  FOR EACH ROW
  EXECUTE PROCEDURE preenche_cp();