VBA Excel | Como obter coordenadas geográficas (latitude e longitude) usando a API do Google Geocoding

Através do VBA, faremos uma abordagem, criando um script que siga a lógica de executar uma chamada à API do Google Geocoding, para cada linha de uma lista, armazenando e manipulando as respostas.

VBA Excel | Como obter coordenadas geográficas (latitude e longitude) usando a API do Google Geocoding

 

QlikView | Qlik Sense: Script de Carregamento usando QlikView Scripting Language faz chamadas para a API do Google Geocoding e Processa a Resposta XML

 Veja também: 

 

 

Configuração de Dados: Suponha que tenha uma tabela com endereços no Excel e deseja obter coordenadas geográficas (latitude e longitude) usando a API do Google Geocoding.

Uso da API do Google: O VBA fará as chamadas HTTP para a API do Google e processará a resposta XML.

Manipulação de Dados: Atualizará a planilha com as respostas da API.

Aplicabilidades

Geocodificação de Endereços:

Localização de Dados: Pode ser usado para converter endereços em coordenadas geográficas (latitude e longitude), o que é útil para aplicações de mapeamento e análise espacial.

Visualização: Ideal para criar mapas de localização em ferramentas como Google Maps ou GIS (Sistema de Informação Geográfica) usando coordenadas.

Análise de Dados:

Análise Geoespacial: Pode ser usado para análise de dados baseada em localização, como identificar padrões geográficos ou realizar análises de proximidade.

Segmentação de Mercado: Empresas podem usar coordenadas para segmentar e analisar mercados com base em localização geográfica.

Integração com Outras Ferramentas:

Relatórios e Dashboards: Integrar dados de localização em relatórios e dashboards no Excel para visualização e análise.

Automatização: Automatizar a coleta de dados geoespaciais para processos de negócios que requerem localização, como planejamento de rotas ou logística.

Verificação e Validação de Endereços:

Correção de Dados: Verificar a precisão dos endereços em uma base de dados e corrigir ou normalizar os dados de endereço.

Exemplo de Código VBA

Aqui está um exemplo básico de como você pode realizar essas tarefas em VBA no Excel:

Option Explicit

Sub ObterCoordenadas()

    Dim ws As Worksheet

    Dim url As String

    Dim http As Object

    Dim xml As Object

    Dim i As Integer

    Dim lastRow As Long

    Dim endereco As String

    Dim chaveunica As String

    Dim resposta As String

    Dim lat As String

    Dim lng As String

    

    ' Definir a planilha onde estão os dados

    Set ws = ThisWorkbook.Sheets("Endereços") ' Substitua pelo nome da sua planilha

    

    ' Configurar a última linha com dados

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    

    ' Configurar o objeto HTTP

    Set http = CreateObject("MSXML2.XMLHTTP")

    

    ' Iterar sobre cada linha na planilha

    For i = 2 To lastRow ' Assumindo que a primeira linha é o cabeçalho

        endereco = ws.Cells(i, 1).Value ' Supondo que o endereço está na coluna A

        chaveunica = ws.Cells(i, 2).Value ' Supondo que a chave única está na coluna B

        

        ' Construir URL da API

        url = "http://maps.googleapis.com/maps/api/geocode/xml?address=" & Replace(endereco, " ", "%20") & "&sensor=false"

        

        ' Fazer a chamada HTTP

        http.Open "GET", url, False

        http.send

        

        ' Obter resposta da API

        resposta = http.responseText

        

        ' Criar o objeto XML

        Set xml = CreateObject("MSXML2.DOMDocument")

        xml.LoadXML resposta

        

        ' Extrair latitude e longitude

        On Error Resume Next

        lat = xml.SelectSingleNode("//location/lat").Text

        lng = xml.SelectSingleNode("//location/lng").Text

        On Error GoTo 0

        

        ' Atualizar planilha com latitude e longitude

        ws.Cells(i, 3).Value = lat ' Supondo que a latitude será armazenada na coluna C

        ws.Cells(i, 4).Value = lng ' Supondo que a longitude será armazenada na coluna D

        

        ' Esperar para evitar problemas com limites de API

        Application.Wait Now + TimeValue("00:00:01")

    Next i

    

    ' Limpar objetos

    Set http = Nothing

    Set xml = Nothing

    

    MsgBox "Processo concluído!", vbInformation

End Sub

 

Detalhes do Código

Referências: Certifique-se de que você tem as referências necessárias habilitadas. No VBA, você pode usar MSXML2.XMLHTTP para chamadas HTTP e MSXML2.DOMDocument para processar XML. Verifique se a biblioteca Microsoft XML, v6.0 está ativada em Ferramentas -> Referências.

URL da API: A URL da API do Google é construída dinamicamente substituindo espaços no endereço com %20.

Respostas da API: O XML é processado para extrair latitude e longitude. Use SelectSingleNode para encontrar elementos no XML retornado pela API.

Delay: Adiciona um atraso de 1 segundo entre as chamadas para evitar atingir limites de taxa da API.

Atualização de Planilha: Os resultados são escritos diretamente na planilha. Ajuste as referências de coluna conforme necessário.

Observações Finais

Autenticação da API: Para usar a API do Google, você precisará de uma chave de API. Adicione-a ao URL da API se necessário.

Limites da API: A API do Google pode ter limites de uso. Certifique-se de não exceder esses limites para evitar erros.

 Clique aqui e nos contate via What's App para avaliarmos seus projetos 

Envie seus comentários e sugestões e compartilhe este artigo!

brazilsalesforceeffectiveness@gmail.com

 Série de Livros nut Project 

  

 Série DONUT PROJECT 2024 

  

  

  

  

QlikView | Qlik Sense: Script de Carregamento usando QlikView Scripting Language faz chamadas para a API do Google Geocoding e Processa a Resposta XML

Aplicabilidades

Geocodificação de Endereços:

Localização de Dados: Pode ser usado para converter endereços em coordenadas geográficas (latitude e longitude), o que é útil para aplicações de mapeamento e análise espacial.

Visualização: Ideal para criar mapas de localização em ferramentas como Google Maps ou GIS (Sistema de Informação Geográfica) usando coordenadas.

VBA Excel | Como obter coordenadas geográficas (latitude e longitude) usando a API do Google Geocoding

 

QlikView | Qlik Sense: Script de Carregamento usando QlikView Scripting Language faz chamadas para a API do Google Geocoding e Processa a Resposta XML

 Veja também: 

 

 

Análise de Dados:

Análise Geoespacial: Pode ser usado para análise de dados baseada em localização, como identificar padrões geográficos ou realizar análises de proximidade.

Segmentação de Mercado: Empresas podem usar coordenadas para segmentar e analisar mercados com base em localização geográfica.

Integração com Outras Ferramentas:

Relatórios e Dashboards: Integrar dados de localização em relatórios e dashboards no Excel para visualização e análise.

Automatização: Automatizar a coleta de dados geoespaciais para processos de negócios que requerem localização, como planejamento de rotas ou logística.

Verificação e Validação de Endereços:

Correção de Dados: Verificar a precisão dos endereços em uma base de dados e corrigir ou normalizar os dados de endereço.

Aqui estão algumas características do código:

Sintaxe de Carregamento de Dados: O uso de LOAD, RESIDENT, e LEFT JOIN são características específicas da linguagem de script de QlikView.

Variáveis e Loops: O uso de variáveis com let e loops com for são comuns em scripts QlikView para processamento iterativo de dados.

Manipulação de Dados: O código inclui operações como DROP TABLE, NoConcatenate, e SLEEP, que são típicas de scripts QlikView para manipulação de dados e controle do fluxo de execução.

Chamadas de API e Processamento de XML: O código faz chamadas para a API do Google Geocoding e processa a resposta XML, o que é uma prática comum em scripts QlikView para integrar dados externos.

Formatação de Resultados: O uso de IF e ISNULL para tratamento condicional e filtragem é uma prática comum em scripts QlikView.

Instruções de Script de Carregamento: A estrutura geral de carregamento e transformação de dados é consistente com o estilo do QlikView, incluindo a criação de tabelas intermediárias e a junção de dados.

Correção e Melhorias no Código

Aqui estão algumas correções e melhorias no script, com pontos a considerar:

Formatação e Consistência: Verifique se todos os nomes das tabelas e colunas estão corretos e consistentes. Assegure-se de que todos os nomes usados são os mesmos que estão no seu ambiente QlikView.

Tratamento de Erros e Logs: Adicione instruções para registrar erros ou inconsistências que possam ocorrer durante a execução do script.

Eficiência e Desempenho: Considere otimizar o script para melhorar a eficiência, como reduzir o número de chamadas à API ou melhorar a lógica de junção.

Segurança e Controle: Certifique-se de que o script está seguro, especialmente ao fazer chamadas a APIs externas, e que está gerenciando dados sensíveis de maneira apropriada.

Aqui está uma versão limpa do código:

//BUSCA DE COORDENADAS ATRAVÉS DA API DO GOOGLE

SET ErrorMode = 0;

[Tabela_endereços]:

LOAD DISTINCT

    ChavePrincipal,

    RowNo() AS chaveunica,

    rua & '&' & numero & '&' & cidade & '&' & pais AS EndereçoTratado

FROM ....; // Especificar a origem dos dados

// Faz um tratamento caso seja preciso

[Maior Linha]:

LOAD MAX(chaveunica) AS maxLinhas

RESIDENT [Tabela_endereços];

LET vMaxLinhas = Peek('maxLinhas');

FOR i = 1 TO $(vMaxLinhas)

    // Ou FOR i = 1 TO 1249, assim por diante.

    LOAD

        EndereçoTratado AS logradouro

    RESIDENT [Tabela_endereços]

    WHERE chaveunica = $(i);

    LET vRegistro = Peek('logradouro');

    GeocodeResponse:

    LOAD $(i) AS chaveunica,

         [result/geometry/location/lat] AS Latitude,

         [result/geometry/location/lng] AS Longitude,

         %Key_GeocodeResponse_62C7A0A69859D655,

         [result/geometry/location_type] AS location_type,

         status

    FROM [http://maps.googleapis.com/maps/api/geocode/xml?address=$(vRegistro)&sensor=false] (XmlSimple, Table is [GeocodeResponse]);

    address_component:

    LOAD $(i) AS chaveunica,

         long_name AS Itens,

         %Key_address_component_3D78D9B402C68EA5 AS codigo,

         %Key_GeocodeResponse_62C7A0A69859D655 AS key

    FROM [http://maps.googleapis.com/maps/api/geocode/xml?address=$(vRegistro)&sensor=false] (XmlSimple, Table is [GeocodeResponse/result/address_component]);

    DROP TABLE GeocodeResponse;

    SLEEP(700);

NEXT

LEFT JOIN (Tabela_endereços)

    LOAD

        chaveunica,

        status

    RESIDENT GeocodeResponse;

LEFT JOIN

    LOAD

        chaveunica,

        location_type

    RESIDENT GeocodeResponse;

LEFT JOIN

    LOAD

        chaveunica,

        Latitude

    RESIDENT GeocodeResponse;

LEFT JOIN

    LOAD

        chaveunica,

        Longitude

    RESIDENT GeocodeResponse;

LEFT JOIN

    LOAD

        chaveunica,

        IF(codigo = 9, Itens) AS Rua

    RESIDENT address_component

    WHERE ISNULL(IF(codigo = 9, Itens)) = FALSE();

LEFT JOIN

    LOAD

        chaveunica,

        IF(codigo = 13, Itens) AS Bairro

    RESIDENT address_component

    WHERE ISNULL(IF(codigo = 13, Itens)) = FALSE();

LEFT JOIN

    LOAD

        chaveunica,

        IF(codigo = 5, Itens) AS Número

    RESIDENT address_component

    WHERE ISNULL(IF(codigo = 5, Itens)) = FALSE();

LEFT JOIN

    LOAD

        chaveunica,

        IF(codigo = 18, Itens) AS Cidade

    RESIDENT address_component

    WHERE ISNULL(IF(codigo = 18, Itens)) = FALSE();

LEFT JOIN

    LOAD

        chaveunica,

        IF(codigo = 23, Itens) AS Estado

    RESIDENT address_component

    WHERE ISNULL(IF(codigo = 23, Itens)) = FALSE();

LEFT JOIN

    LOAD

        chaveunica,

        IF(codigo = 33, Itens) AS cep

    RESIDENT address_component

    WHERE ISNULL(IF(codigo = 33, Itens)) = FALSE();

[Final]:

NOCONCATENATE LOAD

    ChavePrincipal,

    cep,

    chaveunica,

    EndereçoTratado,

    status,

    location_type,

    Latitude,

    Longitude,

    Rua,

    Bairro,

    Número,

    Cidade,

    Estado

RESIDENT Tabela_endereços

WHERE ISNULL(Latitude) = FALSE() AND Latitude <> '';

DROP TABLE GeocodeResponse;

DROP TABLE [Maior Linha];

DROP TABLE address_component;

DROP TABLE Tabela_endereços;

STORE Final INTO ; // Especificar o destino do armazenamento

Observações Finais

API do Google: Certifique-se de que a API do Google está acessível e que as credenciais necessárias estão configuradas corretamente.

Testes e Validação: Sempre teste o script com um conjunto menor de dados antes de executá-lo em um ambiente de produção.

 Clique aqui e nos contate via What's App para avaliarmos seus projetos 

Envie seus comentários e sugestões e compartilhe este artigo!

brazilsalesforceeffectiveness@gmail.com

 Série de Livros nut Project 

  

 Série DONUT PROJECT 2024