git remote set-url origin git@gitlab.terras.agr.br:mapbiomas/workspace.git    

createdb -U postgres -h localhost dev_mapbiomas -T mapbiomas

docker exec -it mapbiomas-server-db bash

pg_dump -h localhost -U postgres -d mapbiomas > /mnt/disks/data/arquivos/databasebkps/mapbiomas_20180115.sql

gsutil cp /data/arquivos/databasebkps/mapbiomas_20180115.sql gs://mapbiomas-data/brasil/db_bkps/mapbiomas_20180115.sql

psql -U postgres -h localhost mapbiomas < brasil_territorio.sql  

Ex.: psql -h localhost -U postgres -d dev_mapbiomas_col_3 -c "COPY estatistica_cobertura_infrabuffer TO stdout DELIMITER ',' CSV HEADER;" > estatistica_cobertura_infrabuffer-utf8.csv

ex: psql -h localhost -U postgres -d dev_mapbiomas_col_3 -c "COPY estatistica_cobertura_infrabuffer TO stdout DELIMITER ',' CSV HEADER;" > estatistica_cobertura_infrabuffer.csv

pg_dump -h localhost -U postgres -d mapbiomas --column-inserts --table="classes" --data-only > /mnt/disks/data/classes.sql

Somente dados
pg_dump -h localhost -U postgres -d mapbiomas -t estatisticas -a > /mnt/disks/data/arquivos/databasebkps/mapbiomas_20170425_estatisticas.sql

Somente tabela + dados
pg_dump -h localhost -U postgres -d mapbiomas -t estatisticas > /mnt/disks/data/arquivos/databasebkps/mapbiomas_20170425_estatisticas.sql

pg_dump -h localhost -U postgres -d mapbiomas -t territorios > /mnt/disks/data/arquivos/databasebkps/territorios_20180115.sql

gsutil cp /data/arquivos/databasebkps/mapbiomas_v2_20171213.sql gs://mapbiomas-data/brasil/db_bkps/mapbiomas_v2_20171213.sql

scp root@104.196.9.9:/data/arquivos/databasebkps/territorios_20180115.sql ./

shp2pgsql -s 4326 biomas.shp public.territorio_biomas | psql -U postgres -d mapbiomas

drop table if exists imaflora_landure_introFields;
CREATE TABLE imaflora_landure_introFields
(
    id serial PRIMARY KEY,
    Value numeric, 
    Count numeric,
    cd_mun numeric,
    nm_mun varchar,
    cd_micro numeric,
    nm_micro varchar,
    cd_meso numeric,
    nm_meso varchar,
    cd_uf int,
    nm_uf varchar,
    nm_region varchar,
    biome varchar,
    country varchar,
    landtenure varchar,
    pc_pl varchar,
    tot_mf numeric,
    pl_size varchar,
    areaHa numeric,
    notprotect numeric,
    lrdeficit numeric,
    appdeficit numeric
);
COPY 
    imaflora_landure_introFields(Value, Count, cd_mun, nm_mun, cd_micro, nm_micro, cd_meso, nm_meso, cd_uf, nm_uf, nm_region, biome, country, landtenure, pc_pl, tot_mf, pl_size, areaHa, notprotect, lrdeficit, appdeficit)
FROM '/mnt/disks/data/mapbiomas_base_car/landtenure_infoFields.csv' DELIMITER ',' CSV HEADER;

drop table if exists imaflora_estatistica_transicao;
CREATE TABLE imaflora_estatistica_transicao
(
    id serial PRIMARY KEY,
    classe_inicial numeric, 
    classe_final numeric,
    area numeric,
    ano_inicial numeric,
    ano_final numeric,
    territorio numeric,
    created timestamp,
    modified timestamp,
    porcentagem numeric
);
COPY 
    imaflora_estatistica_transicao(classe_inicial, classe_final, area, ano_inicial, ano_final, territorio, created, modified, porcentagem)
FROM '/mnt/disks/data/mapbiomas_base_car/mapbiomas_vs_landtenure_estatistica_transicao.csv' DELIMITER ',' CSV HEADER;

drop table estatistica_niveis;
create table estatistica_niveis as 
SELECT st.classe,
    clsi.valor_l1 AS classe_l1,
    clsi.valor_l2 AS classe_l2,
    clsi.valor_l3 AS classe_l3,
    clsi.classe AS classe_desc,
    st.ano::integer AS ano,
    sum(st.area) AS area,
    st.territorio
FROM estatisticas st
    LEFT JOIN classes clsi ON clsi.valor = st.classe
GROUP BY clsi.valor_l1, clsi.valor_l2, clsi.valor_l3, st.classe, clsi.classe, st.ano, st.territorio;

CREATE OR REPLACE FUNCTION map_territorio_inspector(cat TEXT, long FLOAT, lat FLOAT)
RETURNS TABLE (id INT, name VARCHAR, categoria VARCHAR, classe INT, classe_l1 INT, classe_l2 INT, classe_l3 INT, 
            classe_desc VARCHAR, ano INT, area FLOAT) AS $$
BEGIN

    RETURN QUERY SELECT  t.id, t.descricao, t.categoria, st.classe, st.classe_l1, st.classe_l2, st.classe_l3, st.classe_desc, st.ano, st.area
    FROM    territorios AS t
    LEFT JOIN estatistica_niveis st ON t.id = st.territorio
    WHERE   t.categoria = cat AND st_intersects(t.the_geom, ST_SetSRID(ST_Point(long, lat),4326)); 

END; 
$$  LANGUAGE plpgsql

CREATE TABLE infrastructure_hierarchy AS
SELECT ROW_NUMBER() OVER (ORDER BY cat1) AS id, cat1, cat2, cat3, cat4, cat5, nome 
FROM territorios_infra 
GROUP BY nome, cat1, cat2, cat3, cat4, cat5;