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.

 1Schema::create('businesses', function (Blueprint $table) {
 2    $table->id();
 3    $table->timestamps();
 4 
 5    // Columns entered by users
 6    $table->string('name');
 7    $table->string('user_supplied_address')->nullable();
 8 
 9    // Columns for data retrieved from Geocodio
10    // Unlike most geo services, Geocodio allows you to store info retrieved from the API (https://www.geocod.io/features/api/)
11 
12    // Metabase requires coordinates to be split in two columns, rather than using GIS columns like POINT
13    $table->decimal('latitude', 10, 8)->nullable();
14    $table->decimal('longitude', 11, 8)->nullable();
15 
16    // A single formatted string, useful for searching within future analysis
17    $table->string('formatted_address')->nullable();
18 
19    // Distinct columns for address components. Useful for filters, such as per state, in Metabase.
20    $table->string('street')->nullable();
21    $table->string('city')->nullable();
22    $table->string('county')->nullable();
23    $table->string('state')->index()->nullable();
24    $table->string('zip')->nullable();
25    $table->string('country')->index()->nullable();
26 
27    // Additional Census data you will be retrieving from Geocodio
28    $table->integer('acs_number_of_households')->index()->nullable();
29    $table->integer('acs_median_household_income')->index()->nullable();
30});

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

1/**
2* The event map for the model.
3*
4* @var array
5*/
6protected $dispatchesEvents = [
7    'created' => BusinessCreated::class,
8];

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.

 1<?php
 2 
 3namespace AppEvents;
 4 
 5use AppModelsBusiness;
 6use IlluminateFoundationEventsDispatchable;
 7use IlluminateQueueSerializesModels;
 8 
 9class BusinessCreated
10{
11    use Dispatchable, SerializesModels;
12 
13    /**
14    * The business instance that was created.
15    */
16    public $business;
17 
18    /**
19    * Create a new event instance.
20    *
21    * @param Business $business
22    * @return void
23    */
24    public function __construct(Business $business)
25    {
26       $this->business = $business;
27    }
28}

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

1composer require geocodio/geocodio-library-php`
2 
3php artisan vendor:publish --provider="GeocodioGeocodioServiceProvider"

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:

1php artisan make:listener GeocodeBusiness

 1<?php
 2 
 3namespace AppListeners;
 4 
 5use AppEventsBusinessCreated;
 6use GeocodioGeocodio;
 7use IlluminateContractsQueueShouldQueue;
 8use IlluminateQueueInteractsWithQueue;
 9 
10class GeocodeBusiness implements ShouldQueue
11{
12    use InteractsWithQueue;
13 
14    /**
15     * Use dependency injection to instantiate a fully configured Geocodio class
16     */
17    private $geocodio;
18 
19    public function __construct(Geocodio $geocodio)
20    {
21        $this->geocodio = $geocodio;
22    }
23 
24    // $afterCommit is available in Laravel 8.x
25    // See https://github.com/laravel/ideas/issues/1441 for alternative ideas and context.
26    public $afterCommit = true;
27 
28    /**
29     * Handle the event.
30     *
31     * @param  BusinessCreated  $event
32     * @return void
33     */
34    public function handle(BusinessCreated $event)
35    {
36        $business = $event->business;
37 
38        // Hit the Geocodio API, request additional census data, and limit the results to one.
39        // https://www.geocod.io/docs/#geocoding
40        $response = $this->geocodio->geocode($business->user_supplied_address, ['acs-economics'], 1);
41        $results = $response->results[0];
42 
43        // Pull out high level street format and coordinates
44        $business->formatted_address = $results->formatted_address;
45        $business->latitude = $results->location->lat;
46        $business->longitude = $results->location->lng;
47 
48        // The address components, which we'll use for filtering in Metabase
49        $addressComponents = $results->address_components;
50        $business->street = $addressComponents->number . " " . $addressComponents->formatted_street;
51        $business->city = $addressComponents->city;
52        $business->county = $addressComponents->county;
53        $business->state = $addressComponents->state;
54        $business->zip = $addressComponents->zip;
55        $business->country = $addressComponents->country;
56 
57        // Additional census data
58        $ecomData = $results->fields->acs->economics;
59        $business->acs_number_of_households = $ecomData->{'Number of households'}->Total->value;
60        $business->acs_median_household_income = $ecomData->{'Median household income'}->Total->value;
61 
62        // Make sure we explicitly persist the changes, since we are in an afterCommit callback
63        $business->save();
64    }
65}

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!

 1/**
 2 * The event listener mappings for the application.
 3 *
 4 * @var array>
 5 */
 6protected $listen = [
 7    BusinessCreated::class => [
 8        GeocodeBusiness::class,
 9    ]
10];

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.

 1<?php
 2 
 3namespace AppListeners;
 4 
 5use AppEventsCheckInCreated;
 6use GeocodioGeocodio;
 7use IlluminateContractsQueueShouldQueue;
 8use IlluminateQueueInteractsWithQueue;
 9 
10class ReverseGeocodeCheckIn implements ShouldQueue
11{
12    use InteractsWithQueue;
13 
14    /*
15     * Use dependency injection to instantiate a fully configured Geocodio class
16     */
17    private $geocodio;
18 
19    public function __construct(Geocodio $geocodio)
20    {
21        $this->geocodio = $geocodio;
22    }
23 
24    // $afterCommit is available in Laravel 8.x
25    // See https://github.com/laravel/ideas/issues/1441 for alternative ideas and context.
26    public $afterCommit = true;
27 
28    /**
29     * Handle the event.
30     *
31     * @param  CheckInCreated  $event
32     * @return void
33     */
34    public function handle(CheckInCreated $event)
35    {
36        $checkIn = $event->checkIn;
37 
38        // Hit the Geocodio Reverse Geocode API, request additional census data, and limit the results to one.
39        $response = $this->geocodio->reverse($checkIn->latitude. "," . $checkIn->longitude, ['acs-economics'], 1);
40        $results = $response->results[0];
41 
42        // Look familiar? The Geocodio reverse geocode response is the same format as the forward geocode API
43        // Pull out high level street format and coordinates
44        $checkIn->formatted_address = $results->formatted_address;
45        $checkIn->latitude = $results->location->lat;
46        $checkIn->longitude = $results->location->lng;
47 
48        // The address components, which we'll use for filtering in Metabase
49        $addressComponents = $results->address_components;
50        $checkIn->street = $addressComponents->number . " " . $addressComponents->formatted_street;
51        $checkIn->city = $addressComponents->city;
52        $checkIn->county = $addressComponents->county;
53        $checkIn->state = $addressComponents->state;
54        $checkIn->zip = $addressComponents->zip;
55        $checkIn->country = $addressComponents->country;
56 
57        // Additional census data
58        $ecomData = $results->fields->acs->economics;
59        $checkIn->acs_number_of_households = $ecomData->{'Number of households'}->Total->value;
60        $checkIn->acs_median_household_income = $ecomData->{'Median household income'}->Total->value;
61 
62        // Make sure you explicitly persist the changes, since you are in an afterCommit callback
63        $checkIn->save();
64    }
65}

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.