Getting Started with Geospatial Data in Laravel

Brice Hartmann
Jul 17, 2018 · 18 min read

Today we’ll be learning about working with geospatial data, or data relating to geographical locations, in Laravel 5.6. As an exercise for working with this kind of data we’ll be building a simple USA Neighborhood Finder application.

screenshot of a successful result in our application

There are three main learning objectives for this article:

  1. How to use Google’s Geocoding API to geocode an address into corresponding coordinates consisting of a longitude and latitude.
  2. How to import geospatial data in Well-Known Text (WKT) format into a spatial column in MySQL.
  3. How to determine if a point (in this case a longitude and latitude) is contained by a geometry (in this case a neighborhood boundary).

Overview

The flow of our application is as follows:

  1. A user enters an address and clicks “Submit”.
  2. The name of the neighborhood which contains the address is displayed to the user (or an error message stating no location or neighborhood could be found).

For the purposes of this article we won’t be tracking down the neighborhood boundaries of every neighborhood in the USA; instead we’ll be using three example data sets but set things up so more data sources can easily be added in the future.

You can take a look at the data sources using these links:

A basic schema of our application’s database is shown below. We only need two tables: Laravel’s table for migrations and a table for storing neighborhoods. Our spatial column will be named geometry and be of the multipolygon type. Think of a multipolygon as a collection of polygons. We’re using a multipolygon because a neighborhood may have more than one polygon to define its boundary. If a neighborhood uses a polygon to define its boundary we can always create multipolygon containing a single polygon (which we’ll be doing later).

schema of our application’s database

In addition to the Laravel Framework, we’ll be using two more packages to build our application:

We will also be using Bootstrap 4 for basic styling.

Creating Our Application

For this article it’s assumed you’re comfortable using a development environment for Laravel (such as Homestead).

Generating the Project

First, let’s create a new Laravel project using the terminal.

Wait for the installation to finish and change directories to the root of the project.

Next let’s clean up some of the things Laravel comes with that we won’t be using for our application. This includes authentication controllers, the User model/migration/factory, and the password_resets table.

  • delete the app/Http/Controllers/Auth directory
  • delete app/User.php
  • delete database/factories/UserFactory.php
  • delete database/migrations/*_create_users_table.php
  • delete database/migrations/*_create_password_resets_table.php

Now we’re ready to create the model and migration for a Neighborhood. Note how we’re creating the model in the App\Models namespace.

For our application each Neighborhood will have an (auto-incrementing) id, a name, a city, a state(abbreviation), and a geometry representing the neighborhood’s boundary as a multipolygon.

Open up the generated database/migrations/*_create_neighborhoods_table.php migration and edit the up method as shown below.

Installing the First Package

Now let’s install our first package using composer: grimzy/laravel-mysql-spatial. This package will allow us to easily work with spatial data types.

Let composer install the package dependencies and regenerate autoload files.

The Neighborhood Model

Our Neighborhood model will be using the SpatialTrait found in the grimzy/laravel-mysql-spatial package. The package looks for any attributes defined in the $spatialFields array on a model using this trait.

Edit app/Models/Neighborhood.php to match below. Notice how a spatial field is still eligible to be fillable through mass assignment.

Gathering Test Data

Many cities have data portals open to the public. To seed our database with neighborhoods including geospatial data for boundaries, our application will use Comma-separated values (CSV) files exported from three of these data portals.

Create a directory at database/seeds/flat-files for a place to store the flat files.

We want to ensure none of these flat files are checked into source control, so create a .gitignore file at database/seeds/flat-files/.gitignore containing the following:

The * entry is a wildcard telling git to ignore all files in the directory. The !.gitignore entry excludes the .gitignore file from the wildcard so it is still checked into source control.

Download the following CSV files to each location specified below.

  • this CSV to database/seeds/flat-files/chicago-illinois.csv
  • this CSV to database/seeds/flat-files/baltimore-maryland.csv
  • this CSV to database/seeds/flat-files/east-baton-rouge-parish-louisiana.csv

Let’s take a quick peek at what this data looks like. Open up database/seeds/flat-files/chicago-illinois.csv and notice the file contains a header row specifying the columns. The two columns we want are PRI_NEIGH (primary neighborhood) and the_geom (the geometry data).

From the first row of data in the file, copy the MULTIPOLYGON(((...))) part. This is the WKT representation of the neighborhood’s boundary.

To visualize WKT data one of my favorite tools is Wicket by Arthur Endsley. Open up Wicket, paste the WKT data from your clipboard into the text box, and click “Map It!”. You’ll see the mapped multipolygon for Chicago’s neighborhood Grand Boulevard.

Creating Seeders

Now that we have our flat files and an understanding of what the data looks like let’s create some seeders.

For this exercise we’ll keep things simple with one seeder per file, each of which will extend a base class. The base class will hold logic for reading a CSV and creating a Neighborhood record. It will also contain an abstract method for transforming a geometry record into a Multipolygon. Each seeder extending the base class will implement this abstract method with logic specific to the file being processed. These seeders will also contain the run() method to be called by Laravel.

While this pattern works well for our purposes as we only have a few flat files to process, for a larger application with possibly hundreds of files I’d suggest a variation of this pattern and not defining one seeder per file.

First, let’s create our BaseNeighborhoodSeeder using artisan.

Update the created file at database/seeds/BaseNeighborhoodSeeder.php to match below.

Here we’re defining an abstract class which cannot be instantiated; this class must be extended to be used. At the top of the class we have a constant for the file mode we’ll be using to open CSV files.

We define the abstract method parseGeometryToMulipolygon($geometry) and declare it returns a Multipolygon. This method must be implemented by any class extending BaseNeighborhoodSeeder and will contain the logic necessary for converting the geometry data in each CSV record to a Multipolygon. For our purposes this will always be parsing WKT but it could easily be parsing another format such as GeoJSON.

The seedFromFlatFile method contains parameters for the path to the file to read data from, the index of the neighborhood name column, the index of the neighborhood boundary geometry column, the name of the city for the neighborhoods, the name of the state for the neighborhoods, whether or not to skip the first row of the file (in case there is a header row), and whether or not the neighborhood name should be converted to Title Case before being saved.

In this method we first check if a file exists at $file_path using PHP’s file_exists function. If a file does not exist at the specified path we throw an exception.

Next, inside a try block, we open the file for reading using fopen with the file mode 'r'. If the $skip_first_row flag is true, we read the first row of the file using PHP’s function fgetcsv. Looping through each row, while there’s still a row left to read in the file, we use fgetcsv to parse the CSV row into an array of data.

Using the given $name_index we get the neighborhood name from the array and if $use_title_case is true we use Laravel’s helper method title_case to convert the string to Title Case. The neighborhood’s geometry is parsed into a MultiPolygon by passing the data of the geometry column into the parseGeometryToMultiPolygon method, which will be implemented by child classes.

Finally we create the new neighborhood record by passing an array of attributes to the Neighborhood model’s constructor. If the model could not be saved, an exception is thrown.

In the finally block we check if the $file variable has a value and if it does, we use fclose to close the file. Putting this logic inside the finally block ensures we close the file even if an exception is thrown.


With our base seeder class in place, we’re ready to create a seeder for each flat file. Start by creating ChicagoIllinoisNeighborhoodSeeder using artisan.

Update the file database/seeds/ChicagoIllinoisDatabaseSeeder.php with the content below.

At the top of the file we have constants for the column indexes of the name and geometry data as well as constants for the neighborhood city, the neighborhood state, the file path relative to the database directory, whether or not the file has a header row, and whether or not the neighborhood names should be converted to Title Case.

Next we have the run method which is called by Laravel when executing the seeder. In this method we first resolve the path of the flat file using Laravel’s helper method database_path. Then we call the parent class’s method seedFromFlatFile using the file path and our constants as arguments.

Finally, we implement the parseGeometryToMultiPolygon method by using the fromWKT static method of Grimzy\LaravelMysqlSpatial\Types\Multipolygon to instantiate a new MultiPolygon and return it. Remember this method will be called by the base class during the execution of the seedFromFlatFile method.


Continuing with our seeders, use artisan to create BaltimoreMarylandSeeder.

Edit the file database/seeds/BaltimoreMarylandSeeder.php to match the contents below.

Notice how simple it was to implement a new flat file seeder because we abstracted away the logic into our base class.


Again using artisan, create our last seeder EastBatonRougeParishSeeder.

Open up the file database/seeds/EastBatonRougeParishSeeder.php and edit the contents to match below.

This time the implementation of the parseGeometryToMultiPolygon method is different. If you check the east-baton-rouge-parish-louisiana.csv file you’ll notice the WKT contains polygons instead of multipolygons, but the method calls for a MultiPolygon to be returned. Therefore we first parse the Polygon from WKT and then create and return a new MutliPolygon using an array containing the Polygon passed to the constructor.

Next we need to edit database/seeds/DatabaseSeeder.php to call each of our seeders. Update the file to match the contents below.

Let’s quickly regenerate our autoload files using composer.

Finally, let’s migrate and seed our database using an artisan command. This will create our neighborhoods table as well as seed all the neighborhoods from our flat files.

Installing the Second Package

Use composer to require the toin0u/geocoder-laravel package we’ll be using to geocode addresses.

While composer is running, this is a great time to get a Google API key for our project. We’ll be using Google’s Geocoding API.

  1. Go to the Google Cloud Console and log in using a Google account.
  2. Create a new project.
  3. Under the APIs & Services dashboard, click ENABLE APIS AND SERVICES.
  4. Enable the Geocoding API by searching for Geocoding API, clicking on the result, and then clicking the button labeled ENABLE.
  5. Under APIs & Services go to Credentials.
  6. Click Create and select API Key.
  7. Copy the generated API key to your clipboard.

Now we’re going to add the necessary configuration for the geocoding package we just installed.

Edit the .env environment file at the root of our project, adding the key GOOGLE_MAPS_API_KEY and pasting in the value of your API key.

For posterity’s sake let’s also add an entry in .env.example for the same key. Remember, don’t add your API key here; this file is only a template and is checked into source control.

The Home Controller

Now let’s define our routes by editing routes/web.php, deleting the existing welcome route, and replacing it with the routes below.

The first route, named home.show, is for displaying the home page. The second route, named home.submit, will handle the submission of addresses and return a response containing the result of the search.

Next, create a HomeController using artisan.

Edit app/Http/Controllers/HomeController.php to match the contents below.

In this file we first define constants for the session key storing success messages, the session key storing error messages, the text for result messages, and the home.show route name.

In the show method we simply return the view named home using the Laravel helper method view.

The submit method accepts an argument of type Illuminate\Http\Request called $request. Laravel will automatically inject this variable containing the current request data.

First we validate the request by specifying a rule for address making the field required. The validated address is then retrieved using the input method on the $request variable. We use Laravel’s app helper method which uses the service container to resolve an instance of the geocoder. Using method chaining we geocode the given address and get the first result. If a result couldn’t be found for the given address we redirect the user to the home page with an error message flashed to the session.

Next we get the longitude and latitude coordinates from the result and create a new Grimzy\LaravelMysqlSpatial\Types\Point instance by passing the coordinates into the constructor.

The eloquent query scope contains provided by the grimzy/laravel-mysql-spaital package is then used to scope the query by records with a geometry containing the point. We use the first method to get the first result. This will generate a query along the lines of:

In this case 'POINT(0 0)' is the WKT representation of our longitude and latitude (which won’t actually be 0, 0 unless our user lives in the middle of the ocean).

Notice that we are using MySQL to calculate if the the geometry contains the point. This is much faster than if we had chunked through all the records and had done the calculation in PHP.

Finally, if a resulting neighborhood containing the point couldn’t be found we return a redirect to the home page with an error message flashed to the session. Otherwise, we format the neighborhood name, city, and state into a success result message (using the formatNeighborhoodResult method) and return a redirect to the home page with the message flashed to the session.

The Home View

Rename the blade template at resources/views/welcome.blade.php to resources/views/home.blade.php and open the file.

Under the <!-- Styles --> comment, add a link to Bootstrap’s style sheet.

Next, in the .title class definition, change the font size to something smaller.

Just before the closing </body> tag, add script tags for Bootstrap’s dependencies jQuery, Popper.js, and Bootstrap’s minified JavaScript file.

Because our application has no authentication, remove the entire @if (Route::has(‘login’)) directive including the contents and closing directive.

Finally, edit the contents of the <div class="content"> div to match below.

Here we use the blade directive @if to check if there’s a message in the success key of the current session. If there is one, we display an alert to the user containing the message. If there isn’t one, we use the blade directive @elseif to check if there’s a message in the error key of the current session, again displaying it to the user if it exists.

Next we define a form with an action specifying our submission route using the route helper method. The @crsf blade directive is used to generate a Cross-Site Request Forgery field and token. If the $errors message bag contains an entry for address we add the has-error class to the form group div and display the error in a help block.

Conclusion

That’s it! Open the project in your browser and try out some different addresses! The address will be geocoded and a neighborhood will be returned if any neighborhoods in the database have a boundary containing the address’s coordinates. If no address coordinates or neighborhood could be found, an error message stating such will be returned.

Try some of these addresses:

Additional Resources

For additional data sources I encourage you to check out this Forbes article as well as Open Data Inception.

You can view the source code for this project on GitHub.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store