quinta-feira, 12 de setembro de 2013

TV Serpro - Conectado: banco de dados espaciais PostGIS

Na primeira edição do programa Conectado da TV Serpro, discuto sobre o funcionamento do banco de dados espaciais PostGIS.



terça-feira, 30 de abril de 2013

PGBR 2013 - Conferência Brasileira PostgreSQL


PGBR 2013 - Conferência Brasileira PostgreSQL

terça-feira, 9 de abril de 2013

Especialista em PostgreSQL?

Você pode ser considerado especialista no SGBD PostgreSQL se...

...já instalou e configurou o PostgreSQL em pelo menos três plataformas distintas (ex: Linux, Windows, Mac OS, *BSD, Solaris, Mainframe, celular, cafeteira)

...já administrou uma base de dados PostgreSQL de porte razoável (a partir de 500 GB de dados) e agora entende a importância do autovacuum!

...já baixou e analisou os códigos fontes do PostgreSQL e compilou os binários após ter apanhado para encontrar dependências como flex, bison, readline e zlib...

...já desenvolveu funções em pelo menos três das inúmeras linguagens procedurais disponíveis no PostgreSQL (ex: PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, PL/Ruby, PL/Java, PL/R, PL/sh, PL/Lua)

...já construiu pelo menos um módulo em C aproveitando a incrível extensibilidade do PostgreSQL (i.e., DLL no Windows, SO no Linux)

...conhece e sabe usar pelo menos dois módulos contidos no contrib ou que já foram incorporados ao core do PostgreSQL

...sabe o que é "pghackers" e já se deparou com inúmeras threads do PostgreSQL que foram finalizadas com um simples "regards, tom lane"...

...entende que pgAdmin ou phpPgAdmin são interessantes para o usuário de PostgreSQL, mas não abre mão do psql e sobrevive tranquilamente na falta de um mouse!

...já implementou "no braço" scripts (em Shell ou batch) que efetuam backups lógicos e físicos e replicação dos dados do PostgreSQL usando tecnologias como pg_dump, PITR, archiving, streaming replication e Warm ou Hot Standby

...sabe a pronúncia correta do PostgreSQL e irrita-se quando escrevem "PostGreSQL" ou soltam um horripilante "Postgrí"... :D

sexta-feira, 25 de janeiro de 2013

Carga Geoespacial de Estados do IBGE


Atenção: recomenda-se a prévia leitura do primeiro artigo, Carga Geoespacial de Municípios do IBGE, uma vez que conceitos e artefatos explorados nele são pré-requisitos para o presente estudo.

No artigo anterior vimos como baixar a Malha Digital dos Municípios de 2010 do site do IBGE e em seguida importar os arquivos no formato shapefile para um banco de dados PostgreSQL dotado da respectiva extensão espacial PostGIS.

Dessa vez o objetivo será a criação da malha digital dos 26 Estados e do Distrito Federal do Brasil, também disponibilizada pelo IBGE.

Vamos utilizar os mesmos artefatos considerados no artigo prévio, isto é, o diretório "ibge" contendo o subdiretório "2010" com os arquivos do tipo ZIP baixados do site do IBGE.

Primeiramente, crie o Script Shell de nome carga-estados-ibge.sh no diretório "ibge" com o conteúdo abaixo. Ele servirá para gerar um script SQL para a importação dos shapefiles para um banco de dados PostgreSQL.

#!/bin/bash

sql="$PWD/`basename $0 .sh`.sql"
tab="tbestado_carga"

primeiro=true

echo -e "DROP TABLE IF EXISTS $tab;" > $sql

cd 2010
for a in *.zip; do
    uf=`basename $a .zip`
    ufm=`echo $uf | tr a-z A-Z`
    echo -e "\n$ufm..."

    if [ ! -d $uf ]; then
        unzip $a "*UF*"
    fi

    cd $uf
    shp=`ls ??UF*.shp`

    if $primeiro; then
        echo -e "\n\n-- criação da tabela\n" >> $sql
        shp2pgsql -s 4326 -p -D -W iso88591 $shp $tab >> $sql
        echo -e "ALTER TABLE $tab ADD uf char(2);" >> $sql
        primeiro=false
    fi

    echo -e "\n\n-- $ufm ($shp)\n" >> $sql
    echo -e "ALTER TABLE $tab ALTER uf SET DEFAULT '$ufm';\n" >> $sql
    shp2pgsql -s 4326 -a -D -W iso88591 $shp $tab >> $sql

    cd ..
    rm -rf $uf
done
cd ..

echo -e "\nGerado arquivo: $sql"

Em seguida, abra um terminal e execute esse script carga-estados-ibge.sh. Como resultado, será criado o arquivo carga-estados-ibge.sql contendo os polígonos de todos os estados cuja UF esteja no diretório "2010".

No PostgreSQL, utilize o mesmo banco de dados anterior dotado da extensão espacial PostGIS de nome ibge e cujo dono seja o usuário sa_gis.

Execute nesse banco o script carga-estados-ibge.sql para efetuar a criação da tabela temporária tbestado_carga.

Tal como no primeiro artigo, usaremos uma modelagem de dados mais aprimorada para os dados de estados. Para isso, execute os seguintes comandos SQL:

DROP TABLE IF EXISTS tbestado;

CREATE TABLE tbestado
(
  chv_estado integer NOT NULL,
  sig_estado char(2) NOT NULL,
  nom_estado varchar(45) NOT NULL,
  nom_regiao varchar(30) NOT NULL,
  CONSTRAINT pkestado PRIMARY KEY (chv_estado),
  CONSTRAINT akestado UNIQUE (sig_estado)
);

SELECT AddGeometryColumn('', 'tbestado', 'geo_area', '4326', 'MULTIPOLYGON', 2);

COMMENT ON TABLE tbestado IS 'Armazena os estados brasileiros com dados georreferenciados';

Finalmente iremos popular a recém-criada tabela tbestado a partir da tabela temporária tbestado_carga, efetuando as transformações necessárias em quase todas as colunas (repare que utilizaremos novamente a função pretty() criada no primeiro artigo):

INSERT INTO tbestado (chv_estado, sig_estado, nom_estado, nom_regiao, geo_area)
SELECT cd_geocodu::int, uf, pretty(nm_estado), pretty(nm_regiao), ST_Force_2D(geom)
FROM tbestado_carga;

Crie restrições no tipo de dados geométrico com a instrução a seguir:

ALTER TABLE tbestado
  ADD CONSTRAINT ckestado_dims_geo_area CHECK (st_ndims(geo_area) = 2),
  ADD CONSTRAINT ckestado_type_geo_area CHECK (geometrytype(geo_area) = 'MULTIPOLYGON' OR geo_area IS NULL),
  ADD CONSTRAINT ckestado_srid_geo_area CHECK (st_srid(geo_area) = 4326);

Ou seja, a coluna espacial nessa tabela de estados conterá apenas multipolígonos em 2 dimensões cujo sistema de referências será o 4326 (WGS 84).

Para acelerar as consultas espaciais, precisaremos criar um índice espacial do tipo GiST:

CREATE INDEX ixestado_001 ON tbestado USING gist (geo_area);

Resta alterar o dono da tabela e conceder acesso aos demais usuários:

ALTER TABLE tbestado OWNER TO sa_gis;
GRANT ALL ON TABLE tbestado TO public;

A tabela temporária não será mais necessária e pode ser removida:

DROP TABLE tbestado_carga;

Se você baixou os arquivos ZIP das 27 Unidades da Federação, verá que existirão exatamente 27 registros na tabela de estados. :)

SELECT chv_estado, sig_estado, nom_estado, nom_regiao
FROM tbestado
ORDER BY 2;
 chv_estado | sig_estado |     nom_estado      |  nom_regiao  
------------+------------+---------------------+--------------
         12 | AC         | Acre                | Norte
         27 | AL         | Alagoas             | Nordeste
         13 | AM         | Amazonas            | Norte
         16 | AP         | Amapá               | Norte
         29 | BA         | Bahia               | Nordeste
         23 | CE         | Ceará               | Nordeste
         53 | DF         | Distrito Federal    | Centro-Oeste
         32 | ES         | Espirito Santo      | Sudeste
         52 | GO         | Goiás               | Centro-Oeste
         21 | MA         | Maranhão            | Nordeste
         31 | MG         | Minas Gerais        | Sudeste
         50 | MS         | Mato Grosso do Sul  | Centro-Oeste
         51 | MT         | Mato Grosso         | Centro-Oeste
         15 | PA         | Pará                | Norte
         25 | PB         | Paraíba             | Nordeste
         26 | PE         | Pernambuco          | Nordeste
         22 | PI         | Piauí               | Nordeste
         41 | PR         | Paraná              | Sul
         33 | RJ         | Rio de Janeiro      | Sudeste
         24 | RN         | Rio Grande do Norte | Nordeste
         11 | RO         | Rondônia            | Norte
         14 | RR         | Roraima             | Norte
         43 | RS         | Rio Grande do Sul   | Sul
         42 | SC         | Santa Catarina      | Sul
         28 | SE         | Sergipe             | Nordeste
         35 | SP         | São Paulo           | Sudeste
         17 | TO         | Tocantins           | Norte
(27 rows)

Agora veja o resultado dos polígonos usando ferramentas GIS desktop, tal como o Quantum GIS:


Como diria um colega de trabalho de sanidade mental relativamente duvidosa: "é simples como um tomate: redondo e vermelho"! :D

No próximo artigo iremos gerar os polígonos referentes às regiões geopolíticas do Brasil a partir dessa malha de estados.

sexta-feira, 4 de janeiro de 2013

Carga Geoespacial de Municípios do IBGE


Neste artigo vamos mostrar como importar para um banco de dados PostgreSQL dotado da extensão espacial PostGIS os shapefiles e atributos da Malha Digital dos Municípios de 2010 fornecida pelo IBGE.

Para começar, crie um diretório de nome "ibge" no disco local para os arquivos a serem manipulados neste artigo.

Será preciso baixar os arquivos de malhas digitais diretamente do site do IBGE. Acesse o portal do IBGE no endereço http://downloads.ibge.gov.br/ e clique no banner geociências.

Abra a árvore malhas_digitais > municipio_2010. Os shapefiles são agrupados por Unidade da Federação (UF). Por exemplo, ac.zip contém os shapefiles do Acre, ba.zip contém os da Bahia, e assim por diante. Eles possuem tamanho variado: de 500 kB a 44 MB. Faça download das UFs desejadas. Crie um subdiretório de nome "2010" no diretório "ibge" e grave os arquivos com extensão ZIP nele.

Agora crie o Script Shell de nome carga-municipios-ibge.sh no diretório "ibge" com o conteúdo abaixo. Ele servirá para gerar um script SQL para a importação dos shapefiles para um banco de dados PostgreSQL.

#!/bin/bash

sql="$PWD/`basename $0 .sh`.sql"
tab="tbmunicipio_carga"

primeiro=true

echo -e "DROP TABLE IF EXISTS $tab;" > $sql

cd 2010
for a in *.zip; do
    uf=`basename $a .zip`
    ufm=`echo $uf | tr a-z A-Z`
    echo -e "\n$ufm..."

    if [ ! -d $uf ]; then
        unzip $a "*MU*"
    fi

    cd $uf
    shp=`ls ??MU*.shp`

    if $primeiro; then
        echo -e "\n\n-- criação da tabela\n" >> $sql
        shp2pgsql -s 4326 -p -D -W iso88591 $shp $tab >> $sql
        echo -e "ALTER TABLE $tab ADD uf char(2);" >> $sql
        primeiro=false
    fi

    echo -e "\n\n-- $ufm ($shp)\n" >> $sql
    echo -e "ALTER TABLE $tab ALTER uf SET DEFAULT '$ufm';\n" >> $sql
    shp2pgsql -s 4326 -a -D -W iso88591 $shp $tab >> $sql

    cd ..
    rm -rf $uf
done
cd ..

echo -e "\nGerado arquivo: $sql"

Abra um terminal e execute o script carga-municipios-ibge.sh. Como resultado, será criado o arquivo carga-municipios-ibge.sql contendo os polígonos de todos os municípios cuja UF esteja no diretório "2010".

No PostgreSQL, crie um banco de dados dotado da extensão espacial PostGIS de nome ibge e cujo dono seja o usuário sa_gis.

Em seguida, execute no banco ibge o script carga-municipios-ibge.sql para efetuar a criação da tabela temporária tbmunicipio_carga.

Usaremos uma modelagem de dados mais aprimorada para os dados de município. Para isso, execute os seguintes comandos SQL:

DROP TABLE IF EXISTS tbmunicipio;

CREATE TABLE tbmunicipio
(
  chv_municipio integer NOT NULL,
  nom_municipio varchar(45) NOT NULL,
  sig_uf char(2) NOT NULL,
  CONSTRAINT pkmunicipio PRIMARY KEY (chv_municipio)
);

SELECT AddGeometryColumn('', 'tbmunicipio', 'geo_area', '4326', 'MULTIPOLYGON', 2);

COMMENT ON TABLE tbmunicipio IS 'Armazena os municípios brasileiros com dados georreferenciados';

Como originalmente os nomes dos municípios estão em maiúsculo, criaremos a função pretty() para deixá-los mais bonitos! Para isso execute a instrução abaixo:

CREATE OR REPLACE FUNCTION pretty(varchar) RETURNS varchar AS $$
  SELECT regexp_replace(initcap($1), 'D([aeo]s?) ', E'd\\1 ', 'g')
$$ LANGUAGE SQL STRICT IMMUTABLE;

Finalmente iremos popular a recém-criada tabela tbmunicipio a partir da tabela temporária tbmunicipio_carga, efetuando as transformações necessárias em quase todas as colunas:

INSERT INTO tbmunicipio (chv_municipio, nom_municipio, sig_uf, geo_area)
SELECT cd_geocodm::int, pretty(nm_municip), uf, ST_Force_2D(geom)
FROM tbmunicipio_carga;

Crie restrições no tipo de dados geométrico com a instrução a seguir:

ALTER TABLE tbmunicipio
  ADD CONSTRAINT ckmunicipio_dims_geo_area CHECK (st_ndims(geo_area) = 2),
  ADD CONSTRAINT ckmunicipio_type_geo_area CHECK (geometrytype(geo_area) = 'MULTIPOLYGON' OR geo_area IS NULL),
  ADD CONSTRAINT ckmunicipio_srid_geo_area CHECK (st_srid(geo_area) = 4326);

Ou seja, a coluna espacial nessa tabela de municípios conterá apenas multipolígonos em 2 dimensões cujo sistema de referências será o 4326 (WGS 84).

Para acelerar as consultas espaciais, precisaremos criar um índice espacial do tipo GiST:

CREATE INDEX ixmunicipio_001 ON tbmunicipio USING gist (geo_area);

Resta alterar o dono da tabela e conceder acesso aos demais usuários:

ALTER TABLE tbmunicipio OWNER TO sa_gis;
GRANT ALL ON TABLE tbmunicipio TO public;

A tabela temporária não será mais necessária e pode ser removida:

DROP TABLE tbmunicipio_carga;

Se você baixou os arquivos ZIP das 27 Unidades da Federação, verá que existirão 5567 registros na tabela de municípios. Não precisa estranhar: 2 desses registros referem-se às grandes lagoas no Rio Grande do Sul. :)

Veja o resultado dos polígonos usando ferramentas GIS desktop, tal como o Quantum GIS:


Muito fácil, não? Nos próximos artigos iremos gerar os polígonos referentes aos estados e regiões geopolíticas do Brasil a partir dessa malha de municípios. Aguarde... :D