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:
1Schema::create(‘businesses’, function (Blueprint $table) {
5 // Columns entered by users
7 $table->string(‘user_supplied_address’)->nullable();
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();
16 // A single formatted string, useful for searching within future analysis
17 $table->string(‘formatted_address’)->nullable();
19 // Distinct columns for address components. Useful for filters, such as per state, in Metabase.
20 $table->string(‘street’)->nullable();
25 $table->string(‘country’)->index()->nullable();
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.
2* The event map for the model.
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.
5use AppModelsBusiness;
7use IlluminateQueueSerializesModels;
14 * The business instance that was created.
19 * Create a new event instance.
24 public function __construct(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:
1composer require geocodio/geocodio-library-php`
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
5use AppEventsBusinessCreated;
8use IlluminateQueueInteractsWithQueue;
15 * Use dependency injection to instantiate a fully configured Geocodio class
19 public function __construct(Geocodio $geocodio)
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;
34 public function handle(BusinessCreated $event)
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];
43 // Pull out high level street format and coordinates
44 $business->formatted_address = $results->formatted_address;
46 $business->longitude = $results->location->lng;
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;
55 $business->country = $addressComponents->country;
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();
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.
8 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.
5use AppEventsCheckInCreated;
8use IlluminateQueueInteractsWithQueue;
15 * Use dependency injection to instantiate a fully configured Geocodio class
19 public function __construct(Geocodio $geocodio)
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;
34 public function handle(CheckInCreated $event)
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];
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;
46 $checkIn->longitude = $results->location->lng;
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;
55 $checkIn->country = $addressComponents->country;
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();
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.
This content was originally published here.