Hi, I'm Ted Mosby. This is a story about how i met your mother Reverse Geocoding. Stay tune!

Introduction

I was sitting in my room, staring at my laptop screen, while analyzing tactical crime data. Hey, I wasn't pretending to be a clever detective in CSI series! I just spent my spare time. FYI, I downloaded the dataset here. The dataset contained information about crime events in London and consisted of 5 tables:

1. Crimes
2. Crime Person
3. People
4. Crime Roles
5. Crime Types

Ok, let's focus on Crimes table, shall we?

In this table, I had 2500 crime events and I only captured 21 crime events as you see in the picture above. My first impression of this data was "how do I know that all the crime events are located in London?". Some of you would say to me "Hey, you have Latitude and Longitude data!". All right, but it was only Latitude and Longitude after all. If I want to know where exactly the crime events are, I need to visualize them using Map. Nevertheless, I'd like to know where the exact location of each crime event.

After asking to my best friend, Google, I found the solution using Power Query in Power BI. Let's jump into the steps.

Reverse Geocoding

Reverse Geocoding is the process of converting geographic coordinates (latitude and longitude) into a human-readable address or location information. In other words, it takes a set of coordinates and returns information about the location represented by those coordinates, including street address, city, state, country, and other relevant information.

To retrieve the information, I used Nominatim API. Nominatim uses OpenStreetMap data and It's open-source API. Here are the steps using Nominatim API in Power Query:

1. Open your Power Query and choose Home -> New Source -> Blank Query
2. Write the code and rename the query "ReverseGeocoding".
3. Choose the table that contains the geographic data, in this case Crimes table.
4. Go to add column tab and choose Invoke Custom Function.
5. After the Pop-Up menu is appeared, choose your function query, which is our "ReverseGeocoding". Choose Latitude parameter with Latitude column and Longitude parameter with Longitude column. Then, click OK.

Result

After following the steps, I could get more information about those locations.

However, there is a weakness using Nominatim. As you can see in the picture above, in the road column, 1% of the entries are empty. So, there is a lot of homework to do.

Conclusion

Reverse Geocoding is very useful to decipher your geographic data into human-readable location. However, there is a weakness of Reverse Geocoding. You need to check whether there are null values and try to handle those values.

That's my story how I met Reverse Geocoding. Let me know if you have better way.

Sincerely yours,

Ted Mosby