base de referência - Conferência Brasileira PostgreSQL

Sobre o autor:
• Geógrafo (Universidade Federal de Uberlândia)
• Experiência anterior
• Contato com SGBDs teórico e prático;
• Quatro anos de experiência em Sistemas de
Informações Geográficas e Geoprocessamento;
• Atualmente trabalha com exploração mineral;
Sobre o trabalho:
• Desenvolvido em ambiente Windows com PostgreSQL 8.3
, PostGIS 1.3;
• Tempo empreendido: 1 ano, em diversas etapas e ritmos
de trabalho;
• Tema de monografia, defendido em 2009;
Objetivos:
• Demonstrar a utilização do PostGIS em
aplicações de saúde e segurança pública;
• Implementar um algoritmo geocodificador
capaz de lidar com vários formatos de
endereçamento;
• Promover softwares livres em ambientes
corporativos, como o de uma prefeitura;
Sumário:
• O que são sistemas de informações geográficas?
• O que é o PostGIS? Quais são suas capacidades?
• Acidentes de trânsito e a segurança pública;
• Modelo de dados;
• Geocodificação;
• Automatizando a geocodificação;
• Testando o algoritmo;
O que são Sistemas de Informações Geográficas?
O que são Sistemas de Informações
Geográficas (SIG / GIS)?
• É um sistema computacional capaz de criar, armazenar e manipular
dados geográficos;
• É uma ferramenta ampla, envolvendo diversos ramos da ciência e
áreas do conhecimento;
• Utiliza objetos espaciais para representar a realidade (pontos,
linhas, polígonos, superfícies, etc.);
• Difere de um sistema CAD (Computer Aided Design): cada objeto,
dentro de um SIG armazena informações do mundo real, ao passo
que para um CAD estes objetos não significado algum, são apenas
objetos;
O que são Sistemas de Informações
Geográficas (SIG / GIS)?
• Áreas do conhecimento que são, geralmente, utilizadas por um SIG:
Sistema de Informações
Geográficas (SIG ou GIS)
Sensoriamento
Remoto
Cartografia e
Geodésia
Banco de
Dados
Análise
Espacial
Então, o que é o PostGIS?
• É uma extensão que habilita o PostgreSQL à tratar dados espaciais;
• Atualmente se encontra na versão 1.4;
• É a extensão espacial mais completa do mercado;
• Segue o padrão internacional OGC (Open Geospatial Consortium),
o que significa interoperabilidade e facilidade para integrar dados
de diversas fontes;
• Utiliza a SQL para trabalhar com os dados. Não existe necessidade
de se aprender uma nova linguagem de consulta. Ela é a mesma
utilizada pelo PostgreSQL;
O que o PostGIS pode fazer?
• Suporta diversos tipos de geometrias e suas coleções: ponto, linha,
polígono, multi* e coleções de geometrias. Suporte total à
geometrias 3D;
• Suporte completo à centenas de projeções cartográficas e suas
transformações. Permite representar a realidade de forma mais
precisa. Fornecido pela biblioteca Proj4;
• Suporte a diversas operações espaciais, como: interseção, união,
toca, contém, está contido por, interpolação linear, etc;
• Utiliza índices GisT (Generalized Search Tree) para indexar dados
espaciais.
Sobre os acidentes de trânsito:
• É um evento inesperado, envolvendo um ou mais veículos e ou
pedestres, resultando em danos à integridade do indivíduo ou à
bens materiais;
• No Brasil são mais de 350 mil acidentes anuais, com mais de 33 mil
óbitos e 400 mil feridos ou incapacitados;
• Consiste em um grave problema nas cidades de médio e grande
porte de todo o mundo;
• Gera enorme custo para a sociedade: uma vítima ferida em um
acidente de trânsito custa em média R$36.305,00 (IPEA, 2006);
• É um assunto de saúde e segurança pública;
O modelo de dados
Modelo de dados
• Quando uma pessoa lhe relata um acidente de trânsito,
quais são as coisas mais comuns que lhe perguntamos?
• Estado de saúde, se houveram ferimentos, etc;
• Onde o acidente ocorreu?
• Quando ocorreu?
• Como ocorreu? Estava chovendo? Qual era o estado de
saúde do outro motorista? A pista estava danificada ou
mal sinalizada?
Modelo de dados
• No cadastro de acidentes de trânsito, as perguntas acima
representam:
• Severidade;
• Um endereço e um par de coordenadas;
• Data e Hora;
• Característica do acidente;
• Informações sobre os veículos (placa, ano de fabricação, tipo do
veículo, etc) e informações sobre os condutores (estado físico,
comportamento, situação da habilitação, idade, sexo, etc);
Modelo de dados
• Todo o sistema é composto de 3 tabelas principais, onde todos os
registros são inseridos. São elas:
• Acidentes
• Veículos
• Condutores
• Além de diversas tabelas auxiliares (lookup tables), como possíveis
valores de tipos de veículos, tipos de acidentes, características de
acidentes, etc;
Modelo de dados
• A única tabela que possui
geometria é a tabela acidentes;
• Relação 1:M entre acidentes
e veículos;
• Relação 1:1 entre veículos e
condutores;
• Relação 1:M entre acidentes
e condutores;
• O modelo conta com as tabelas auxiliares para
validar a entrada de informações e para sua extensão,
se preciso;
Modelo de dados
Modelo de dados
Modelo de dados
Geocodificação
O que é Geocodificação?
É o processo de associar uma localização na forma de um
par de coordenadas, à um endereço, através da
comparação de seus elementos com uma base de
referência.
Existem diversos geocodificadores online: Google Maps,
Apontador.com.br, entre outros;
Pré-requisitos para geocodificação:
• Existem alguns pré-requisitos à serem cumpridos, para que um
algoritmo possa localizar e pontuar efetivamente um endereço.
• Uma base de referência georreferenciada, composta pelas linhas
centrais de cada logradouro. A base de referência necessita possuir os
seguintes atributos: geometria, tipo logradouro, nome logradouro,
numeração inicial e final, interseção anterior e posterior;
• Um serviço geocodificador, capaz de orientar o algoritmo através da
base de referência;
Base de referência (vista em software SIG):
Base de referência (vista tabular)
Atributo
geometria
Numeração Inicial e
Final
Como funciona a geocodificação:
• Inserção do endereço alvo;
• Quebra do endereço em elementos;
• Abreviação e padronização dos elementos (Regex e metaphone);
• Comparação com a base de referência;
• Cálculo de registros mais prováveis;
• Retorno dos resultados;
Tipos de geocodificação:
• Existem três tipos de geocodificação neste conjunto de ferramentas.
Dois foram desenvolvidos especificamente para este trabalho, e o
outro, mais complexo, adaptado para trabalhar com endereços no
sistema brasileiro;
• Interpolada
• Entre interseções viárias
• Em cruzamentos
Geocodificação interpolada:
• Funciona conforme descrito acima. Algoritmo adaptado de David
Bitner;
• Utiliza uma simples
regra de três
e a
função
ST_LINE_INTERPOLATE_POINT (geometria, double) para localizar um
ponto;
((NumFinal – NumInicial)*NumPesquisado)/100 -> determina o valor
do segundo parâmetro
Geocodificação interpolada:
• A geocodificação interpolada retorna uma série de registros,
derivados de um tipo de usuário, chamado geocode_result;
geometria do logradouro
geometria pontual
localizada
ranking de probabilidade
(5 sendo a maior nota)
tempo de resposta
• Este exemplo acima é de uma query adhoc, somente para testes.
Note que o geocode_result retorna a geometria do trecho de
logradouro e a geometria pontual localizada, além de outros
atributos, como o endereço completo do local (formato norteamericano);
Geocodificação entre interseções viárias
• Localiza o logradouro de acordo com seu nome, interseção anterior
e interseção posterior;
• SELECT * FROM logradouros WHERE nome_logradouro = ‘x’ and
intersecao_anterior = ‘y’ and intersecao_posterior = ‘z’;
• Também utiliza a função ST_LINE_INTERPOLATE_POINT, mas com
um valor de .5, determinando o ponto médio do logradouro;
Geocodificação Em Cruzamentos
• Localiza o logradouro de acordo com seu nome e interseção
posterior;
• SELECT * FROM logradouros WHERE nome_logradouro = ‘x’ and
intersecao_posterior = ‘y’;
• Também utiliza a função ST_LINE_INTERPOLATE_POINT, mas com
um valor de .99, determinando o final do logradouro principal;
Serviço Geocodificador
• O serviço geocodificador é composto basicamente de três tabelas:
geocoders, types e dirs;
algoritmo
geocoders
Bases de referência
types
dirs
• A tabela geocoders armazena todas as informações necessárias para
direcionar o algoritmo de acordo com determinada base de referência
Serviço Geocodificador
• Tabela types: armazena valores de comparação aos tipos de
logradouros. Possui uma coluna com o valor descritivo e outra com
uma string de expressão regular;
• Tabela dirs: armazena valores de comparação às direções que cada
logradouro toma. Este tipo sistema é muito utilizado em outros
países, como Estados Unidos, mas no Brasil, não mostra muita
utilidade.
•Neste caso, ao invés de direções, esta tabela atua como um
repositório de nomes de logradouros, para facilitar a identificação das
interseções anteriores e posteriores de cada trecho. Também possui
uma coluna para o valor descritivo e outra para uma string de
expressão regular, utilizada no momento da comparação;
Serviço Geocodificador
• Qual é a vantagem de termos um serviço geocodificador?
• Separamos o algoritmo de nossas bases de dados. Basta inserir um
novo registro na tabela geocoders e apontar os campos necessários e
já podemos iniciar os procedimentos, utilizando outras bases de
referência;
• Podemos ter várias tabelas do tipo dirs e types, já que as mesmas
também são referenciadas em geocoders;
• Podemos, à qualquer momento, escolher qual base de referência
iremos utilizar para a geocodificação;
Automatizando a geocodificação
• A geocodificação é disparada por três funções pl/pgsql (conforme
citado acima)
• Um trigger AFTER INSERT foi construída na tabela acidentes, que
dispara uma das funções correspondentes com os dados inseridos;
Possui número
viário?
SIM
Geocodificação
interpolada
NÃO
Possui ambas
interseções?
SIM
Geocodificação
entre interseções
NÃO
Possui interseção
posterior?
SIM
Geocodificação
em cruzamento
dados inseridos
trigger
UPDATE the_geom
Porque automatizar a geocodificação?
• A idéia por trás deste trabalho foi tirar a “responsabilidade” das
mãos de um usuário leigo em geoprocessamento e SQL. Usuários
proficientes nestas tecnologias devem executar outras funções;
• Não há perda de perfomance significativa para inserção de registros
um a um. O tempo médio da geocodificação interpolada é de 90ms;
• A vantagem é termos a garantia da geocodificação estar sendo
executada em cada registro, sem a necessidade de posteriormente
processar todos endereços de um só vez. Diminui a chance de erros;
• Dados espaciais em “tempo real”. Acidente inserido é acidente
geocodificado. Ideal para um futuro serviço de mapas;
Testando o algoritmo
• Era imperativo testar o algoritmo e suas chances de sucesso contra a
base de referência construída.
• Não foi testada a precisão posicional de cada resultado. A posição de
cada ponto localizado depende inteiramente da acurácia da base de
referência, levantada basicamente por trabalhos de campo e com o
uso de um Guia Sei, sendo bastante apurada;
• Os testes principais envolviam a capacidade do algoritmo localizar
ou não um endereço passado ao mesmo. Todas as formas de
geocodificação foram testadas, através de um gerador aleatório de
acidentes;
• O algoritmo gera aleatoriamente veículos e condutores, ideal para
construir uma base de dados para testes com consultas, views, etc;
Gerador de Acidentes
Testando o algoritmo
• Foram gerados 500 mil acidentes, em lotes de 100 mil, e aferida a
taxa de acerto do algoritmo;
• Um acerto significa que a função disparada pelo trigger retornou um
ponto e atualizou o campo geometria da tabela acidentes;
• Uma falha significa que a função disparada pelo trigger não
conseguiu retornar um ponto e não atualizou o campo geometria;
• A média de acerto do algoritmo, nestes cinco testes, foi de 98%;
• A principal causa destes erros foi o intervalo numérico entre o fim e início de
determinados logradouros. Nem sempre um trecho de logradouro que termina em
100 se inicia novamente em 101, deixando “janelas” na malha (são número viários
não existentes e não serão cadastrados em uma situação real);
Conclusões
• O trabalho foi bem sucedido nas proposições;
• O algoritmo funciona como esperado e sem overhead para o
servidor;
• O algoritmo é capaz de localizar a maioria dos endereços “atirados à
ele”. Uma inspeção posterior pode apontar pontos falhos na base de
referência e em produção os erros devem ser menores;
• O modelo de dados para o cadastro de acidentes é funcional,
permite adaptação e consegue responder diversas perguntas de
forma simples. Existe margem para melhoras, mas o desempenho e
flexibilidade foram satisfatórias;
Obrigado pela atenção!
Dúvidas?
George Rodrigues da Cunha Silva
[email protected]