Qlik Community

QlikView Extensions

Discussion Board for collaboration on QlikView Extensions.

Not applicable

I need a better way to search lat and long without use a 'For'-SQL DB

English version:

To search for latitude and longitude (coordinates) for create a google map at qlikview, I'm using a 'For' that goes line by line from my table to use the "GeocodeResponse'.

But my table is giant, because I use a sql database. You can see how i did it right below.

Question: Is there a better way to use this method without go record by record from the table?

---------------------------------

Portuguese version:

Para criar minha tabela de clientes com latitude e longitude usando busca do google, Utilizo de um For para percorrer registro por registro da minha tabela e buscar as coordenadas usando GeocodeResponse, conforme o exemplo abaixo. Como uso banco de dados sql server, a minha base de dados é enorme, não posso ficar gerando linha por linha.

Pergunta: Alguém pode me indicar uma maneira mais prática e rápida de buscar estas informações, sem ser por esta forma?

---------------------------------

Exemplo:

// Frete is my table from sql server database.

Let noRows = NoOfRows('Frete')-1;

For i=0 To $(noRows)

  Let a = peek('CliDev',$(i),'Frete');

    Let b = peek('Devedor',$(i),'Frete');

    Let c = peek('CidadeDev',$(i),'Frete');

    Let d = peek('UFDev',$(i),'Frete');

   

  GeocodeResponse:

  LOAD

      '$(a)' as CliDeveM,

      '$(b)' as DevedorMa,

      '$(c)' as CidDevM,

      '$(d)' as UFDevM,

      ([result/geometry/location/lat]) as Latitude,

      ([result/geometry/location/lng]) as Longitude

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

next i;

2 Replies
Highlighted
akl
Honored Contributor

Re: I need a better way to search lat and long without use a 'For'-SQL DB

I would recommend that you store the already geocoded address into a qvd and only geocode new addresses on reload.

Sadly Google don't allow you to geocode in batch.

Not applicable

Re: I need a better way to search lat and long without use a 'For'-SQL DB

Thank you Alexander,

That was a good idea. I got all the coordinates from google maps and I saved in one qvd, just like you said.

For all my others reports, I'll use that one.