QlikView & Google Maps – How to automatically spell-check customer addresses and get location information

Using Google Geocoding Service we can convert addresses into geographic coordinates. Before converting, the service is performing a spell-check on the address and return the result.

Let’s say we have a list of customers:

LOAD *, RowNo() as ID INLINE [
 Name, Address
 "QlikTech Headquarter", "150 N. Radnor Chetser Road, Radnor, USA"
 "QlikTech New York", "292 Madison Ave, New York, USA"
 "QlikTech Boston", "275 Grove Street, Newton, USA"

Please notice how we misspelled “Chester Road” into “Chetser Road” to test the service. Now, we will loop through all rows and pass each address to Google Geocoding Service.

let noRows = NoOfRows('Addresses')-1;
for i=0 to $(noRows)
  let d=peek('ID',$(i),'Addresses');
  let address=peek('Address',$(i),'Addresses');

  '$(d)' as ID,
  [result/formatted_address] as FormattedAddress,
  [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]);

After running the script we see that the service returned the correct address, new information like the zip code and the latitude and longitude of each address.

Name FormattedAddress latitude longitude
QlikTech Headquarter 150 North Radnor Chester Road, Strafford, PA 19087, USA 40.0431640 -75.3660710
QlikTech Boston 275 Grove Street, Newton, MA 02466, USA 42.3386468 -71.2525211
QlikTech New York 292 Madison Avenue, New York, NY 10017, USA 40.7518791 -73.9797619

Happy Qliking!

Tagged with: , ,
Posted in Google Maps
8 comments on “QlikView & Google Maps – How to automatically spell-check customer addresses and get location information
  1. Kevin Pinto says:

    Does this require a Google API key?

  2. Daniel Robu says:

    cu adresele din Romania nu returneaza corect, de ce oare ?
    Sub adresele din script am adaugat
    “Strada Tabacului nr.1E, Iași, Județul Iași” si nu a mai returnat nici in harta nici macar pozitionarea adreselor de la qlik. De ce oare ?

  3. enzo teles says:

    good day , wanted to tell you adimiro their work

    so one more doubt , if I want to put a marker on the map with my localization using gps I consiguo ?

  4. jim says:

    I have tried as well, my question is. how many address can i upload per reload? i have tried adding 13 address per 1 reload, but only 10 with converted address and long and lat coordinates..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: