Zip Codes to Coordinates: Using Google's Geocoding API and PostgreSQL's Earth Distance Extension

In this post, we're taking a look at how you can deal with geographic coordinates in your Flask-SQLAlchemy application. Specifically, we're going to convert zip codes into latitude and longitude using Google's Geocoding API, and then run some queries using PostgreSQL's Earth Distance extension. This could be pretty useful if you're developing an application that needs to work with location data (Like I currently am...).

Prerequisites

Before we dig in, here's what you need to have prepared:

1. Google Cloud account and Geocoding API access: You'll need an API key from the Google Cloud Console to access the Geocoding API. I should warn you this does cost 0.005 USD per each request.

2. PostgreSQL with Earth Distance extension: Make sure you've got a PostgreSQL database up and running, with the Earth Distance extension enabled. You can enable it by running CREATE EXTENSION earthdistance CASCADE; in your SQL terminal. You can do this in your terminal by using the PSQL Command key that your PAAS gave you. If You're using Render.com like me. It will be at the bottom of the info section in your database instance.

Converting Zip Codes to Latitude/Longitude

Let's start by converting zip codes into geographical coordinates. Google's Geocoding API is really straightforward for this. We'll create a Python function using Flask to handle this:

import requests

def get_coordinates(zip_code, api_key):
    url = f'https://maps.googleapis.com/maps/api/geocode/json?address={zip_code}&key={api_key}'
    response = requests.get(url)
    data = response.json()

    coordinates = data['results'][0]['geometry']['location']
    return [coordinates['lat'], coordinates['lng']]

This function takes a zip code and your API key as input, makes a request to the Geocoding API, and if everything goes as planned, returns the latitude and longitude for that zip code.

Doing Query Search with PostgreSQLs Earth Distance Extension

Now that we've got the latitude and longitude of this users zip code, we can now use PostgreSQL's Earth Distance extension to run query based off geographtical distance.

Assuming your sqlalchemy models are all set up correctly, we can now use the Earth Distance extension to run queries based on the geographical distance. For example, we can find all locations within a 10 kilometer radius of a certain point (The extention only works in Metric units)

def get_users_in_radius(user_id):
    user = User.query.get(user_id)
    radius = 1000
    if user:

        # Find users within the specified radius
        nearby_users = User.query.filter(db.func.earth_distance(
            db.func.ll_to_earth(user.latitude, user.longitude),
            db.func.ll_to_earth(User.latitude, User.longitude)
        ) <= radius).all()

        return nearby_user
    else:
        return None

The ll_to_earth() function is part of the Earth Distance extention that we installed on our PostgreSQL. We use it to turn latitude and longitude values into a specific point on the earth that we can then use earth_distance to figure out how far away these points are.