Num projecto recente de powerbi tive de colocar num mapa os estabelecimentos comerciais de um cliente e achei que para complementar seria interessante ter no mapa assinaladas as localizações da concorrencia.
A ideia acabou por ser mais complicada do que inicialmente previsto já que achar uma base de dados com as localizações dos estabelecimentos da área de negócio em questão revelou-se mais dificil que o previsto.
Acabei por me virar para o openstreetmaps que apesar de incompleto e com alguma informação incorrecta revelou-se a melhor opção para o projecto em questão. O openstreetmaps é uma espécie de wikipedia dos mapas em que todos podem participar na actualização do mapa. Claro que isso levanta problemas com pessoas a introduzirem informação falsa ou a destruirem propositadamente o trabalho dos outros mas o resultado final é bem mais positivo que negativo.
Agora que tinha escolhido a minha base de dados, era só uma questão de ver como a importar para o powerbi. Estava à espera de complicadas APIs mas a realidade foi muito mais simpática do que o previsto.
EXTRAIR DADOS DO OPENSTREETMAP
Existem várias APIs para comunicar com o openstreetmap, umas permitem ler e escrever dados e outras, como a que overpass que foi a que usei, só permitem ler o que para o meu caso servia perfeitamente.
A melhor maneira de abordar o overpass é através do overpass-turbo.eu que permite fazer consultas e ver o resultado em tempo real. A minha abordagem foi fazer a consulta no overpass-turbo e depois de verificar que tinha o resultado certo passar para o passo seguinte de usar essa consulta para levar os dados para o powerbi.
Quando abrimos o overpass-turbo somos apresentados com uma query e um mapa:
A query, que por defeito, é
node[amenity=drinking_water]({{bbox}}); out;
procura todas os elementos pontuais (node) do tipo drinking_water dentro da área visivel no mapa ({{box}}). Se a executarem ele apresenta no mapa o resultado pedido.
Depois de algum trabalho de pesquisa e martelada consegui, sem ter de perceber muito de over-pass, obter o resultado pretendido. Vamos supor que pretendo obter todas as zonas de banho de Portugal em formato JSON. O código é:
[out:json];
area["ISO3166-1"="PT"][admin_level=2];
nwr[leisure=swimming_area](area);
out center;
Na primeira linha informamos que queremos o resultado em JSON e de seguida definimos a área pretendida como sendo Portugal.
Na terceira linha definimos o pedido como sendo todos os elementos do tipo “zona de banho”na área definida anteriormente. Usamos o pedido NWR para obter nodes, ways e relations que são todo o tipo de elementos que se pode definir no openstreetmaps.
Por fim, pedimos o centro desses elementos. Para nodes, o centro coincide com o próprio elemento, mas para os outros dois que são elementos que têm áreas se não pedíssemos o centro ia-nos devolver todo o elemento, e para o que pretendemos fazer, não funciona.
O resultado é encorajador:
Podemos verificar no canto inferior direito que foram detectados elementos dos três tipos acima mencionados e foram todos convertidos em POIs, ou seja, elementos pontuais.
Com a consulta testada basta converte-la num formato que possa ser usada no powerbi o que se faz indo à opção Exportar no topo esquerdo da página que nos leva à janela abaixo onde escolhemos a opção dados em bruto da API do Overpass:
Esta opção cria, e abre, uma ligação para consultar directamente o overpass:
Convém apagar os comentários na janela do código antes de exportar porque os comentários também são exportados na ligação. Sem texto adicional além do código indicado acima, ficamos reduzidos a:
https://overpass-api.de/api/interpreter?data=
%5Bout%3Ajson%5D%3B%0
Aarea%5B%22ISO3166-1%22%3D%22PT%22%5D%5Badmin_level%3D2%5D%3B%0A
nwr%5Bleisure%3Dswimming_area%5D%28area%29%3B%0A
out%20center%3B
E com este endereço (que tem quebras de linha para ajudar à leitura) podemos passar para o powerbi. Não é preciso ser expert em over-pass para fazer coisas interessantes, mas para quem quiser ir mais longe, fica aqui o site do over-pass.
IMPORTAR OS DADOS PARA O POWERBI
O trabalho difícil está feito, agora é só copiar-colar e cliques de rato.
No powerbi é só escolher Obter dados de outra origem no meio do ecran e de seguida escolher Outras > Web.
De seguida coloca-se o link obtido anteriormente
e o powerbi faz o resto
Agora é só trabalho de limpeza dos dados ao gosto do freguês, mas não sem antes tratar de um pequeno problema! Os nós têm as coordenadas num campo e os outros elementos noutro campo:
Para se poder usar as coordenadas temos de juntar os dois campos num só. Podemos fazê-lo com a operação Intercalar Colunas. Selecionam-se as duas colunas da latitude, botão direito do rato e opção Intercalar Colunas. Depois é fazer o mesmo para as longitudes.
Aproveite e durante o processo dês os nomes lat e lon às novas colunas
Para Finalizar indique o tipo Número Decimal para as novas colunas através do menu que aparece quando se clica com o botão direito do rato em Cima do ABC que indica que as colunas são do tipo texto.
Agora é só fechar a janela de edição de dados através do botão Fechar e Aplicar no canto superior esquerdo.
Já na janela de edição dos relatórios insira um mapa no relatório, e adicione os campos para a latitude e longitude nos respectivos locais. Pode também em descrições adicionar o campo name para este aparecer quando se passa por cima de um ponto no mapa e pronto… está feito!
MAIS ALÉM
Estes são os passos mínimos para conseguir visualizar dados do openstreetmaps no powerbi, mas para um trabalho minimamente digno muito ficou por fazer: verificação dos dados, remoção de colunas desnecessários, alteração de nomes e tipos de colunas, melhoramentos visuais, etc.
Mas o objectivo deste texto quase que ficou concluído na primeira seção sobre como obter os dados do openstreetmaps, tudo o resto é trabalho normal de powerbi e não quis alongar o texto com os inúmeros melhoramentos que deviam ter sido feito.
Mas não vou embora, metaforicamente claro está, sem vos mostrar um trabalho semelhante, mas com um bocado mais de qualidade.
Em tempos escrevi este artigo sobre a religião em Portugal segundo os censos do INE, e usei o powerbi que já tinha, para mostrar informação sobre os locais de culto em Portugal usando o método que aqui vos mostrei.
Neste caso, além de ir buscar o nome e as coordenadas, vou buscar a religião, a denominação (sub-religião) e ainda a descrição e o url para a imagem do local (poucos têm estes dois últimos).
Podem consultar aqui: