Happy New Year, fellow Jedox enthusiasts! In today’s article, we’ll be delving into an interesting use case: extracting GPS coordinates in XML format from the OpenStreetMap Nominatim API (https://nominatim.openstreetmap.org/). This adventure will involve using a constant table as a source for countries and cities, interacting with the Nominatim API to retrieve latitude and longitude data, and finally looping and storing this valuable information in the Jedox database. This type of use case is often visible when our cube contains a Geography dimension and then we would like to enhance attribute collection by additional parameters like latitude and longitude. These are very useful in case you would like to create a widget map. Something similar can be found:
Let us follow the next 4 Steps.
Step 1: The Constant Table:
In Jedox, we’ll create a constant table containing a list of countries and cities. This table will serve as our foundation for the XML extraction process. Think of it as a map guiding us to the geographical riches we seek. This table can always be replaced with Excel File, CSV File or even extracted from some other tool.
Step 2: Connecting to Nominatim:
Now, let’s establish a connection to the Nominatim OpenStreetMap API which is free at the time of writing. This step involves crafting the XML request, sending it to the API, and patiently awaiting the coordinates in response. The XML format will be the language through which we communicate with OpenStreetMap, making our request clear and structured. The connection type is XMLFile and the Location Type is HTTP.
“https://nominatim.openstreetmap.org/search?q=${s_query}&format=xml&addressdetails=0”
Step 3: Unwrapping the XML:
Once the response arrives, we will be unwrapping the XML package. XML, with its tags and elements, contains the precise coordinates we’re after. We’ll navigate through the XML structure to extract the latitude and longitude hidden within. The extract type is XMLExtract.
Step 4: Writing to Jedox DB:
With our newfound coordinates in hand, it’s time to load them into the Jedox database. We’ll write this information to the database, creating a repository of geographical data that can be utilized for various analytical purposes. For this purpose, we will use the Groovy job to create a loop. Our {s_query} variable has a special format in the form of “Country + City” and Groovy will help us to concatenate it easily.
In the end, our dimension will look like this:
Conclusion
Today, we have acquired the knowledge of utilizing XML to extract GPS coordinates from the Nominatim OpenStreetMap API. The initial step involved the use of a constant table, which facilitated the process of connecting to Nominatim, parsing the XML response, and subsequently storing the extracted coordinates in the Jedox database.
The full ETL project can be downloaded from HERE.