Tutorial: Build Metabase Maps with Geocodio

Published on by

Tutorial: Build Metabase Maps with Geocodio image

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.

Matt Sencenbaugh photo

Freelance software engineer and founder of WrestlingIQ.

Filed in:
Cube

Laravel Newsletter

Join 40k+ other developers and never miss out on new tips, tutorials, and more.

Laravel Forge logo

Laravel Forge

Easily create and manage your servers and deploy your Laravel applications in seconds.

Laravel Forge
Tinkerwell logo

Tinkerwell

The must-have code runner for Laravel developers. Tinker with AI, autocompletion and instant feedback on local and production environments.

Tinkerwell
No Compromises logo

No Compromises

Joel and Aaron, the two seasoned devs from the No Compromises podcast, are now available to hire for your Laravel project. ⬧ Flat rate of $7500/mo. ⬧ No lengthy sales process. ⬧ No contracts. ⬧ 100% money back guarantee.

No Compromises
Kirschbaum logo

Kirschbaum

Providing innovation and stability to ensure your web application succeeds.

Kirschbaum
Shift logo

Shift

Running an old Laravel version? Instant, automated Laravel upgrades and code modernization to keep your applications fresh.

Shift
Bacancy logo

Bacancy

Supercharge your project with a seasoned Laravel developer with 4-6 years of experience for just $2500/month. Get 160 hours of dedicated expertise & a risk-free 15-day trial. Schedule a call now!

Bacancy
Lucky Media logo

Lucky Media

Get Lucky Now - the ideal choice for Laravel Development, with over a decade of experience!

Lucky Media
Lunar: Laravel E-Commerce logo

Lunar: Laravel E-Commerce

E-Commerce for Laravel. An open-source package that brings the power of modern headless e-commerce functionality to Laravel.

Lunar: Laravel E-Commerce
LaraJobs logo

LaraJobs

The official Laravel job board

LaraJobs
SaaSykit: Laravel SaaS Starter Kit logo

SaaSykit: Laravel SaaS Starter Kit

SaaSykit is a Laravel SaaS Starter Kit that comes with all features required to run a modern SaaS. Payments, Beautiful Checkout, Admin Panel, User dashboard, Auth, Ready Components, Stats, Blog, Docs and more.

SaaSykit: Laravel SaaS Starter Kit
Rector logo

Rector

Your partner for seamless Laravel upgrades, cutting costs, and accelerating innovation for successful companies

Rector
MongoDB logo

MongoDB

Enhance your PHP applications with the powerful integration of MongoDB and Laravel, empowering developers to build applications with ease and efficiency. Support transactional, search, analytics and mobile use cases while using the familiar Eloquent APIs. Discover how MongoDB's flexible, modern database can transform your Laravel applications.

MongoDB

The latest

View all →
Introducing Built with Laravel image

Introducing Built with Laravel

Read article
Visual EXPLAIN for MySQL and Laravel image

Visual EXPLAIN for MySQL and Laravel

Read article
VS Code Snippets for Livewire and Alpine.js image

VS Code Snippets for Livewire and Alpine.js

Read article
A guide to Laravel's model events image

A guide to Laravel's model events

Read article
API Versioning in Laravel 11 image

API Versioning in Laravel 11

Read article
A Resize Plugin for Alpine.js image

A Resize Plugin for Alpine.js

Read article