Fetching Latitude & longitude from adress

[#1]

Hi All

I am trying to fetch Geo coordinates to my customer’s addresses, but can’t get it to work correctly

I have been searching community for answers on how to do it, and from what I have found I have put this script together, but I don’t get coordinates for all customers.

I only get one set of coordinates, that are placed on all customers.

Hope someone can help

Script to get coordinates:

let noRows = NoOfRows('Customers')-1;

for i=0 to $(noRows)
 
LET C = peek('vAddress', '$(i)', 'Customers');

if '$(C)' <> ', ' then
 
Data:

LOAD

'$(C)' as Address1,

[result/geometry/location/lat] as latitude,

[result/geometry/location/lng] as longitude,    

[result/place_id]

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

end if

NEXT;

Regards

Niels Juel

[settu_periasamy · Accepted solution]

Hi,

Have you checked your Address values is assigned to your Variable 'C'?

And i see that, you didn't give your variable in the website address..

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

And i assume, you want to exclude the address, if there is comma with space

if '$(C)' <> ', ' then     Try this   

if not WildMatch('$(b)','*, *') then

Complete script

let noRows = NoOfRows('Customers')-1;
for i=0 to $(noRows) 
LET C = peek('vAddress', '$(i)', 'Customers');
if not WildMatch('$(b)','*, *') then
Data:
LOAD
    '$(C)' as Address1,
    [result/geometry/location/lat] as latitude,
    [result/geometry/location/lng] as longitude,
    [result/place_id]
FROM [http://maps.googleapis.com/maps/api/geocode/xml?address=$(C) &sensor=false] (XmlSimple, Table is [GeocodeResponse]);
end if
NEXT i;

Check it here too, Steps for beginners to implement Google Maps in Qlikview

[#3]

Hi Settu

Thanks for your reply.

I have tried your code, but still only just got coordinates for one of my test customers.

But I found out what the issue was, just not yet how to fix it

Issue was and is now, that in Denmark we use some special letters such as Å,Ø,Æ, and when an address has one of theese letters the coordinates are not found.

So I have to find out how to get the service to use the Danish caracter set, any ideas ?

thx

NJ

[petter]

You will have to URI / URL encode your parameter... then it will work

For instance using w3schools website gives this for the ÅØÆ and åøæ:

upper+case+%C3%85%C3%98%C3%86+lower+case+%C3%A5%C3%B8%C3%A6

The pluses are the correct replacement for spaces actually...

%C3%85 is Å since it is two bytes in UTF-8 which is hex C3 and hex 85

The best is to use a function to do the translation as it will also cover accents and similar things like ü and ö...

[settu_periasamy]

If it is limited characters, may be try to use replace() function to replace the Danish Character to English Character..

[#6]

Hi Petter

Thanks for your reply.

I think you are right, and it would also be nice to cover accents and similar things.

But unfortunately I don't know how to do this

Regards

NJ

[petter]

This link should be helpful:

Howto: URL encoding in QlikView and Qlik Sense

Basically you can do it two ways:

A - Creating a simple JScript function that can be called directly from the Load Script

B - Making a MAPPING LOAD table

   Then use MapSubString() function.