Mostrando postagens com marcador shell. Mostrar todas as postagens
Mostrando postagens com marcador shell. Mostrar todas as postagens

sexta-feira, 11 de dezembro de 2015

Evaluating NIST Biometric Image Software (NBIS) using FVC2004 Databases - Part 2

This series of articles aim at evaluating NBIS biometric software by using it against fingerprint image databases from FVC2004 verification competition.



WARNING: If you have not yet read the first article, I hardly suggest you to do so: Evaluating NIST Biometric Image Software (NBIS) using FVC2004 Databases - Part 1.

Considering you have already followed the steps made in the first article, which included installing NBIS and downloading FVC2004 databases, open a terminal and type these commands:

source nbis-env.sh

cd images/

3. Lights, camera, action!


3.1. Converting TIFF images to WSQ format

The first step on our biometric journey is to convert fingerprint image files into a format called WSQ (Wavelet Scalar Quantization) [11]. The WSQ algorithm is based on wavelet theory and has become a standard for the exchange and storage of fingerprint images. It was developed by the FBI, the Los Alamos National Laboratory, and the National Institute of Standards and Technology (NIST).

NBIS provides a tool called CWSQ to compress grayscale fingerprint images using WSQ algorithm. We'll use it in this section.

In our case of FVC2004 databases, fingerprint image files are offered in TIFF format. We'll select a single one to test NBIS tools: "db1/101_1.tif". You could display this fingerprint image by issuing (or your preferred image viewer):

qiv db1/101_1.tif


As the image is in TIFF file format, we need to know its dimensions before invoking CWSQ. Therefore, type this:

identify db1/101_1.tif
db1/101_1.tif TIFF 640x480 640x480+0+0 8-bit Grayscale DirectClass 308KB 0.000u 0:00.009

The command output means that the image we chose is really a TIFF format, has 640 of width and 480 of height in pixels, and is in 8-bit Grayscale.

Finally, call the CWSQ program using these arguments (.75 stands for a 15:1 compression rate):

cwsq .75 wsq db1/101_1.tif -r 640,480,8

If successful, it will produce a new file with the extension ".wsq" (11 kB) in the same directory of the ".tif" (301 kB) file:

ls -la db1/101_1.*

NBIS provides a WSQ viewing tool called DPYIMAGE. Try running it to see the resulting WSQ file:

dpyimage db1/101_1.wsq

Once we could convert a single TIFF image into a WSQ specific format, let's do the same throughout all four FVC2004 databases. As each database has distinct dimensions, we need to specify them separately:

find db1/ -name "*.tif" -exec cwsq .75 wsq {} -r 640,480,8 \;
find db2/ -name "*.tif" -exec cwsq .75 wsq {} -r 328,364,8 \;
find db3/ -name "*.tif" -exec cwsq .75 wsq {} -r 300,480,8 \;
find db4/ -name "*.tif" -exec cwsq .75 wsq {} -r 288,384,8 \;

You can verify whether all WSQ files were created by issuing this command:

find -name "*.wsq" | head
./db1/108_4.wsq
./db1/109_2.wsq
./db1/102_2.wsq
./db1/109_5.wsq
./db1/101_4.wsq
./db1/104_4.wsq
./db1/104_8.wsq
./db1/103_2.wsq
./db1/110_4.wsq
./db1/109_8.wsq

3.2. Checking quality of WSQ images through NFIQ

Another interesting NIST algorithm to test is NIST Fingerprint Image Quality (NFIQ) [12], which also comes with the NBIS bundle.

NFIQ, a fingerprint image quality algorithm, analyses a fingerprint image and assigns to it a quality value on a scale of 1 (highest quality) to 5 (lowest quality). Higher quality images produce significantly better performance with matching algorithms. That's exactly why NFIQ is so important to biometric applications: it can be employed to assure fingerprint images are being collected with an acceptable quality.

As we have already produced WSQ files, the synthax for NFIQ is quite simple. Just execute this:

nfiq db1/101_1.wsq

By the output value ("2"), we can assume the biometric quality for the given image is quite good.

If we would want to check the quality of our entire dataset, we could execute the following Shell Script:

for i in `seq 1 4`
do
  for a in db$i/*.wsq
  do
    echo "$a"
    #nfiq $a
  done
done

3.3. Extracting features from fingerprints through MINDTCT

In an Automated Fingerprint Identification System (AFIS), there are two major biometric algorithms that play an indispensable role: 1) the features extractor (or simply "extractor") and 2) the templates matcher (or simply "matcher"). We'll start by the extractor: in the case of NBIS, it is embedded in a program called MINDTCT [1].

NBIS bundle provides MINDTCT, a minutiae detector, which automatically locates and records ridge ending and bifurcations in a fingerprint image [13, 15]. This system includes minutiae quality assessment based on local image conditions. The FBI's Universal Latent Workstation uses MINDTCT, and it too is the only known no cost system of its kind [16].

The program MINDTCT takes a WSQ compressed image file, processes the fingerprint image and automatically detects minutiae [16].

Considering the file "db1/101_1.wsq" we generated previously, we could invoke MINDTCT in order to extract the features:

mindtct -b -m1 db1/101_1.wsq db1/101_1

The result of the last instruction is a bunch of other files with the same prefix and in the same directory. Check it out:

find db1/101_1.*
db1/101_1.brw
db1/101_1.dm
db1/101_1.hcm
db1/101_1.lcm
db1/101_1.lfm
db1/101_1.min
db1/101_1.qm
db1/101_1.tif
db1/101_1.wsq
db1/101_1.xyt

One of these files is of special interest to us: the one with the ".xyt" extension. Take a look of its contents:

head db1/101_1.xyt
85 102 0 13
86 142 174 12
88 90 0 13
104 79 0 35
111 92 90 36
120 31 17 82
129 54 5 39
135 176 90 78
136 143 84 74
140 30 95 42

In this ".xyt" file created by the MINDTCT program, the minutiae values are written in "x y theta quality" format, each minutia per line. This file format is what NBIS matcher program, BOZORTH3, considers. We'll use it further in this article.

Likewise, we could extract the features of the entire dataset, by executing this small Shell Script:

for i in `seq 1 4`
do
  for a in db$i/*.wsq
  do
    echo "$a"
    b="${a/.wsq/}"
    mindtct $a $b
  done
done

After a little while, you can verify whether all XYT files were created by issuing this command:

find -name "*.xyt" | head
./db1/103_3.xyt
./db1/109_4.xyt
./db1/106_1.xyt
./db1/108_1.xyt
./db1/102_5.xyt
./db1/104_5.xyt
./db1/102_1.xyt
./db1/105_4.xyt
./db1/105_3.xyt
./db1/102_4.xyt

3.4. Performing fingerprint matches through BOZORTH3

Now that we have extracted the minutiae from every fingerprint image in the dataset, we can finally use the matcher algorithm. In the case of NBIS, it is embedded in a program called BOZORTH3 [1].

NBIS bundle provides BOZORTH3, a fingerprint matching system. It uses the minutiae detected by MINDTCT to determine if two fingerprints are from the same person, same finger [14]. It can analyze fingers two at a time or run in a batch mode comparing a single finger (probe) against a large database of fingerprints (gallery).

The program BOZORTH3 computes match scores from fingerprint minutiae files. The files are expected to be in xyt-format, a simple text file format that is produced by the minutiae detector program MINDTCT, which is also part of the NBIS distribution.

Considering the specific file "db1/101_1.xyt" we generated previously, as well as all others ".xyt" in that same directory, we could invoke BOZORTH3 in order to compare the fingerprint "101_1.tif" with the rest images in the same database (i.e., residing in "db1/" directory):

bozorth3 -m1 -A outfmt=spg -T 20 -p db1/101_1.xyt db1/*.xyt
271 db1/101_1.xyt db1/101_1.xyt
59 db1/101_1.xyt db1/101_2.xyt
25 db1/101_1.xyt db1/101_4.xyt

The output states that there are 3 possible fingerprint candidates that match the "101_1.tif" probe, where the first value is the number representing the matching score. As a good practice, we set a match score threshold (note the "-T 20" argument) in the matcher. Once the threshold is specified, only match scores meeting or exceeding that value are printed.

The first line represents exactly the same image as the probe ("101_1"). That explains the highest score value. The remaining two are fingerprints that the matching algorithm identified as possibly the same, "101_2" having higher probability than "101_4".

But as we know by the FVC2004 database, there are 8 versions for each fingerprint, which means in an ideal AFIS system all them should be listed:

find db1/101_?.xyt
db1/101_1.xyt
db1/101_2.xyt
db1/101_3.xyt
db1/101_4.xyt
db1/101_5.xyt
db1/101_6.xyt
db1/101_7.xyt
db1/101_8.xyt


Well, that is an issue concerning "Error Rates" in security systems, particularly "False Acceptance Rate (FAR)" and "False Rejection Rate (FRR)", but that is a whole new subject I'll keep for another article. :D

At least other fingerprint images in the same dataset (e.g., "102_*", "103_*", etc) didn't have high scores by the matcher algorithm.

In other words, in this case of NBIS algorithms against FVC2004 database, we are facing a high FRR value (i.e., many genuine fingerprints don't match) and a low FAR value (i.e., it is difficult to a fake fingerprint match).

If we would want to compute the matches of our entire dataset, considering each fingerprint as a probe at a time, we could execute the following Shell Script:

for i in `seq 1 4`
do
  for a in db$i/*.xyt
  do
    echo "[$a]"
    bozorth3 -m1 -A outfmt=spg -T 20 -p $a db$i/*.xyt
    echo
  done
done

For instance, note how good is the matching score for the fingerprint named "104_4.tif" in the "db3" dataset:

[db3/104_4.xyt]
80 db3/104_4.xyt db3/104_1.xyt
59 db3/104_4.xyt db3/104_2.xyt
136 db3/104_4.xyt db3/104_3.xyt
948 db3/104_4.xyt db3/104_4.xyt
113 db3/104_4.xyt db3/104_5.xyt
72 db3/104_4.xyt db3/104_7.xyt
61 db3/104_4.xyt db3/104_8.xyt


4. Summing up


4.1. A complete Shell Script

In order to reproduce all the steps explained before, I suggest you to create a single Shell Script, named "02-run-nbis.sh", inside the "images/" directory, with the following content:

#!/bin/bash

if ! which bozorth3
then
  echo "NBIS programs not in shell path"
  exit 1
fi

echo "Converting TIFF images to WSQ format..."
find db1/ -name "*.tif" -exec cwsq .75 wsq {} -r 640,480,8 \;
find db2/ -name "*.tif" -exec cwsq .75 wsq {} -r 328,364,8 \;
find db3/ -name "*.tif" -exec cwsq .75 wsq {} -r 300,480,8 \;
find db4/ -name "*.tif" -exec cwsq .75 wsq {} -r 288,384,8 \;

echo "Checking quality of WSQ images through NFIQ..."
for i in `seq 1 4`
do
  for a in db$i/*.wsq
  do
    echo "$a"
    nfiq $a
  done
done

echo "Extracting features from fingerprints through MINDTCT..."
for i in `seq 1 4`
do
  for a in db$i/*.wsq
  do
    echo "$a"
    b="${a/.wsq/}"
    mindtct $a $b
  done
done

echo "Performing fingerprint matches through BOZORTH3..."
for i in `seq 1 4`
do
  for a in db$i/*.xyt
  do
    echo "[$a]"
    bozorth3 -m1 -A outfmt=spg -T 20 -p $a db$i/*.xyt
    echo
  done
done

exit 0

Then you should give the file proper permissions and run it using the following instructions:

source nbis-env.sh

cd images/

chmod +x 02-run-nbis.sh

./02-run-nbis.sh

That's all for today, folks! We have reached the end of our article. I hope you enjoyed achieving the proposed tasks, as much as I enjoyed writing them. :D


References

Evaluating NIST Biometric Image Software (NBIS) using FVC2004 Databases - Part 1

This series of articles aim at evaluating NBIS biometric software by using it against fingerprint image databases from FVC2004 verification competition.




1. Contextualization matters


1.1. First of all, what is NBIS?

NBIS, which is an acronym for NIST Biometric Image Software [1], is a biometric software distribution developed by the National Institute of Standards and Technology (NIST) for the Federal Bureau of Investigation (FBI) and the Department of Homeland Security (DHS).



The NBIS software bundle provides a collection of application programs, utilities, and source code libraries. It is organized in two categories: i) non-export controlled and ii) export controlled.

The non-export controlled NBIS software is comprised of five major packages:

  • PCASYS: a neural network based fingerprint pattern classification system;
  • MINDTCT: a fingerprint minutiae detector;
  • NFIQ: a neural network based fingerprint image quality algorithm;
  • AN2K7: a reference implementation of the ANSI/NISTITL 1-2000 "Data Format for the Interchange of Fingerprint, Facial, Scar Mark & Tattoo (SMT) Information" standard; and
  • IMGTOOLS: a collection of image utilities, including encoders and decoders for Baseline and Lossless JPEG and the FBI's WSQ specification.

The export controlled NBIS software is organized into two major packages:

  • NFSEG: a fingerprint segmentation system useful for segmenting four-finger plain impressions; and
  • BOZORTH3: a minutiae based fingerprint matching system.

NIST software is publicly available for download [2] and it is apparently continuously developed to present days. Its last version, 5.0.0, was released in April of 2015.

1.2. Second, what is FVC2004?

The Fingerprint Verification Competition (FVC) is an international competition focused on fingerprint verification software assessment [3]. A subset of fingerprint impressions acquired with various sensors was provided to registered participants, to allow them to adjust the parameters of their algorithms. Participants were requested to provide enroll and match executable files of their algorithms; the evaluation was conducted at the organizers' facilities using the submitted executable files on a sequestered database, acquired with the same sensors as the training set.

These events received great attention both from academic and industrial biometric communities. They established a common benchmark, allowing developers to unambiguously compare their algorithms, and provided an overview of the state-of-the-art in fingerprint recognition. Based on the response of the biometrics community, last editions of FVC were undoubtedly successful initiatives.

FVC2004 [4], the Third International Fingerprint Verification Competition, was held in 2004. The contest involved four different databases (three real and one synthetic) [5] collected by using the following sensors and technologies:

  • DB1: optical sensor "V300" by CrossMatch [6];
  • DB2: optical sensor "U.are.U 4000" by Digital Persona [7];
  • DB3: thermal sweeping sensor "FingerChip FCD4B14CB" by Atmel [8]; and
  • DB4: synthetic fingerprint generation by SFinGe v3.0 [9].

At the end of the data collection, for each database a total of 120 fingers and 12 impressions per finger (1440 impressions) were gathered. As in previous editions, the size of each database to be used in the test was established as 110 fingers wide and 8 impressions per finger deep (880 fingerprints in all); collecting some additional data gave a margin in case of collection/labeling errors.

Fingers from 101 to 110 (set B) have been made available to the participants to allow parameter tuning before the submission of the algorithms; the benchmark is then constituted by fingers numbered from 1 to 100 (set A).

Sensor Type Image Size Set A (wxd) Set B (wxd) Resolution
DB1 Optical Sensor 640x480 (307 Kpixels) 100x8 10x8 500 dpi
DB2 Optical Sensor 328x364 (119 Kpixels) 100x8 10x8 500 dpi
DB3 Thermal sweeping Sensor 300x480 (144 Kpixels) 100x8 10x8 512 dpi
DB4 SFinGe v3.0 288x384 (108 Kpixels) 100x8 10x8 about 500 dpi

The following figure shows a sample image from each database:



2. Gentlemen, start your engines!


2.1. Compiling and setting up NBIS

Well, now that we know what NBIS and FVC are, it is time to put the things to work! We'll be considering Linux operating system from now on. :D

First of all, we need to download NBIS source codes, which are available on NIST's site [2]. At the time this post was being written, the last version was "Release 5.0.0".

Once the ZIP file is downloaded, we need to extract its files and rename the main directory by issuing these commands:

unzip nbis_v5_0_0.zip

mv Rel_5.0.0/ nbis-5.0.0/

cd nbis-5.0.0/

Then, as a good practice in Linux, let's assume a proper directory to install NBIS files: "/opt/nbis-5.0.0/".

mkdir /opt/nbis-5.0.0/

./setup.sh /opt/nbis-5.0.0/

You might face some dependency issues after running "setup.sh". This means you'll need to previously install some packages on your system. Do it before following the next step! For instance, on CentOS 6.x these packages were needed: cmake, libpng-devel, and libX11-devel.

Once "setup.sh" has been executed successfully, you'll run a series of "make" instructions:

make config
make it
make install
make catalog

Very good! At this time you already have 1) prepared the NBIS source codes, 2) compiled them, 3) installed it on /opt, and 4) created the catalog for libraries and programs.

As a suggestion, create a simple Shell Script in order to export system variables, named "nbis-env.sh", and with the following content:

#!/bin/bash

NBIS_HOME="/opt/nbis-5.0.0"

export PATH=$PATH:$NBIS_HOME/bin/
export MANPATH=$NBIS_HOME/man/

How does it work? Simple! Try executing these commands:

cwsq -version

man cwsq

Nothing happened, but errors, right? Now try typing this:

source nbis-env.sh

From now on, NBIS programs and manuals are on the path.

Let's check the version of CWSQ program:

cwsq -version
Standard Version: ANSI/NIST-ITL 1-2007
NBIS Non-Export Control Software Version: Release 5.0.0

And now take a look of its manual:

man cwsq

Well done! This simple Shell Script snippet serves to check whether NBIS programs are available in the path:

if ! which bozorth3; then echo "NBIS programs not in shell path"; fi

2.2. Let's fetch the database files

In order to facilitate retrieving and extracting the files from the FVC2004 site [10], I suggest you to write a simple Shell Script, named "01-get-images.sh", with the following content:

#!/bin/bash

# create directory for the ZIP files
if [ ! -d zips ]
then
  mkdir zips
fi

# download the files
for i in `seq 1 4`
do
  arq="DB${i}_B.zip"
  if [ ! -f zips/$arq ]
  then
    wget "http://bias.csr.unibo.it/fvc2004/Downloads/${arq}"
    mv $arq zips/
  fi
done

# remove directories
rm -rf images/

# extract files
for i in `seq 1 4`
do
  mkdir -p images/db$i
  unzip zips/DB${i}_B.zip -d images/db$i/
done

exit 0

Thus, simply give execution permission to the script and run it:

chmod +x 01-get-images.sh

./01-get-images.sh

You'll note the "images/" directory will be filled with several TIFF images from the four FVC2004 set B databases. Take a look at it:

find images/ | head
images/
images/db1
images/db1/104_3.tif
images/db1/105_6.tif
images/db1/107_8.tif
images/db1/105_3.tif
images/db1/106_4.tif
images/db1/109_1.tif
images/db1/110_4.tif
images/db1/102_5.tif


That's it! We finally ended the first article.

In the next article we'll start using NBIS binaries on FVC2004 fingerprint images: Evaluating NIST Biometric Image Software (NBIS) using FVC2004 Databases - Part 2.

References


sexta-feira, 25 de janeiro de 2013

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

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

terça-feira, 11 de setembro de 2012

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

sexta-feira, 24 de agosto de 2012

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