Geocoding addresses in Talend Open Studio for Data Integration
Last week I had to geocode the addresses in a dataset using Talend. Geocoding is adding coordinates to addresses. This way you can easily visualize your data on a map.
I experienced problems when I tried to download some plugins from Talend Exchange but I found this blog post about geocoding without the use of a plugin. The blogpost wasn’t very clear so I decided to write a step-by-step explanation which you can find below. In this guide, we will be using the Google Geocoding API.
Map your location dataset (with a tMap) to 2 columns: an unique ID of your location and a concatenated string containing the location address. I used one of Talend’s built-in StringHandling-functions.
StringHandling.CHANGE(LOCATIONS.STREET_ADDRESS + LOCATIONS.POSTAL_CODE + LOCATIONS.CITY," ","+")
My first column was called LOCATION_ID and my second column was called LOCATION_CONCAT_ADDRESS. Make sure to remember the name of your output. I named mine LocConcatOut.
Next, connect your output from the tMap to a tFlowToIterate component to start processing all records one by one.
The output from the tFlowToIterate has to be connected to a new tFileInputJson component. You can change the number of parallel iterations to speed up the geocoding. In the tFileInputJson component, check the Use Url option and enter the following URL:
https://maps.googleapis.com/maps/api/geocode/json?address= + ((String)globalMap.get("LocConcatOut.LOCATION_CONCAT_ADDRESS"))
+ "&key=YourAPIkey"if you would like to use your own API key for the Google Geocoding API.
Edit the schema for this component and make sure it contains 2 columns: LATITUDE and LONGITUDE. Their type should be float, nullable.
In the mapping, LATITUDE should be set to
"$.results.geometry.location.lat"and LONGITUDE should be set to
Now we’re going to map the coordinates back to the UIDs. Add another tMap component at the end. Create a new output, map the 2 coordinates from the tFileInputJson to identical columns in the new tMap and create a new column with a primary key. Enter the following for the value of this key (my keys are integers):
Finally connect the output from the tMap component to a tBufferOutput. That ends the loop started with the tFlowToIterate component.
The previously mentioned components should all be put in the same subjob. Use a trigger like OnSubjobOk to start your next job after this one is completed. You can find all the location identifiers with their coordinates in the tBufferInput component. This is easily mapped (joined) to the original locations dataset using a tMap. Make sure to set the correct data types in the mapping (1 integer and 2 floats) for the tBufferInput component.
I hope my guide is clear, but you can always tweet me with any further questions.
About the author
Samuel Debruyn is a C# developer who builds mobile (cross platform) apps with Xamarin. Sam is a certified Xamarin mobile developer since 2016. He likes to experiment with all kinds of programming languages and software frameworks. More info