Tutorial: Build Metabase Maps with Geocodio

Have you reached for Metabase's map visualizations, only to find that your data model is incomplete? This tutorial will show you how to pull in all the geographical data you need from Geocodio to fully utilize Metabase maps, complete with production ready Laravel code.

Geocodio is a geocoder that supports the US and Canada, and is especially good for times when you need additional data, like Census data, timezones, or political districts.

#Geocode an address with a queued event listener

We'll be using an example Eloquent model of a Business for this tutorial. The schema is included below.

Your codebase is undoubtedly different, but make sure you have:

• A source address you can geocode. Geocodio can return a lot of information from a single user-entered string.
• Two decimal columns for latitude and longitude, so you can use pin maps. At the time of publishing, Metabase does not support GIS columns.
• Columns to store the formatted address components retrieved from Geocodio, which will help with future analysis in Metabase.
• Columns for additional data you may find insightful from Geocodio. We'll be retrieving and storing Census household income data as an example.
• A Geocodio API key, which you can create here. The first 2,500 lookups per day are free.

Schema::create('businesses', function (Blueprint $table) {
    $table->id();
    $table->timestamps();
 
    // Columns entered by users
    $table->string('name');
    $table->string('user_supplied_address')->nullable();
 
    // Columns for data retrieved from Geocodio
    // Unlike most geo services, Geocodio allows you to store info retrieved from the API (https://www.geocod.io/features/api/)
 
    // Metabase requires coordinates to be split in two columns, rather than using GIS columns like POINT
    $table->decimal('latitude', 10, 8)->nullable();
    $table->decimal('longitude', 11, 8)->nullable();
 
    // A single formatted string, useful for searching within future analysis
    $table->string('formatted_address')->nullable();
 
    // Distinct columns for address components. Useful for filters, such as per state, in Metabase.
    $table->string('street')->nullable();
    $table->string('city')->nullable();
    $table->string('county')->nullable();
    $table->string('state')->index()->nullable();
    $table->string('zip')->nullable();
    $table->string('country')->index()->nullable();
 
    // Additional Census data you will be retrieving from Geocodio
    $table->integer('acs_number_of_households')->index()->nullable();
    $table->integer('acs_median_household_income')->index()->nullable();
});

We want to retrieve data from Geocodio every time a new Business is created. This means hooking into Eloquent events.

/**
* The event map for the model.
*
* @var array
*/
protected $dispatchesEvents = [
    'created' => BusinessCreated::class,
];

Next up, you need to create the event class referenced above. You can use artisan to generate a template like so:

php artisan make:event BusinessCreated

We aren't doing anything fancy here. The event class is the glue that helps us pass data from the model event to our queued event listener. We'll write that next.

<?php
 
namespace App\Events;
 
use App\Models\Business;
use Illuminate\Foundation\Events\Dispatchable;
use Illuminate\Queue\SerializesModels;
 
class BusinessCreated
{
    use Dispatchable, SerializesModels;
 
    /**
    * The business instance that was created.
    */
    public $business;
 
    /**
    * Create a new event instance.
    *
    * @param Business $business
    * @return void
    */
    public function __construct(Business $business)
    {
       $this->business = $business;
    }
}

Before you write the listener code, you need to install Geocodio. Run the following commands to get Geocodio installed in your Laravel codebase:

composer require geocodio/geocodio-library-php`
 
php artisan vendor:publish --provider="Geocodio\GeocodioServiceProvider"

At this point, the Geocodio PHP Library should be installed and you have a new file—config/geocodio.php—in your app. Make sure to set the env variable GEOCODIO_API_KEY to your Geocodio API key before continuing.

Finally, let's generate a listener:

php artisan make:listener GeocodeBusiness

<?php
 
namespace App\Listeners;
 
use App\Events\BusinessCreated;
use Geocodio\Geocodio;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Queue\InteractsWithQueue;
 
class GeocodeBusiness implements ShouldQueue
{
    use InteractsWithQueue;
 
    /**
     * Use dependency injection to instantiate a fully configured Geocodio class
     */
    private $geocodio;
 
    public function __construct(Geocodio $geocodio)
    {
        $this->geocodio = $geocodio;
    }
 
    // $afterCommit is available in Laravel 8.x
    // See https://github.com/laravel/ideas/issues/1441 for alternative ideas and context.
    public $afterCommit = true;
 
    /**
     * Handle the event.
     *
     * @param  BusinessCreated  $event
     * @return void
     */
    public function handle(BusinessCreated $event)
    {
        $business = $event->business;
 
        // Hit the Geocodio API, request additional census data, and limit the results to one.
        // https://www.geocod.io/docs/#geocoding
        $response = $this->geocodio->geocode($business->user_supplied_address, ['acs-economics'], 1);
        $results = $response->results[0];
 
        // Pull out high level street format and coordinates
        $business->formatted_address = $results->formatted_address;
        $business->latitude = $results->location->lat;
        $business->longitude = $results->location->lng;
 
        // The address components, which we'll use for filtering in Metabase
        $addressComponents = $results->address_components;
        $business->street = $addressComponents->number . " " . $addressComponents->formatted_street;
        $business->city = $addressComponents->city;
        $business->county = $addressComponents->county;
        $business->state = $addressComponents->state;
        $business->zip = $addressComponents->zip;
        $business->country = $addressComponents->country;
 
        // Additional census data
        $ecomData = $results->fields->acs->economics;
        $business->acs_number_of_households = $ecomData->{'Number of households'}->Total->value;
        $business->acs_median_household_income = $ecomData->{'Median household income'}->Total->value;
 
        // Make sure we explicitly persist the changes, since we are in an afterCommit callback
        $business->save();
    }
}

The use of $afterCommit ensures that our listener is not enqueued until after all open database transactions finish, so that the model exists in the database by the time our queue workers pick it up. For the rabbit hole-inclined, you can read more about $afterCommit here and here.

For simplicity, we will be hooking up the application database directly to Metabase for analysis examples. However, if you have an established ETL pipeline that is decoupled from your application database, the listener is still a great spot to call Geocodio, parse the data, and send it off to your warehouse.

The last step is to update the EventServiceProvider so that the listener picks up any BusinessCreated events. Once that's done, you have all the data you need to use Metabase maps!

/**
 * The event listener mappings for the application.
 *
 * @var array>
 */
protected $listen = [
    BusinessCreated::class => [
        GeocodeBusiness::class,
    ]
];

#Check whether Metabase has the correct column types for your data model

After connecting the database to Metabase and re-syncing the schema, if needed, double check that the data model has correctly identified the latitude and longitude.

#Building a pin map in Metabase

Now that we have latitude and longitude, we can create a pin map—the most precise geographical visualization in Metabase—to pull out insights related to the businesses in our database.

On pin maps, there is a handy 'Draw box to filter' button. Press it, draw a box around some pins, and the map will zoom in to reveal a street level map. 

#Using Census data as a filter

We used Geocodio to request additional Census data for each business—number of households and median household income—that we can now use as a filter within Metabase.

#Bonus: Reverse geocoding with Geocodio

Our example so far has only used forward geocoding to turn addresses into coordinates, but what if you have coordinates (i.e. a customer is checking-in to a physical location) and you want to turn that into an address?

Lucky for us, Geocodio also has a reverse geocoding API. If you need to use it, follow the same architecture as above to fire an Eloquent event in your model, which gets picked up by a queued event listener.

As far as the listener code goes, it's extremely similar to the forward geocoding example. In this example, you are storing the latitude and longitude as separate columns in the CheckIn table, hence the string concatenation as the first parameter to the reverse API.

<?php
 
namespace App\Listeners;
 
use App\Events\CheckInCreated;
use Geocodio\Geocodio;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Queue\InteractsWithQueue;
 
class ReverseGeocodeCheckIn implements ShouldQueue
{
    use InteractsWithQueue;
 
    /*
     * Use dependency injection to instantiate a fully configured Geocodio class
     */
    private $geocodio;
 
    public function __construct(Geocodio $geocodio)
    {
        $this->geocodio = $geocodio;
    }
 
    // $afterCommit is available in Laravel 8.x
    // See https://github.com/laravel/ideas/issues/1441 for alternative ideas and context.
    public $afterCommit = true;
 
    /**
     * Handle the event.
     *
     * @param  CheckInCreated  $event
     * @return void
     */
    public function handle(CheckInCreated $event)
    {
        $checkIn = $event->checkIn;
 
        // Hit the Geocodio Reverse Geocode API, request additional census data, and limit the results to one.
        $response = $this->geocodio->reverse($checkIn->latitude. "," . $checkIn->longitude, ['acs-economics'], 1);
        $results = $response->results[0];
 
        // Look familiar? The Geocodio reverse geocode response is the same format as the forward geocode API
        // Pull out high level street format and coordinates
        $checkIn->formatted_address = $results->formatted_address;
        $checkIn->latitude = $results->location->lat;
        $checkIn->longitude = $results->location->lng;
 
        // The address components, which we'll use for filtering in Metabase
        $addressComponents = $results->address_components;
        $checkIn->street = $addressComponents->number . " " . $addressComponents->formatted_street;
        $checkIn->city = $addressComponents->city;
        $checkIn->county = $addressComponents->county;
        $checkIn->state = $addressComponents->state;
        $checkIn->zip = $addressComponents->zip;
        $checkIn->country = $addressComponents->country;
 
        // Additional census data
        $ecomData = $results->fields->acs->economics;
        $checkIn->acs_number_of_households = $ecomData->{'Number of households'}->Total->value;
        $checkIn->acs_median_household_income = $ecomData->{'Median household income'}->Total->value;
 
        // Make sure you explicitly persist the changes, since you are in an afterCommit callback
        $checkIn->save();
    }
}

#Go forth and map!

Thanks for reading! I hope these geocoding examples provide a clear path to normalized geographical data for you to use in Metabase. To get started, create a free Geocodio account and get your API key.