Function to generate a random date value in PostgreSQL

In order to populate a database with artificial but semantically valid contents, sometimes we need to obtain random values.

Considering PostgreSQL DBMS, in the case of generating DATE type values, we could create a customized function, named gen_date(), which receives the lower bound for the date to be created as argument:

CREATE OR REPLACE FUNCTION gen_date(min date) RETURNS date AS $$
  SELECT CURRENT_DATE - (random() * (CURRENT_DATE - $1))::int;
$$ LANGUAGE sql STRICT VOLATILE;

The following instruction is able to check the function results:

SELECT gen_date('1980-01-01'), gen_date('2015-12-31');

  gen_date  |  gen_date  
------------+------------
 2003-03-08 | 2016-01-20
(1 record)

pgAFIS: Biometric Elephant

Biometric technologies are increasingly used in civilian applications. One example is that in the 2014 elections, 21.6 million Brazilians (15% of the country's voters) in 762 municipalities (including 15 capitals) should use biometrics in electronic voting machines in order to reduce the risk of errors, fraud and slowness.

In India a government pioneering initiative promises to create the largest biometric database in the world. In the country there are over half a billion people who lack any kind of identification, making it impossible for them to receive government aid, open bank accounts, apply for loans, get a driver's license, among others. Expecting to register the biometric data of more than one million people a day, the project hopes to have by the end of 2014 the records of all 1.2 billion Indians in its database.

Thus, while scratching on the subject, I found out that the sourcecodes of biometric routines from FBI/NIST are public. Such routines are the heart of AFIS (Automatic Fingerprint & Identification System), among which two algorithms stand out: the characteristics extractor (feature extractor) and the comparator of minutiae (matcher).

After studying this NIST code (in C language), I decided to create support for such features natively on my favorite DBMS, PostgreSQL. And so it was born pgAFIS (Automated Fingerprint Identification System support for PostgreSQL), an extension capable of providing feature extraction and minutiae comparison from SQL statements within PostgreSQL.

But how does that work in practice?

Well, let's start by data modeling. You'll need to create in the database a table in which information of fingerprints will be stored, as described below:

Table "public.fingerprints"
 Column |     Type     | Modifiers 
--------+--------------+-----------
 id     | character(5) | not null
 pgm    | bytea        | 
 wsq    | bytea        | 
 mdt    | bytea        | 
 xyt    | text         | 
Indexes:
    "fingerprints_pkey" PRIMARY KEY, btree (id)

• "pgm" stores fingerprints raw images (PGM)
• "wsq" stores fingerprints images in a compressed form (WSQ)
• "mdt" stores fingerprints templates in  XYTQ  type in a binary format (MDT)
• "xyt" stores fingerprints minutiae data in textual form

The data type used in these columns is "bytea" (an array of bytes), similar to BLOB in other DBMSs. PGM and WSQ formats are open and well-known in the market. On the other hand, MDT is a format I created. :D

Therefore, fingerprints images may be obtained in raw format and stored on "pgm" column in that table. PGM format is a kind of bitmap, where there is no compression. Fingerprint readers usually store image files using this format.

Now pgAFIS comes into play! A particular function is able to convert the binary data from a PGM content into a WSQ format (a kind of JPEG created by NIST/FBI). This is done by running this SQL statement:

UPDATE fingerprints
SET wsq = cwsq(pgm, 2.25, 300, 300, 8, null);

The second step where pgAFIS acts is the extraction of the fingerprints local characteristics (the minutiae) from the WSQ content. The result is the information in XYTQ type (i.e., horizontal and vertical coordinates, slope angle and quality) of each minutia. Again, you'll only need to execute a single SQL statement:

UPDATE fingerprints
SET mdt = mindt(wsq, true);

To get an idea of storing volume: fingerprint images in the PGM format of 300x300 pixels of 90 kB, when compressed in the WSQ format occupy 28 kB of disk space. After extracting minutiae and generating the content in the MDT format (specific to pgAFIS), that data occupies measly 150 bytes! The textual version of MDT, XYT, takes a little more: about 300 bytes!

afis=>
SELECT id,
  length(pgm) AS raw_bytes,
  length(wsq) AS wsq_bytes,
  length(mdt) AS mdt_bytes,
  length(xyt) AS xyt_chars
FROM fingerprints
LIMIT 5;

  id   | pgm_bytes | wsq_bytes | mdt_bytes | xyt_chars 
-------+-----------+-----------+-----------+-----------
 101_1 |     90015 |     27895 |       162 |       274
 101_2 |     90015 |     27602 |       186 |       312
 101_3 |     90015 |     27856 |       146 |       237
 101_4 |     90015 |     28784 |       154 |       262
 101_5 |     90015 |     27653 |       194 |       324
(5 rows)

These processes are part of the of biometrics Retrieve of biometrics represented in the figure below:

Very cool, but what we do with this pile of binary data? Biometric comparisons!

Biometric systems are able to perform two essential comparison operations: Verification and Identification.

In Verification, also called Authentication or [1:1] Search, a sample fingerprint is compared against a single fingerprint already stored in the database. This process can be seen in the figure below:

And that's where pgAFIS strikes again! With this extension, this operation can also be performed through a simple SQL statement:

SELECT (bz_match(a.mdt, b.mdt) >= 20) AS match
FROM fingerprints a, fingerprints b
WHERE a.id = '101_1' AND b.id = '101_6';

In this statement, the fingerprint identified as "101_1" is compared to "101_6". If the level of similarity between the two is at least 20, we can consider that both are equal, that is, refer to the same finger of the same person. It is a quick procedure, because the identification of the person is given at the start and the system only needs to return a boolean value: "yes" or "no".

Oppositely, Identification or [1:N] Search is a costly process for the system, since a sample fingerprint must be compared against all existing records in the biometrics database. In addition, its feedback is a set of possible identifications found in the database, that is, those considered most similar to the sample according to the comparison algorithm. This process can be seen in the figure below:

Even with this exception that the process may adversely affect the server, pgAFIS provides a feature to handle this risk: the limitation on the number of possible suspects. In SQL, a search example would be like this:

SELECT a.id AS probe, b.id AS sample,
  bz_match(a.mdt, b.mdt) AS score
FROM fingerprints a, fingerprints b
WHERE a.id = '101_1' AND b.id != a.id
  AND bz_match(a.mdt, b.mdt) >= 23
LIMIT 3;

In this example, the three fingerprints that more resemble the one labeled "101_1" are returned.

Did you enjoy it? Have fun with it! Contribute! Source codes are available on GitHub at https://github.com/hjort/pgafis.

pgAFIS: o Elefante Biométrico

Tecnologias biométricas são cada vez mais empregadas em aplicações civis. Um exemplo disso é que nas eleições deste ano, 21,6 milhões de brasileiros (15% do total de eleitores do país) em 762 municípios (entre eles 15 capitais) deverão usar a biometria nas urnas eletrônicas, visando reduzir os riscos de erros, fraudes e lentidão.

Na Índia, uma iniciativa pioneira do governo promete criar o maior banco de dados biométricos do mundo. No país existem mais de meio bilhão de pessoas que não possuem qualquer espécie de identificação, o que torna impossível a elas receber auxílios governamentais, abrir conta em banco, solicitar empréstimos, obter carteira de motorista, entre outros. Com a expectativa de cadastrar os dados biométricos de mais de um milhão de pessoas ao dia, o projeto espera ter até o final do ano os registros dos 1,2 bilhão de indianos em seu banco de dados.

Diante disso, ao vasculhar sobre o tema, descobri que os códigos fontes de rotinas biométricas FBI/NIST são públicos. Tais rotinas são o coração dos sistemas AFIS (Automatic Fingerprint Identification System), e dentre elas duas se destacam: o extrator de características (feature extractor) e o comparador de minúcias (matcher).

Estudando esse código do NIST, em linguagem C, resolvi criar o suporte a tais funcionalidades de forma nativa no meu SGBD preferido, o PostgreSQL. E assim nasceu o pgAFIS (Automated Fingerprint Identification System support for PostgreSQL), um módulo capaz de fornecer funcionalidades de extração de características e comparação de minúcias a partir de instruções SQL dentro do PostgreSQL.

Mas como funciona isso na prática?

Bem, comecemos pela modelagem de dados. É preciso criar uma tabela em que sejam armazenadas as informações das impressões digitais, tal como descrito a seguir:

Table "public.fingerprints"
 Column |     Type     | Modifiers 
--------+--------------+-----------
 id     | character(5) | not null
 pgm    | bytea        | 
 wsq    | bytea        | 
 mdt    | bytea        | 
 xyt    | text         | 
Indexes:
    "fingerprints_pkey" PRIMARY KEY, btree (id)

• "pgm" guarda imagens originais das impressões digitais (PGM)
• "wsq" guarda imagens comprimidas das impressões digitais (WSQ)
• "mdt" guarda os templates das digitais do tipo XYTQ em formato binário (MDT)
• "xyt" guarda os dados das minúcias das digitais em formato textual

O tipo de dados usado nessas colunas é o "bytea" (um array de bytes), similar ao BLOB em outros SGBDs. Os formatos PGM e WSQ são abertos e muito conhecidos no mercado. Já o formato MDT eu que inventei. :D

Em seguida, as impressões digitais devem ser obtidas em formato cru (raw) e armazenadas na coluna "pgm" dessa tabela. O formato PGM é uma espécie de bitmap (mapa de bits), onde não existe compressão. Os leitores de impressão digital gravam arquivos obtidos com esse formato.

Agora é que o pgAFIS entra em cena! Uma função é capaz de converter os dados binários de um conteúdo PGM no formato WSQ (uma espécie de JPEG criado pelo NIST/FBI). Isso é feito com a execução dessa instrução SQL:

UPDATE fingerprints
SET wsq = cwsq(pgm, 2.25, 300, 300, 8, null);

A segunda etapa em que age o pgAFIS é na extração das características locais das impressões digitais (as minúcias) a partir do conteúdo WSQ. O resultado são as informações do tipo XYTQ (coordenadas horizontal e vertical, ângulo de inclinação e qualidade) de cada minúcia. Novamente, basta executar uma instrução SQL:

UPDATE fingerprints
SET mdt = mindt(wsq, true);

Para ter uma ideia de volume: imagens de impressões digitais em formato PGM de 300x300 pixels de 90 kB ao serem comprimidas no formato WSQ ocupam 28 kB de espaço. Ao extrair as minúcias e gerar o respectivo conteúdo no formato MDT (específico do pgAFIS), esse ocupa míseros 150 bytes! A versão textual do MDT, em XYT, ocupa um pouco mais: cerca de 300 bytes!

afis=>
SELECT id,
  length(pgm) AS raw_bytes,
  length(wsq) AS wsq_bytes,
  length(mdt) AS mdt_bytes,
  length(xyt) AS xyt_chars
FROM fingerprints
LIMIT 5;

  id   | pgm_bytes | wsq_bytes | mdt_bytes | xyt_chars 
-------+-----------+-----------+-----------+-----------
 101_1 |     90015 |     27895 |       162 |       274
 101_2 |     90015 |     27602 |       186 |       312
 101_3 |     90015 |     27856 |       146 |       237
 101_4 |     90015 |     28784 |       154 |       262
 101_5 |     90015 |     27653 |       194 |       324
(5 rows)

Tais processos fazem parte da etapa de obtenção dos dados biométricos, representada na figura abaixo:

Muito legal, mas fazemos o quê com esse monte de dados binários? As comparações biométricas!

Os sistemas biométricos são capazes de realizar duas operações essenciais de comparação: verificação e identificação.

Na verificação, também chamada de autenticação ou busca [1:1], uma impressão digital de amostra é comparada contra uma única digital já armazenada no banco de dados. Tal processo pode ser visualizado na figura abaixo:

E eis que o pgAFIS ataca novamente! Com ele, essa operação também pode ser realizada através de uma simples instrução SQL:

SELECT (bz_match(a.mdt, b.mdt) >= 20) AS match
FROM fingerprints a, fingerprints b
WHERE a.id = '101_1' AND b.id = '101_6';

Nessa instrução, a impressão digital identificada como "101_1" é comparada à "101_6". Caso o nível de similaridade entre as duas seja pelo menos 20, podemos considerar que ambas são iguais, ou seja, referem-se ao mesmo dedo de uma mesma pessoa. Trata-se de um procedimento rápido, pois a identificação da pessoa é informada no início e o sistema precisa apenas retornar um valor lógico: "sim" ou "não".

Já a identificação ou busca [1:N] é um processo custoso para o sistema, uma vez que uma impressão digital de amostra deve ser comparada contra todos os registros existentes no banco de dados biométricos. Além disso, o retorno é um conjunto de possíveis identificações encontradas no banco, ou seja, as consideradas mais similares à amostra segundo o algoritmo de comparação. Esse processo pode ser visualizado na figura abaixo:

Mesmo com essa ressalva de que o processo pode onerar o servidor, o pgAFIS oferece um recurso para lidar com esse risco: a limitação no número de possíveis suspeitos. Em linguagem SQL, um exemplo de busca seria assim:

SELECT a.id AS probe, b.id AS sample,
  bz_match(a.mdt, b.mdt) AS score
FROM fingerprints a, fingerprints b
WHERE a.id = '101_1' AND b.id != a.id
  AND bz_match(a.mdt, b.mdt) >= 23
LIMIT 3;

Nesse exemplo, são retornadas as três impressões digitais que mais se assemelham à identificada como "101_1".

Gostou? Divirta-se! Contribua! Os códigos estão disponibilizados no GitHub: https://github.com/hjort/pgafis.

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

PostGIS - Conhecendo o Elefante Geoespacial

Workshop de PostGIS que ministrei com o Ignacio Talavera (IMM - Intendencia de Montevideo) no V CONSEGI (Congresso Internacional Software Livre e Governo Eletrônico):

Extraindo indicadores do DATASUS com Shell, SED e AWK

O DATASUS (Departamento de Informática do SUS) disponibiliza na Internet informações que podem servir para subsidiar análises objetivas da situação sanitária, tomadas de decisão baseadas em evidências e elaboração de programas de ações de saúde.

Entre essas destacam-se as Informações de Saúde (vide link), produtos de ações integradas do governo brasileiro nessa área. Apesar de os dados estarem públicos em em diversos formatos, a sua extração automática não é tão trivial.
Neste artigo irei explorar ferramentas poderosas do GNU/Linux, em especial o meu tripleto favorito Shell, SED e AWK, além do utilitário cURL e do SGBD PostgreSQL, na missão de extrair de forma automática alguns indicadores do DATASUS disponíveis na Internet.

Apesar de basear-se em um caso altamente específico, os indicadores do DATASUS, as ideias e trechos de instruções contidas nesse texto podem servir para automatizar a obtenção de dados provenientes de outras fontes. Então vamos começar!

Antes de qualquer coisa, precisamos preparar as estruturas de banco de dados em que serão armazenados os indicadores coletados. No PostgreSQL, crie as estruturas segundo as instruções SQL a seguir:

-- criação do esquema específico

CREATE SCHEMA indicador;

-- criação da tabela de entrada

CREATE TABLE indicador.datasus_entrada
(
  cod_munic integer,
  vlr_dado integer
);

COMMENT ON TABLE indicador.datasus_entrada
  IS 'Tabela de entrada para os indicadores do DATASUS para os Municípios';

-- criação da tabela final

CREATE TABLE indicador.datasus_municipio
(
  cod_munic integer NOT NULL,
  num_ano_mes integer NOT NULL,
  num_inter_hospi_sus integer,
  num_consu_medic_sus integer,
  num_estab_urgen_sus integer,
  num_equip_saude_famil integer,
  PRIMARY KEY (cod_munic, num_ano_mes)
);

COMMENT ON TABLE indicador.datasus_municipio
  IS 'Indicadores mensais do DATASUS para os Municípios';

COMMENT ON COLUMN indicador.datasus_municipio.cod_munic
  IS 'Código do Município (IBGE)';

COMMENT ON COLUMN indicador.datasus_municipio.num_ano_mes
  IS 'Ano e Mês do Indicador (ex: 201209 => Set/2012)';

COMMENT ON COLUMN indicador.datasus_municipio.num_inter_hospi_sus
  IS 'Internações Hospitalares pelo SUS';

COMMENT ON COLUMN indicador.datasus_municipio.num_consu_medic_sus
  IS 'Consultas Médicas pelo SUS';

COMMENT ON COLUMN indicador.datasus_municipio.num_estab_urgen_sus
  IS 'Estabelecimentos com Atendimento de Urgência SUS';

COMMENT ON COLUMN indicador.datasus_municipio.num_equip_saude_famil
  IS 'Equipes de Saúde da Família';

Repare que foram definidos comentários para as tabelas e colunas em questão, uma vez que o padrão de nomenclatura que costumo adotar pode não te parecer trivial. :D
Em seguida, crie um arquivo com o nome sugestivo carregar_indice_datasus.sh e inclua as seguintes intruções em linguagem Shell Script:

#!/bin/bash

if [ $# -ne 1 ]
then
 echo "Sintaxe: $0 <indicador>"
 echo "Indicadores: qrbr qabr aturgbr equipebr"
 exit 1
fi
ind="$1"

# parametrizar de acordo com indicador especificado
case "$ind" in
"qrbr")
 nom="Internações Hospitalares pelo SUS"
 col="num_inter_hospi_sus"
 sis="sih"
 ;;
"qabr")
 nom="Consultas Médicas pelo SUS"
 col="num_consu_medic_sus"
 sis="sia"
 ;;
"aturgbr")
 nom="Estabelecimentos com Atendimento de Urgência SUS"
 col="num_estab_urgen_sus"
 sis="cnes"
 ;;
"equipebr")
 nom="Equipes de Saúde da Família"
 col="num_equip_saude_famil"
 sis="cnes"
 ;;
*)
 echo "Índice desconhecido: $ind"
 exit 2
esac

url="http://tabnet.datasus.gov.br/cgi/tabcgi.exe?$sis/cnv/$ind.def"
tmp="$ind.tmp"
res="$ind.res"
csv="$ind.csv"

echo "Indicador: $nom ($ind)"

# buscar último período disponível
echo "Buscando último período..."
curl -s $url > $tmp
arq=$(awk 'BEGIN{FS="\""}/dbf" SELECTED/{print$2}' $tmp)
mes=$(echo $arq | sed 's/^.*\([0-9]\{2\}\)\.dbf/\1/')
ano=$(echo $arq | sed 's/^.*\([0-9]\{2\}\)[0-9]\{2\}\.dbf/20\1/')
rm -f $tmp
echo "Último período disponível: $mes/$ano (arquivo $arq)"

# definir parâmetros da requisição
case "$ind" in
"qrbr")
 par="Linha=Munic%EDpio&Coluna=--N%E3o-Ativa--&Incremento=Interna%E7%F5es&Arquivos=$arq&SMicrorregi%E3o=TODAS_AS_CATEGORIAS__&SReg.Metropolitana=TODAS_AS_CATEGORIAS__&SAglomerado_urbano=TODAS_AS_CATEGORIAS__&SCapital=TODAS_AS_CATEGORIAS__&SUnidade_Federa%E7%E3o=TODAS_AS_CATEGORIAS__&SProcedimento=TODAS_AS_CATEGORIAS__&SGrupo_procedimento=TODAS_AS_CATEGORIAS__&SSubgrupo_proced.=TODAS_AS_CATEGORIAS__&SForma_organiza%E7%E3o=TODAS_AS_CATEGORIAS__&SComplexidade=TODAS_AS_CATEGORIAS__&SFinanciamento=TODAS_AS_CATEGORIAS__&SRubrica_FAEC=TODAS_AS_CATEGORIAS__&SRegra_contratual=TODAS_AS_CATEGORIAS__&SNatureza=TODAS_AS_CATEGORIAS__&SRegime=TODAS_AS_CATEGORIAS__&SNatureza_jur%EDdica=TODAS_AS_CATEGORIAS__&SEsfera_jur%EDd%EDca=TODAS_AS_CATEGORIAS__&SGest%E3o=TODAS_AS_CATEGORIAS__&zeradas=exibirlz&formato=prn&mostre=Mostra"
 ;;
"qabr")
 par="Linha=Munic%EDpio&Coluna=--N%E3o-Ativa--&Incremento=Qtd.apresentada&Arquivos=$arq&SMicrorregi%E3o=TODAS_AS_CATEGORIAS__&SReg.Metropolitana=TODAS_AS_CATEGORIAS__&SAglomerado_urbano=TODAS_AS_CATEGORIAS__&SCapital=TODAS_AS_CATEGORIAS__&SUnidade_Federa%E7%E3o=TODAS_AS_CATEGORIAS__&SProcedimento=1075&SProcedimento=1076&SProcedimento=1077&SProcedimento=1078&SProcedimento=1079&SProcedimento=1080&SProcedimento=1081&SProcedimento=1082&SProcedimento=1083&SProcedimento=1084&SProcedimento=1085&SProcedimento=1086&SProcedimento=1087&SProcedimento=1088&SProcedimento=1089&SProcedimento=1090&SProcedimento=1091&SProcedimento=1092&SProcedimento=1093&SProcedimento=1094&SProcedimento=1114&SProcedimento=1115&SProcedimento=1117&SProcedimento=1125&SProcedimento=1126&SProcedimento=1127&SProcedimento=1128&SProcedimento=1129&SProcedimento=1130&SProcedimento=1131&SProcedimento=1132&SProcedimento=1133&SProcedimento=1134&SProcedimento=1135&SProcedimento=1136&SProcedimento=1137&SProcedimento=1138&SProcedimento=1139&SProcedimento=1140&SProcedimento=1141&SProcedimento=1142&SProcedimento=1143&SProcedimento=1144&SProcedimento=1145&SProcedimento=1146&SProcedimento=1147&SProcedimento=1148&SProcedimento=1149&SProcedimento=1150&SProcedimento=1169&SProcedimento=1170&SProcedimento=1189&SProcedimento=1190&SProcedimento=1191&SProcedimento=1192&SProcedimento=1193&SProcedimento=1194&SProcedimento=1195&SProcedimento=1196&SProcedimento=1197&SGrupo_procedimento=TODAS_AS_CATEGORIAS__&SSubgrupo_proced.=TODAS_AS_CATEGORIAS__&SForma_organiza%E7%E3o=TODAS_AS_CATEGORIAS__&SComplexidade=TODAS_AS_CATEGORIAS__&SFinanciamento=TODAS_AS_CATEGORIAS__&SSubtp_Financiament=TODAS_AS_CATEGORIAS__&SRegra_contratual=TODAS_AS_CATEGORIAS__&SCar%E1ter_Atendiment=TODAS_AS_CATEGORIAS__&SGest%E3o=TODAS_AS_CATEGORIAS__&SDocumento_registro=TODAS_AS_CATEGORIAS__&SEsfera_administrat=TODAS_AS_CATEGORIAS__&STipo_de_prestador=TODAS_AS_CATEGORIAS__&SAprova%E7%E3o_produ%E7%E3o=TODAS_AS_CATEGORIAS__&zeradas=exibirlz&formato=prn&mostre=Mostra"
 ;;
"aturgbr")
 par="Linha=Munic%EDpio&Coluna=--N%E3o-Ativa--&Incremento=SUS&Arquivos=$arq&SRegi%E3o=TODAS_AS_CATEGORIAS__&SUnidade_Federa%E7%E3o=TODAS_AS_CATEGORIAS__&SCapital=TODAS_AS_CATEGORIAS__&SMicrorregi%E3o=TODAS_AS_CATEGORIAS__&SReg.Metropolitana=TODAS_AS_CATEGORIAS__&SAglomerado_urbano=TODAS_AS_CATEGORIAS__&SEnsino%2FPesquisa=TODAS_AS_CATEGORIAS__&SEsfera_Administrativa=TODAS_AS_CATEGORIAS__&SNatureza=TODAS_AS_CATEGORIAS__&STipo_de_Estabelecimento=TODAS_AS_CATEGORIAS__&STipo_de_Gest%E3o=TODAS_AS_CATEGORIAS__&STipo_de_Prestador=TODAS_AS_CATEGORIAS__&zeradas=exibirlz&formato=prn&mostre=Mostra"
 ;;
"equipebr")
 par="Linha=Munic%EDpio&Coluna=--N%E3o-Ativa--&Incremento=Quantidade&Arquivos=$arq&SRegi%E3o=TODAS_AS_CATEGORIAS__&SUnidade_Federa%E7%E3o=TODAS_AS_CATEGORIAS__&SCapital=TODAS_AS_CATEGORIAS__&SMicrorregi%E3o=TODAS_AS_CATEGORIAS__&SReg.Metropolitana=TODAS_AS_CATEGORIAS__&SAglomerado_urbano=TODAS_AS_CATEGORIAS__&SEnsino%2FPesquisa=TODAS_AS_CATEGORIAS__&SEsfera_Administrativa=TODAS_AS_CATEGORIAS__&SNatureza=TODAS_AS_CATEGORIAS__&STipo_de_Estabelecimento=TODAS_AS_CATEGORIAS__&STipo_de_Gest%E3o=TODAS_AS_CATEGORIAS__&STipo_de_Prestador=TODAS_AS_CATEGORIAS__&STipo_da_Equipe=1&STipo_da_Equipe=2&STipo_da_Equipe=3&zeradas=exibirlz&formato=prn&mostre=Mostra"
 ;;
esac

# obter dados via requisição HTTP POST
echo "Buscando dados do servidor..."
curl -s -d $par $url > $res

# extrair trecho e converter em formato CSV
sed -n '/<PRE>/,/PRE>/p' $res | tr -d '\r' | sed \
 -e '/^"[0-9]/!d' -e 's/^"\([0-9]\+\).*;/\1;/' \
 -e '/^000000/d' -e 's/;-$/;0/' > $csv

# manipulações no banco de dados
export PGDATABASE="banco"
export PGHOST="servidor"
export PGUSER="usuario"

# popular tabela de entrada
echo "Populando tabela de entrada..."

cat $csv | psql -c "TRUNCATE TABLE indicador.datasus_entrada; COPY indicador.datasus_entrada FROM stdin CSV DELIMITER ';'"

# popular tabela final
echo "Populando tabela final..."

echo "UPDATE indicador.datasus_municipio SET $col = NULL WHERE num_ano_mes = $ano$mes" | psql

echo "INSERT INTO indicador.datasus_municipio (cod_munic, num_ano_mes, $col) SELECT cod_munic, $ano$mes, vlr_dado FROM indicador.datasus_entrada" | psql

echo "UPDATE indicador.datasus_municipio a SET $col = vlr_dado FROM indicador.datasus_entrada b WHERE a.cod_munic = b.cod_munic AND num_ano_mes = $ano$mes" | psql

psql -c "TRUNCATE TABLE indicador.datasus_entrada; ANALYZE indicador.datasus_municipio"

Lembre de dar permissão de execução nesse arquivo antes de rodá-lo. Esse script realiza os seguintes procedimentos:

1. valida a informação de um parâmetro (entre "qrbr", "qabr", "aturgbr" e "equipebr") que identifica qual dos indicadores do DATASUS ele irá buscar
2. utiliza a ferramenta cURL para fazer uma requisição HTTP do tipo GET e com isso obter o último período disponibilizado (i.e., Mês/Ano) para o indicador
3. através de SED e AWK, extrai os valores de ano, mês e nome do arquivo a ser recuperado
4. efetua uma segunda requisição HTTP com o cURL, desta vez do tipo POST, para agora assim obter os dados finais em formato pseudo-CSV
5. usando SED, faz o parse da página HTML obtida e extrai apenas os dados desejados, transformando-os em um arquivo do tipo CSV puro
6. com o auxílio do utilitário psql, interface cliente do PostgreSQL via terminal, atualiza as tabelas que criamos

Ufa! Espero que tenha sido claro nessa explicação do script... :D
Grave o arquivo e agora tente executar o script! Sem especificar parâmetro, ele exibirá o seguinte:

$ ./carregar_indice_datasus.sh

Sintaxe: ./carregar_indice_datasus.sh <indicador>
Indicadores: qrbr qabr aturgbr equipebr

Chegou o grande momento! Ao especificar "qrbr", temos a seguinte saída:

$ ./carregar_indice_datasus.sh qrbr

Indicador: Internações Hospitalares pelo SUS (qrbr)
Buscando último período...
Último período disponível: 06/2012 (arquivo qrbr1206.dbf)
Buscando dados do servidor...
Populando tabela de entrada...
Populando tabela final...
UPDATE 0
INSERT 0 5652
UPDATE 5652
ANALYZE

Execuções subsequentes serão um pouco diferentes. Veja:

$ ./carregar_indice_datasus.sh aturgbr

Indicador: Estabelecimentos com Atendimento de Urgência SUS (aturgbr)
Buscando último período...
Último período disponível: 06/2012 (arquivo stbr1206.dbf)
Buscando dados do servidor...
Populando tabela de entrada...
Populando tabela final...
UPDATE 5652
ERROR:  duplicate key value violates unique constraint "datasus_municipio_pkey"
DETAIL:  Key (cod_munic, num_ano_mes)=(110001, 201206) already exists.
UPDATE 5652
ANALYZE

Como produtos intermediários, teremos os arquivos no formato CSV. Veja o conteúdo deles:

$ head -3 *.csv

==> aturgbr.csv <==
110001;1
110037;1
110040;1

==> equipebr.csv <==
110001;5
110037;5
110040;3

==> qabr.csv <==
110001;13718
110037;3216
110040;0

==> qrbr.csv <==
110001;173
110037;60
110040;31

Mas o nosso objetivo final era armazenar esses dados no banco de dados. Olha só como ficou:

$ psql -c "SELECT * FROM indicador.datasus_municipio LIMIT 5"

 cod_munic | num_ano_mes | num_inter_hospi_sus | num_consu_medic_sus | num_estab_urgen_sus | num_equip_saude_famil 
-----------+-------------+---------------------+---------------------+---------------------+-----------------------
    110001 |      201206 |                 173 |               13718 |                   1 |                     5
    110037 |      201206 |                  60 |                3216 |                   1 |                     5
    110040 |      201206 |                  31 |                   0 |                   1 |                     3
    110034 |      201206 |                  21 |                   0 |                   1 |                     2
    110002 |      201206 |                 438 |               40356 |                  11 |                    13
(5 rows)

Ou seja, para cada município (i.e., código do IBGE) e período (i.e., ano/mês), obtivemos quatro indicadores de saúde do DATASUS.

E aí, demorou muito pra executar? Não mediu o tempo..? Sem problemas, execute isso agora:

$ time for i in qrbr qabr aturgbr equipebr; do ./carregar_indice_datasus.sh $i; done

Essa instrução executará de forma serializada o script para cada possível indicador. Observe o final da saída. Pra mim apareceu o seguinte:

real 0m15.776s
user 0m0.976s
sys 0m0.260s

Ou seja, levou menos de 16 segundos para coletar mais de 22 mil linhas de dados do DATASUS transmitidos em 700 kB em um total de 8 requisições HTTP e ainda gravar tudo no banco de dados de forma estruturada... E isso via terminal e sem qualquer intervenção humana - porque pessoas erram! :D

Desvendando os Microdados do ENEM 2010

Estava olhando o conteúdo do Portal Brasileiro de Dados Abertos (vide link) e me deparei com os Microdados do ENEM[1] fornecidos pelo INEP [2]. Fiquei interessado nessas informações e resolvi baixar os arquivos da última edição disponibilizada do exame: 2010.

Achei muito organizado o material fornecido pela instituição ao público, e então resolvi destrinchar os dados usando uma ferramenta mais apropriada: o SGBD de código aberto mais avançado do mundo, o PostgreSQL[3].
O principal conteúdo no arquivo ZIP é o "DADOS_ENEM_2010.txt", um arquivo de texto com 4.626.094 linhas e míseros 4,4 GB...! Cada linha representa um inscrito no exame, e os campos dividem-se em seções de variáveis como CONTROLE DO INSCRITO, CONTROLE DA ESCOLA, CIDADE DA PROVA, PROVA OBJETIVA e PROVA DE REDAÇÃO.
A lista de informações de cada inscrito é extensa, por isso resolvi extrair apenas algun campos de maior interessante. Fazemos isso usando as instruções a seguir no Linux:

cat DADOS_ENEM_2010.txt | cut -b 1-12,21-179,533-572,951,997-1006 > enem10a.txt

O arquivo resultante "enem10a.txt" fica bem menor, com cerca de 980 MB... Os dados nele ainda não estão perfeitos: existem valores em branco em colunas como código e nome de município e nas notas. Para o código do município, usamos o SED com a seguinte instrução para inserir zeros no lugar de vazio, o que será tratado posteriormente:

sed 's/^\(.\{12\}\)\s\{7\}/\10000000/' enem10a.txt > enem10b.txt

Agora temos outro arquivo de texto com 980 MB, pronto para ser carregado no SGBD. É preciso então criar o banco de dados "enem". Podemos fazer isso usando o comando createdb.
Uma vez conectado ao banco recém-criado, criaremos a tabela "enem10" usando a seguinte instrução SQL:

CREATE TABLE enem10 (
  num_inscr int8,
  cod_munic int,
  nom_munic varchar,
  sig_uf char(2),
  idc_cn int2,
  idc_ch int2,
  idc_lc int2,
  idc_mt int2,
  not_cn numeric(6,2),
  not_ch numeric(6,2),
  not_lc numeric(6,2),
  not_mt numeric(6,2),
  idc_rd char(1),
  not_rd numeric(6,2)
);

Veja que começamos a normalizar os dados, principalmente pela especificação de restrições de tipos de dados para cada uma das colunas. Além disso, tabela e cada uma de duas colunas serão melhor documentadas se dotadas de descrições. Isso pode ser feito através dos comandos de criação de comentários abaixo:

COMMENT ON TABLE enem10 IS 'Microdados do Exame Nacional do Ensino Médio 2010';
COMMENT ON COLUMN enem10.num_inscr IS 'Número de inscrição no ENEM 2010';
COMMENT ON COLUMN enem10.cod_munic IS 'Código do Município em que o inscrito mora';
COMMENT ON COLUMN enem10.nom_munic IS 'Nome do município em que o inscrito mora ';
COMMENT ON COLUMN enem10.sig_uf IS 'Código da Unidade da Federação do inscrito no Enem';
COMMENT ON COLUMN enem10.idc_cn IS 'Presença à prova objetiva de Ciências da Natureza';
COMMENT ON COLUMN enem10.idc_ch IS 'Presença à prova objetiva de Ciências Humanas';
COMMENT ON COLUMN enem10.idc_lc IS 'Presença à prova objetiva de Linguagens e Códigos';
COMMENT ON COLUMN enem10.idc_mt IS 'Presença à prova objetiva de Matemática';
COMMENT ON COLUMN enem10.not_cn IS 'Nota da prova de Ciências da Natureza ';
COMMENT ON COLUMN enem10.not_ch IS 'Nota da prova de Ciências Humanas';
COMMENT ON COLUMN enem10.not_lc IS 'Nota da prova de Linguagens e Códigos';
COMMENT ON COLUMN enem10.not_mt IS 'Nota da prova de Matemática';
COMMENT ON COLUMN enem10.idc_rd IS 'Presença à redação';
COMMENT ON COLUMN enem10.not_rd IS 'Nota da prova de redação';

Para dar carga de maneira mais eficiente no PostgreSQL, além de um tuning básico, podemos utilizar a ferramenta pgloader [4]. Para isso, após instalar o pacote "pgloader", crie um arquivo de configurações de nome "pgloader.conf" com o seguinte conteúdo:

[pgsql]
base = enem
log_file = /tmp/pgloader.log
;log_min_messages = DEBUG
client_min_messages = WARNING
client_encoding = 'utf-8'
lc_messages = C
;pg_option_client_encoding = 'utf-8'
;pg_option_standard_conforming_strings = on
pg_option_work_mem = 512MB
copy_every = 10000
commit_every = 50000
null = "         "
empty_string = ""
max_parallel_sections = 4

[enem10]
table       = enem10
format      = fixed
filename    = enem10b.txt
columns     = *
fixed_specs = num_inscr:0:12, cod_munic:12:7, nom_munic:19:150, sig_uf:169:2, idc_cn:171:1, idc_ch:172:1, idc_lc:173:1, idc_mt:174:1, not_cn:175:9, not_ch:184:9, not_lc:193:9, not_mt:202:9, idc_rd:211:1, not_rd:212:9

Esse arquivo especificará ao pgloader de que forma o arquivo de entrada "enem10b.txt" será lido para alimentar a tabela "enem10" no banco de dados. Para maior desempenho, é utilizado o comando COPY (e não INSERT INTO) a cada 10 mil linhas e as transações são efetivadas a cada 50 mil registros. O grande pulo do gato é a substituição de espaços em branco pelo valor nulo. Para iniciar a carga, basta executar pgloader nesse diretório.
Assim que o processo de carga finalizar, é preciso executar as instruções SQL abaixo para ajustes finais nos dados:

UPDATE enem10 SET nom_munic = trim(nom_munic);

UPDATE enem10 SET cod_munic = null WHERE cod_munic = 0;

Confira então se a tabela "enem10" possui as 4,6 milhões de linhas referentes a cada inscrito no exame de 2010. Eis um exemplo do conteúdo dessa tabela:

enem=# SELECT * FROM enem10 LIMIT 10;

  num_inscr   | cod_munic |   nom_munic    | sig_uf | idc_cn | idc_ch | idc_lc | idc_mt | not_cn | not_ch | not_lc | not_mt | idc_rd | not_rd 
--------------+-----------+----------------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------
 200000382760 |   2105302 | IMPERATRIZ     | MA     |      1 |      1 |      1 |      1 | 545.40 | 598.20 | 589.00 | 502.10 | P      | 550.00
 200004076118 |   3202306 | GUACUI         | ES     |      1 |      1 |      1 |      1 | 434.10 | 505.80 | 439.00 | 495.30 | P      | 475.00
 200001265338 |   4309209 | GRAVATAI       | RS     |      1 |      1 |      1 |      1 | 491.10 | 598.40 | 528.70 | 322.50 | P      | 450.00
 200003174558 |   2211001 | TERESINA       | PI     |      1 |      1 |      1 |      1 | 499.90 | 521.10 | 479.10 | 411.50 | P      | 875.00
 200000277562 |   1501709 | BRAGANCA       | PA     |      1 |      1 |      1 |      1 | 479.70 | 583.90 | 447.20 | 398.60 | P      | 575.00
 200000104197 |   2800670 | BOQUIM         | SE     |      1 |      1 |      1 |      1 | 341.90 | 438.40 | 360.00 | 370.70 | P      | 550.00
 200004343078 |   3139409 | MANHUACU       | MG     |      1 |      1 |      1 |      1 | 499.00 | 610.90 | 452.00 | 513.80 | P      | 450.00
 200001011958 |   1502400 | CASTANHAL      | PA     |      1 |      1 |      1 |      1 | 597.80 | 599.30 | 517.80 | 586.60 | P      | 700.00
 200002382852 |   3106200 | BELO HORIZONTE | MG     |      1 |      1 |      1 |      1 | 506.50 | 623.70 | 555.50 | 530.10 | P      | 725.00
 200000757106 |   1709500 | GURUPI         | TO     |      1 |      1 |      1 |      1 | 494.70 | 534.10 | 558.00 | 430.80 | P      | 800.00
(10 rows)

A essa altura você já deve ter percebido que trabalhar com tamanho volume de dados no SGBD não é nada trivial. As consultas tendem a ser mais lentas a cada vez que uma varredura sequencial de tabela (i.e., full scan) é invocado. Para minimizar esse problema, podemos criar tabelas totalizadoras.
Para criar a tabela "nota_media_cidade", uma agregação da média e desvio padrão das notas e quantidades de inscritos para cada cidade (i.e., municípios com mais de 1.000 alunos), podemos executar a seguinte instrução SQL (note que desconsideramos os candidatos que não compareceram às provas):

SELECT nom_munic AS municipio, sig_uf AS uf,
  avg(not_cn + not_ch + not_lc + not_mt + not_rd)::int AS media,
  stddev(not_cn + not_ch + not_lc + not_mt + not_rd)::int AS desvio,
  count(num_inscr) AS inscritos
INTO nota_media_cidade
FROM enem10
WHERE cod_munic IS NOT NULL
  AND idc_cn = 1 AND idc_ch = 1 AND idc_lc = 1 AND idc_mt = 1 AND idc_rd = 'P'
GROUP BY nom_munic, sig_uf
HAVING count(num_inscr) > 1000
ORDER BY media DESC;

Outra análise interessante é criar a tabela "nota_media_estado", uma agregação da média, desvio padrão, mínima e máxima das notas e quantidades de inscritos para cada Unidade da Federação (i.e., estado brasileiro). Para isso, executamos a instrução SQL abaixo:

SELECT sig_uf AS uf,
  avg(not_cn + not_ch + not_lc + not_mt + not_rd)::int AS media,
  stddev(not_cn + not_ch + not_lc + not_mt + not_rd)::int AS desvio,
  min(not_cn + not_ch + not_lc + not_mt + not_rd)::int AS minima,
  max(not_cn + not_ch + not_lc + not_mt + not_rd)::int AS maxima,
  count(num_inscr) AS inscritos
INTO nota_media_estado
FROM enem10
WHERE cod_munic IS NOT NULL
  AND idc_cn = 1 AND idc_ch = 1 AND idc_lc = 1 AND idc_mt = 1 AND idc_rd = 'P'
GROUP BY sig_uf
ORDER BY 2 DESC;

Nas agregações anteriores, usamos a soma das notas dos candidatos nas 4 provas objetivas e na redação. Para obter o desempenho dos candidatos separadamente em cada uma das provas, podemos criar a tabela "nota_prova_geral" conforme instrução a seguir:

SELECT
  min(not_cn) AS min_cn, max(not_cn) AS max_cn, avg(not_cn)::numeric(6,2) AS med_cn, stddev(not_cn)::numeric(6,2) AS dsv_cn,
  min(not_ch) AS min_ch, max(not_ch) AS max_ch, avg(not_ch)::numeric(6,2) AS med_ch, stddev(not_ch)::numeric(6,2) AS dsv_ch,
  min(not_lc) AS min_lc, max(not_lc) AS max_lc, avg(not_lc)::numeric(6,2) AS med_lc, stddev(not_lc)::numeric(6,2) AS dsv_lc,
  min(not_mt) AS min_mt, max(not_mt) AS max_mt, avg(not_mt)::numeric(6,2) AS med_mt, stddev(not_mt)::numeric(6,2) AS dsv_mt,
  min(not_rd) AS min_rd, max(not_rd) AS max_rd, avg(not_rd)::numeric(6,2) AS med_rd, stddev(not_rd)::numeric(6,2) AS dsv_rd
INTO nota_prova_geral
FROM enem10
WHERE idc_cn = 1 AND idc_ch = 1 AND idc_lc = 1 AND idc_mt = 1 AND idc_rd = 'P';

A fim de melhor entender os dados dessa tabela, podemos criar a visão "nota_prova" com esse comando SQL:

CREATE VIEW nota_prova AS
SELECT 'Ciências da Natureza' AS prova, min_cn AS min, max_cn AS max, med_cn AS media, dsv_cn AS desvio FROM nota_prova_geral
UNION
SELECT 'Ciências Humanas', min_ch, max_ch, med_ch, dsv_ch FROM nota_prova_geral
UNION
SELECT 'Linguagens e Códigos', min_lc, max_lc, med_lc, dsv_lc FROM nota_prova_geral
UNION
SELECT 'Matemática', min_mt, max_mt, med_mt, dsv_mt FROM nota_prova_geral
UNION
SELECT 'Redação', min_rd, max_rd, med_rd, dsv_rd FROM nota_prova_geral
ORDER BY 1;

Como resultado, teremos as seguintes estruturas no banco de dados "enem":

enem=# \d+
                                              List of relations
 Schema |       Name        | Type  | Owner |    Size    |                    Description                    
--------+-------------------+-------+-------+------------+---------------------------------------------------
 public | enem10            | table | hjort | 1452 MB    | Microdados do Exame Nacional do Ensino Médio 2010
 public | nota_media_cidade | table | hjort | 40 kB      | 
 public | nota_media_estado | table | hjort | 8192 bytes | 
 public | nota_prova        | view  | hjort | 0 bytes    | 
 public | nota_prova_geral  | table | hjort | 16 kB      | 
(5 rows)

Pronto! Agora podemos começar a fazer as análises dos dados usando essas tabelas e visão. Eis alguns exemplos a seguir.

1. Quais são as cidades cujos alunos obtiveram as maiores médias?

municipio	uf	media	desvio	inscritos
NITEROI	RJ	2.935	420	9.328
FLORIANOPOLIS	SC	2.935	385	6.160
VALINHOS	SP	2.892	424	1.634
NOVA FRIBURGO	RJ	2.891	376	2.129
SAO CAETANO DO SUL	SP	2.888	402	2.092
BOTUCATU	SP	2.887	405	1.449
ITAJUBA	MG	2.886	361	2.647
JUIZ DE FORA	MG	2.884	399	11.411
ARAXA	MG	2.883	396	1.054
ARARAQUARA	SP	2.882	390	3.214
CATANDUVA	SP	2.863	408	1.115
PATOS DE MINAS	MG	2.858	392	1.767
RIBEIRAO PRETO	SP	2.856	406	9.512
SAO CARLOS	SP	2.855	403	5.528
SAO JOSE DO RIO PRETO	SP	2.852	413	5.686
BARBACENA	MG	2.849	374	2.469
JAU	SP	2.847	409	1.233
POUSO ALEGRE	MG	2.846	379	2.340
VICOSA	MG	2.845	410	2.713
UBERABA	MG	2.843	420	4.058
PORTO ALEGRE	RS	2.843	389	24.059
UBA	MG	2.841	385	1.229
BELO HORIZONTE	MG	2.840	422	63.090
POCOS DE CALDAS	MG	2.839	355	2.614
BLUMENAU	SC	2.831	364	1.485
PIRASSUNUNGA	SP	2.830	396	1.317
CAMPINAS	SP	2.830	417	13.638
VITORIA	ES	2.828	439	8.475
VOLTA REDONDA	RJ	2.828	373	3.934
RIO DE JANEIRO	RJ	2.826	408	93.300
SAO JOSE DOS CAMPOS	SP	2.825	403	11.545
JABOTICABAL	SP	2.822	381	1.077
DIVINOPOLIS	MG	2.822	369	4.787
SANTA MARIA	RS	2.819	390	7.601
SANTOS	SP	2.817	404	5.195
GUARATINGUETA	SP	2.817	397	1.559
LAVRAS	MG	2.814	388	2.446
JUNDIAI	SP	2.814	392	5.232
CONSELHEIRO LAFAIETE	MG	2.813	382	2.429
PASSOS	MG	2.813	399	1.316
CURITIBA	PR	2.812	394	38.904
SAO JOAO DEL REI	MG	2.812	354	2.273
CRICIUMA	SC	2.810	399	1.232
MARILIA	SP	2.807	409	2.721
LAGOA SANTA	MG	2.806	391	1.038
MOGI MIRIM	SP	2.806	412	1.249
NOVA LIMA	MG	2.806	403	1.701
SAO JOSE	SC	2.805	350	2.478
PIRACICABA	SP	2.804	399	4.435
TAUBATE	SP	2.804	407	3.209

(Vide "nota_media_cidade")

2. Em quais estados os alunos obtiveram as maiores médias?

uf	media	desvio	minima	maxima	inscritos
RJ	2.764	392	1.588	4.242	220.383
SP	2.739	392	1.488	4.346	522.098
MG	2.737	387	1.513	4.239	368.835
SC	2.728	363	1.605	4.179	60.242
PR	2.704	370	1.563	4.111	159.061
RS	2.688	363	1.578	4.230	199.630
DF	2.675	383	1.579	4.111	40.292
GO	2.645	393	1.585	4.134	77.254
CE	2.641	408	1.543	4.221	146.687
ES	2.640	392	1.592	4.174	75.985
PE	2.626	380	1.450	4.186	155.738
PB	2.592	371	1.561	4.160	68.475
MS	2.590	371	1.601	4.127	69.354
RN	2.589	374	1.493	4.105	64.952
PA	2.587	368	1.475	4.131	120.739
MT	2.559	356	1.544	4.029	76.255
AP	2.551	335	1.615	3.782	9.413
PI	2.550	390	1.567	4.217	63.441
RO	2.548	346	1.570	4.094	33.387
AL	2.545	366	1.633	4.107	30.301
BA	2.545	366	1.443	4.166	264.654
MA	2.543	374	1.544	4.109	123.806
RR	2.527	350	1.622	3.873	8.921
TO	2.523	374	1.558	4.014	19.491
AM	2.514	345	1.517	4.084	80.490
SE	2.499	370	1.537	4.126	33.099
AC	2.491	344	1.621	3.995	9.887

(Vide "nota_media_estado")

3. Qual foi o desempenho geral dos alunos em cada uma das provas?

prova	min	max	media	desvio
Ciências da Natureza	297.30	844.70	489.05	79.96
Ciências Humanas	265.10	883.70	550.16	89.83
Linguagens e Códigos	254.00	810.10	512.00	77.28
Matemática	313.40	973.20	506.90	112.51
Redação	250.00	1000.00	596.44	132.34

(Vide "nota_prova")
Bom, através dos dados pude constatar que o ensino médio brasileiro de qualidade (pelo menos no ano de 2010) está polarizado no eixo Sudeste-Sul do país. Parabéns a Niterói - RJ, Florianópolis - SC e Valinhos - SP, as cidades campeãs no ensino! Tomara que o Ministério da Educação tenha ideia de como homogeneizar (para melhor!) o ensino em todas as regiões do Brasil.

Com relação às notas, a mídia limita-se a divulgar apenas as mínimas e máximas (vide [5,6]). Entretanto, qualquer profissional com conhecimento estatístico sabe que o mais importante nesse tipo de análise são as médias e os desvios padrão. Um exemplo disso é na prova de matemática, onde ocorreu a maior nota das objetivas, porém também a maior diferença entre as notas dos candidatos. Ou seja, é uma disciplina cujo ensino precisa ser reforçado! :D

Referências

[1] Sobre o Enem - http://portal.inep.gov.br/web/enem/sobre-o-enem/
[2] Microdados do Enem - http://dados.gov.br/dataset/microdados-do-exame-nacional-do-ensino-medio-enem/
[3] PostgreSQL - http://www.postgresql.org/
[4] pgloader - http://pgfoundry.org/projects/pgloader/
[5] Confira as notas mínima e máxima das provas do Enem (Estadão) - http://www.estadao.com.br/noticias/vidae,confira-as-notas-minima-e-maxima-das-provas-do-enem,666251,0.htm
[6] Como calcular a nota do Enem? - http://vestibular.brasilescola.com/enem/como-calcular-nota-enem.htm