por Adriano Belisário
A análise de dados a partir de tabelas pode ir muito além das operações matemáticas mais usuais. Às vezes, é preciso estudar os padrões espaciais da sua base de dados e, nessas horas, conseguir transformar sua tabela em mapas ou dados geográficos é fundamental.
Imagine que você está viajando com uma lista de diferentes cidades para visitar e deseja visualizá-las no mapa, para identificar quais estão próximas e planejar sua locomoção. Ou que você precisa fazer um mapa com o endereço do seus clientes, para planejar uma logística de entrega.
Em situações assim, um mapa nos revela muito mais do que uma tabela. Mas como podemos passar de uma lista de endereços em uma tabela para um mapa? É isso que você irá aprender neste tutorial.
É possível realizar este processo usando serviços pagos ou linguagens de programação, como Python ou R. Aqui, porém, utilizaremos o Open Refine e Open Street Maps (OSM), que facilitam o trabalho para quem não sabe programar e precisa de soluções gratuitas.
O GeoJSON é um formato de dado geográfico, que utilizaremos neste tutorial. Ele é baseado no WGS-84 para identificar os pontos na terra, o mesmo sistema de coordenadas da tecnologia GPS. Fonte da imagem: GIS Geography
Resumo
A versão resumida (TLDR) do tutorial é a seguinte:
1. Instale o Open Refine e importe os dados desejados;
2. Na coluna do campo de “Endereço”, selecione a opção “Edit column” > “Add column by fetching URL”. Insira o texto abaixo no campo ‘Expression’, alterando os textos em negrito. Acompanhe o tutorial para saber mais sobre os parâmetros de busca.
“https://nominatim.openstreetmap.org/search?city=Rio de Janeiro&format=json&street=” + value
3. Será criada uma nova coluna com o arquivo JSON retornado pelo Open Street Maps. Para extrair apenas a latitude, selecione a nova coluna e vá na opção “Add column based on”. Então, coloque o seguinte código:
with(value.parseJson()[0], pair, pair.lat)
4. Repita o procedimento para adicionar a longitude, com a seguinte fórmula:
with(value.parseJson()[0], pair, pair.lat)
5. Repita mais uma vez o procedimento para adicionar um campo que irá mostrar o endereço por extenso da latitude e longitude anterior. Isto ajuda a checar se a localização de fato corresponde ao endereço utilizado na consulta.
with(value.parseJson()[0], pair, pair.display_name)
6. Se quiser, delete a coluna criada na “etapa 3”, com o JSON bruto, e exporte o resultado como CSV.
7. Converta a tabela para o formato de GeoJSON, usando o Ogre
8. Visualize no mapa rapidamente ou exporte para outros formatos com GeoJSON.io
Agora que você já tem um panorama do que iremos fazer, vamos falar detalhadamente sobre cada etapa. Iremos começar entendendo melhor o serviço de geocodificação do Open Street Maps (Nominatim) e suas possibilidades.
Como funciona o Nominatim?
Para transformar endereços (textos) em coordenadas geográficas, precisamos usar um serviço que receba uma string (sequência de caracteres, no caso, o endereço) e nos retorne a latitude e longitude desta posição. Esse processo é conhecido como geocodificação (geocoding) e existem diversos serviços pagos e gratuitos na internet para fazer isso.
Aqui, iremos usar uma solução de código-aberto, gratuita e que não requer registro. Trata-se da API do Nominatim, que faz uso da base de dados do Open Street Maps.
Com ela, conseguimos fazer consultas em texto e obter arquivos em formatos HTML, XML, JSON, GEOJSON, que trazem a localização dos endereços, caso eles estejam na base do Open Street Maps. Se você fizer uma consulta via plataforma web, verá que a URL obedece ao seguinte padrão:
https://nominatim.openstreetmap.org/search.php?q=rua+jogo+da+bola&polygon_geojson=1&viewbox=
No trecho destacado em azul claro, você tem o endereço principal da página de consulta do Nominatim. Depois do ponto de interrogação, começamos a passar alguns termos da nossa consulta (por isso, “q” de query).
Seguindo, em vermelho, temos o termo de busca, que varia conforme a consulta. E, em seguida, alguns parâmetros que não nos interessam. Lendo a documentação da API, descobrimos que é possível especificar o formato desejado para os dados, com um parâmetro. Vamos definir então “format=JSON”.
https://nominatim.openstreetmap.org/search.php?q=rua+jogo+da+bola&format=json
Se você inspecionar este resultado, já vai conseguir enxergar os dados de latitude e longitude. Se reparar bem, vai perceber que são retornados diversos resultados, inclusive endereços em Portugal. Mas e se você quiser filtrar o resultado por uma cidade ou país em específico?
Neste caso, a URL de consulta à API seria um pouco diferente, mas a ideia geral de passar parâmetros permanece. Ao invés de fazer uma consulta (query) livre, onde se misturam diversas informações, você faz uma consulta estruturada. Na documentação da API do Nominatim, há a descrição dos parâmetros, formatos e filtros possíveis.
Agora, ao invés de somente um texto, passamos quatro parâmetros: país (country), cidade (city), rua (street) e formato no qual queremos os resultados (format). Os valores de cada um estão destacados em laranja na URL acima e podem ser alterados, conforme sua necessidade, inclusive de forma dinâmica, em situações onde você tenha mais dados com registros de mais de uma cidade.
Importando os dados no Open Refine
Já entendemos como funciona a API que iremos usar. Agora vamos botar a mão na massa. Se você tiver dificuldades para instalar ou executar o Open Refine, primeiro leia a documentação oficial do projeto e, se mesmo assim não conseguir resolver, poste uma dúvida no forum.jornalismodedados.org. Como o processo é relativamente simples, iremos focar aqui na parte da geocodificação.
Neste tutorial, iremos usar como exemplo esta planilha com endereços de delegacias de atendimento à mulher no Rio de Janeiro. Os dados foram retirados desta página oficial da Polícia Civil e, como você pode reparar, foram tratados, de modo que contenham apenas o nome da rua e o número do estabelecimento.
A forma como os dados estão organizados é que vai ditar os desafios nesta etapa, que é fundamental para a geocodificação dar certo. Se você tem uma coluna para o nome da rua, outra para o número, outra para o bairro, além de colunas específicas para cidade, estado e país, então, você está com sorte. Com dados bem estruturados, você pode usar a API de consultas estruturadas “mapeando” as colunas com cada tipo de informação com os respectivos campos da API e tem boas chances de ter excelentes resultados. Porém, na prática, provavelmente, você terá que lidar com dados mais… bagunçados. A depender do volume e da existência de padrões, você pode organizar e padronizar as informações no próprio Open Refine ou de forma manual. Nestes casos, vale avaliar se é melhor usar a “consulta livre” à API ou tentar limpar/organizar os campos da sua tabela para utilizar a consulta estruturada.
Ou seja, aqui, os campos foram ajustados para conter apenas o endereço, removendo informações extras, tais como cidade, CEP e etc. Provavelmente, você terá que fazer também o mesmo trabalho para garantir melhores resultados, especialmente se você for utilizar a consulta estruturada.
Para seguir com o exemplo deste tutorial, faça o download da planilha linkada acima e abra este arquivo no Open Refine. Confira se a importação foi feita corretamente e crie o seu projeto, clicando no botão ‘Create project’.
Consultando a API do Nominatim
Selecione a coluna com os endereços e vá na opção: Edit column > Add column by fetching URL.
Depois, defina um novo nome para a coluna, em “New Column Name”. Vamos escolher aqui o texto “resultados_json” como título para esta coluna, que será temporária. No campo “Expression”, adicione o texto abaixo:
“https://nominatim.openstreetmap.org/search?city=Rio de Janeiro&format=json&street=” + value
Esta expressão irá montar a URL que será requisitada para preencher o conteúdo de cada célula, de acordo com o endereço (representado pelo “value”). O texto em negrito pode ser alterado à vontade.
Se você tem uma outra coluna onde estão registrados diferentes nomes das cidades, você poderia usar a seguinte expressão para formar uma URL dinâmica, consultando não só com os endereços, mas também com as cidades:
“https://nominatim.openstreetmap.org/search?format=json&city=” + cells[“cidade“].value + “&street=” + value
Neste caso, o texto em negrito representa o nome da coluna que contém as cidades. Neste exemplo, esta coluna se chamaria “cidade”.
DICA: Em processo manuais ou semi-automatizados de coleta de dados, a dica é sempre checar os dados durante o processo de coleta. Assim, você já vai se certificando que as coisas estão saindo do jeito planejado. A esta altura, você consegue ver no “Preview” qual link será consultado para preencher cada célula. Utilize essa amostre e abra alguns destes links no seu navegador para se certificar que há consulta funcionou e há dados ali para serem importados.
O campo “Throttle delay” define o intervalo das requisições que serão feitas ao servidor do Open Street Maps, em milisegundos. Quanto menor este valor, mais rápido você terá os resultados. Porém, não utilize valores menores que 1.000ms por pedido para não ferir o termo de uso do serviço.
Depois de dar “OK”, o Open Refine irá começar a consultar a API e obter os resultados. Este processo pode demorar, a depender do número de registros da sua tabela.
Checando a importação
Vamos novamente checar a nossa importação. Você verá que as células da nova coluna foram preenchidas com o JSON obtido via API, porém, pode ser que os resultados tenham vindo em branco em alguns casos. Para checar quais foram, basta ir na nova coluna recém-criada e selecionar a opção Facet > Text Facet.
Do lado esquerdo, selecione a opção com colchetes vazios (“[]”) para filtrar apenas os resultados onde a geocodificação falhou. No nosso exemplo, dos 28 endereços, em apenas 2 a consulta não prosperou.
Você pode verificar se o texto dos endereços usado na busca está correto e eventualmente fazer ajustes seguidos de uma nova consulta, mantendo este filtro ativo. Se forem poucos casos, é possível sempre também buscar e definir manualmente a latitude e longitude da localização. Mas este não é o único problema possível.
Além de vir em branco, pode ser que o OSM tenha geocodificado seu endereço de forma errada. Por exemplo, em casos de nomes de ruas muito comuns, é possível que o mesmo nome ocorra em vários bairros e o sistema não faça a localização adequada.
Para filtrar apenas o endereço por extenso entre as várias informações do JSON, basta selecionar a nova coluna (resultado_json, no nosso caso) e ir na opção: Edit Column > Add column based on this column.
Coloque um novo nome para a coluna e insira o seguinte código ‘Expression’:
with(value.parseJson()[0], pair, pair.display_name)
Agora, já extraímos o endereço do JSON e podemos checar os resultados. Repare que o JSON retorna várias localizações. Ao utilizar o “[0]” na consulta acima, estamos indicando que queremos obter os dados apenas do primeiro deles.
Extraindo latitude e longitude
Para extrair as informações de latitude e longitude, o processo é bem semelhante. Na coluna com o JSON, selecione ‘Edit Column’ > ‘Add column based on this column’. Defina o título como “lat” e insira o código:
with(value.parseJson()[0], pair, pair.lat)
Repita o procedimento, adicionando agora o título “lon” e alterando o código para extrair também a longitude:
with(value.parseJson()[0], pair, pair.lon)
Agora, você já pode remover a coluna com o JSON. Basta ir em ‘Edit Column’ > ‘Remove this column’.
Para o bom funcionamento da próxima etapa, é necessário que os campos de latitude e longitude estejam respectivamente com os títulos “lat” e “lon”.
Transformando CSV em GeoJSON
Por fim, precisamos exportar os dados do Open Refine (Export > Comma-separated value) e transformar este CSV em um arquivo de dados geográficos (GeoJSON).
O GeoJSON é um formato de arquivos, definido por um padrão internacional (RFC 7946) que permite representar recursos geográficos, junto com atributos não espaciais. Como o nome indicado, ele é baseado no JSON, um formato bastante comum em APIs. Ele usa o WGS-84, World Geodetic System, um sistema de coordenadas para representação de locais na Terra, baseado no assim chamado “sistema geodésico mudial” (alô, terraplanistas! :).
Para converter um CSV para GeoJSON, vamos utilizar um serviço online, o Ogre, que é gratuito e não exige registro. Basta subir o arquivo CSV criado no campo “File” da seção “Convert to GeoJSON”. Marque a opção “Force Download” e clique no botão “Convert to GeoJSON”.
Pronto, agora você já tem um arquivo GeoJSON, com as coordenadas criadas.
Visualizando no mapa
Se você quiser ver o resultado no mapa, uma forma fácil é usar o site Geojson.io ou ferramentas como o QGIS. No primeiro caso, basta acessar esta plataforma online e selecionar opção ‘Open’ > ‘File’.
Confira abaixo o mapa gerado com nossa tabela de exemplo.
O processo de geocodificação pode não ser perfeito e exige sempre a checagem dos resultados, mas é uma etapa fundamental para quem quer colocar os dados no mapa.
O tutorial foi útil? Você tem outras sugestões de outras abordagens e referências sobre o tema? Então, compartilha seu comentário abaixo! E, em caso de dúvidas mais práticas sobre geocodificação de dados, você pode postar sua questão no forum.jornalismodedados.org.
