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:

Addresses:
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');

  Data:
  LOAD
  '$(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]);
next

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!

‹ Auto reload every 10 minutes – desktop client

QlikView Macros – Useful collection ›

Tagged with: Geolocation, Google maps, Spell-check
Posted in Google Maps