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.

PGBR 2013 - Conferência Brasileira PostgreSQL

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

Carga Geoespacial de Estados 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

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