topic Re: Google GeoCoding not working anymore in QlikView

Google GeoCoding not working anymore

Mon, 13 May 2013 08:55:17 GMT

In order to get the logitude and latitude data form Google I have used the below mentioned scripting.

But this is not working anymore.

Does anyone has an idea?

Table1:

LOAD

Address,

Patient,

Population,

City

FROM testgoogle.xlsx

(ooxml, embedded labels, table is Blad1);

let noRows = NoOfRows('Table1');

for i=0 to $(noRows)

let c=peek('City',$(i),'Table1');

let p=peek('Population',$(i),'Table1');

let a=peek('Address',$(i),'Table1');

Data:

LOAD

'$(c)' as City,

'$(p)' as Population,

'$(a)' as Address,

subfield([Response/Placemark/Point/coordinates], ',' ,1) AS Longitude,

subfield([Response/Placemark/Point/coordinates], ',' ,2) AS Latitude

FROM [http://maps.google.com/maps/geo?q=$(a)&output=xml&oe=utf8&sensor=false&key=XXYYZZ] (XmlSimple, Table is [kml]);

Re: Google GeoCoding not working anymore

laujerry — Wed, 19 Jun 2013 02:22:31 GMT

i have the same problem too

but did u replace the key [XXYYZZ] with your own?

Re: Google GeoCoding not working anymore

laujerry — Thu, 20 Jun 2013 03:18:36 GMT

ok..the google api has changed:

https://developers.google.com/maps/documentation/geocoding/#XML

Re: Google GeoCoding not working anymore

Thu, 20 Jun 2013 06:55:07 GMT

what have you changed? it it still not working for me

Re: Google GeoCoding not working anymore

laujerry — Thu, 20 Jun 2013 07:07:29 GMT

From: http://community.qlik.com/docs/DOC-3136#/?page=2

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

[result/geometry/location/lng] as longitude

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

if u access the link through a broswer, like
http://maps.googleapis.com/maps/api/geocode/xml?address=new+york&sensor=false

u can see how the response values are retrieved

u can fetch the value "status" for your reference too

https://developers.google.com/maps/documentation/geocoding/#StatusCodes

Re: Google GeoCoding not working anymore

laujerry — Thu, 20 Jun 2013 07:12:48 GMT

but i also find that this method does not support characters other than english

if my address is in chinese, the status shows "invalid request" but i can get the OK status when accessing the same link with a broswer

now i have to encode my address source data before passing to qlikview to solve the problem