Adventures in reverse geocoding

Had some longitude & latitudes although in the biz they use them long, lat (go figure)

The long, lat is in a redshift DB, so first order of business is getting the data out of redshift.

Luckily there is the handy unload command

Remember to escape your quotes, doubling the single ticks works
    'select * from venue where venuestate=''NV'''
) to 's3://yourbucket/test' 
credentials 'aws_iam_role=arn:aws:iam::yourrole' 
delimiter '|' 
parallel off;

Ok, now you have some data

Now we need to do some reverse geocoding.

One pattern to do this sort of transformation is AWS step functions that call Glue Python Shell Jobs.

Once you have the data in S3, write a python script to reverse geocode each entry. There are a number of online services that offer this. has a very generous 250K Freemium account!

LocationIQ has a free 10K a day has a very interesting Reverse Geocode Lite

Common to all of these services is being able to pass the API the lat, long and it brings back the country, city, state etc…

For example from

import requests

resp = requests.get(",0.0079.json")
json = resp.json()
print(json['major']['prov']) has quite extensive documentation with examples.

With a response

So Parse the response, pulling out what data you need, and then load it back into Redshift. The step function looks like this.


One comment

  1. Hi Michael,

    Would like to suggest BigDataClous’s reverse geocoding API as well. Unlike other APIs, BigDataCloud has always free version and freemium version (50K queries per month).

    One of the unique aspects of the API is that it is the first to deliver administrative/non-administrative boundary-based results.

    We have written in more detail the comparison between various reverse Geocoding APIs, which might be insightful.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s