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.
|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|