6bBc0KvR

1CREATE TABLE maps_geometriamunicipio
2(
3    id               serial                       NOT NULL
4        CONSTRAINT maps_geometriamunicipio_pkey
5            PRIMARY KEY,
6    municipio_id     integer                      NOT NULL
7        CONSTRAINT maps_geometriamunicipio_municipio_id_key
8            UNIQUE,
9    geom             geometry(MultiPolygon, 4326) NOT NULL,
10    area_km2         numeric(10, 2),
11    area_ha          numeric(10, 2),
12    cod_ibge_m       varchar(20)                  NOT NULL,
13    data_atualizacao timestamp WITH TIME ZONE     NOT NULL
14);
15
16CREATE TABLE maps_imovelrural
17(
18    id               serial                       NOT NULL
19        CONSTRAINT maps_imovelrural_pkey
20            PRIMARY KEY,
21    cod_ibge_m       varchar(40),
22    fuso             integer,
23    cod_origem       integer,
24    geom             geometry(MultiPolygon, 4326) NOT NULL,
25    incra            varchar(30),
26    sigef            varchar(50),
27    area_incra       varchar(254),
28    area_sigef       varchar(254),
29    car              varchar(254),
30    ccir             varchar(254),
31    cod_ibge_e       double precision,
32    n_certific       varchar(254),
33    uf               varchar(3),
34    area_ha          numeric(10, 2),
35    data_atualizacao timestamp WITH TIME ZONE     NOT NULL
36);
37
38CREATE OR REPLACE FUNCTION update_municipio_area()
39    RETURNS trigger AS
40$$
41BEGIN
42    NEW.area_km2 = ST_Area(NEW.geom::geography) / 1000000;
43    NEW.area_ha = ST_Area(NEW.geom::geography) / 10000;
44    RETURN NEW;
45END;
46$$
47    LANGUAGE 'plpgsql';
48
49DROP TRIGGER IF EXISTS update_municipio_area ON maps_geometriamunicipio;
50CREATE TRIGGER update_municipio_area
51    BEFORE INSERT OR UPDATE
52    ON maps_geometriamunicipio
53    FOR EACH ROW
54EXECUTE PROCEDURE update_municipio_area();
55
56CREATE OR REPLACE FUNCTION update_imovel_area()
57    RETURNS trigger AS
58$$
59BEGIN
60    NEW.area_ha = ST_Area(NEW.geom::geography) / 10000;
61    RETURN NEW;
62END;
63$$
64    LANGUAGE 'plpgsql';
65
66DROP TRIGGER IF EXISTS update_imovel_area ON maps_imovelrural;
67CREATE TRIGGER update_imovel_area
68    BEFORE INSERT OR UPDATE
69    ON maps_imovelrural
70    FOR EACH ROW
71EXECUTE PROCEDURE update_imovel_area();