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.

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:

• Chicago, IL neighborhood boundaries
• Baltimore, MD neighborhood boundaries
• East Baton Rouge Parish, Baton Rouge, LA neighborhood boundaries

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).

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

• toin0u/geocoder-laravel for using Google’s Geocoding API
• grimzy/laravel-mysql-spatial for parsing WKT geometries and determining if a multipolygon contains a point.

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.

$ laravel new neighborhood-finder

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

$ cd neighborhood-finder/

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.

$ php artisan make:model Models\\Neighborhood --migration

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.

/**
 * Run the migrations.
 *
 * @return void
 */
public function up()
{
    Schema::create('neighborhoods', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->string('city');
        $table->string('state', 2);
        $table->multiPolygon('geometry');
        $table->timestamps();
    });
}

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.

$ composer require "grimzy/laravel-mysql-spatial"

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.

<?php

namespace App\Models;

use Grimzy\LaravelMysqlSpatial\Eloquent\SpatialTrait;
use Illuminate\Database\Eloquent\Model;

class Neighborhood extends Model
{
    use SpatialTrait;

    /**
     * The attributes that are mass-assignable.
     *
     * @var array
     */
    protected $fillable = [
        'name',
        'geometry',
    ];

    /**
     * The attributes that are spatial fields.
     *
     * @var array
     */
    protected $spatialFields = [
        'geometry'
    ];
}

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:

*
!.gitignore

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.

$ php artisan make:seeder BaseNeighborhoodSeeder

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

<?php

use App\Models\Neighborhood;
use Grimzy\LaravelMysqlSpatial\Types\MultiPolygon;
use Illuminate\Database\Seeder;

abstract class BaseNeighborhoodSeeder extends Seeder
{
    /**
     * Mode for opening a file as read-only.
     */
    const FILE_MODE_READ = 'r';

    /**
     * Parses the given geometry value into a Multipolygon.
     *
     * @param mixed $geometry the geometry to parse
     * @return \Grimzy\LaravelMysqlSpatial\Types\MultiPolygon
     */
    protected abstract function parseGeometryToMultiPolygon($geometry): MultiPolygon;

    /**
     * Reads all records in a flat file, parses the geometry into a multipolygon,
     * and saves a Neighborhood in the database for each record.
     *
     * @param string $file_path path to the file to read data from
     * @param int $name_index the index of the column containing the neighborhood name
     * @param int $geometry_index the index of the column containing the neighborhood geometry
     * @param string $city the name of the neighborhoods' city
     * @param string $state the name of the neighborhoods' state
     * @param bool $skip_first_row if the first row of the file should be skipped (if there's a header row)
     * @param bool $use_title_case if the neighborhood names should be converted to Title Case
     * @throws \Throwable
     */
    protected function seedFromFlatFile(string $file_path,
                                        int $name_index,
                                        int $geometry_index,
                                        string $city,
                                        string $state,
                                        bool $skip_first_row,
                                        bool $use_title_case) {

        // throw an exception unless a file exists at the given location
        throw_unless(file_exists($file_path), new Exception("No file found at path '$file_path'"));

        try {
            // open the specified file at the given location
            $file = fopen($file_path, self::FILE_MODE_READ);

            // if the first row should be skipped, read the first row of the file
            if ($skip_first_row) {
                fgetcsv($file);
            }

            // while there's a row to be read in the file, read the next row
            while ($row = fgetcsv($file)) {
                // get the neighborhood name from the specified index
                $name = $row[$name_index];

                // if the name should be converted to Title Case, convert it
                if ($use_title_case) {
                    $name = title_case($name);
                }

                // parse the geometry at the specified index into a multipolygon
                $multipolygon = $this->parseGeometryToMultiPolygon($row[$geometry_index]);

                // make the new neighborhood model by filling the name, city, state, and geometry
                $neighborhood = new Neighborhood([
                    'name' => $name,
                    'city' => $city,
                    'state' => $state,
                    'geometry' => $multipolygon,
                ]);

                // throw an exception unless the neighborhood could be saved
                throw_unless($neighborhood->save(), new Exception("Failed to save neighborhood '$name'"));
            }
        } finally {
            // if the file has been opened, close it
            if (! empty($file)) {
                fclose($file);
            }
        }
    }
}

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.

$ php artisan make:seeder ChicagoIllinoisNeighborhoodSeeder

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

<?php

use Grimzy\LaravelMysqlSpatial\Types\MultiPolygon;

class ChicagoIllinoisNeighborhoodSeeder extends BaseNeighborhoodSeeder
{
    /**
     * Index of the name column.
     */
    const COLUMN_INDEX_NAME = 0;

    /**
     * Index of the geometry column.
     */
    const COLUMN_INDEX_GEOMETRY = 1;

    /**
     * Name of the neighborhoods' city.
     */
    const CITY = 'Chicago';

    /**
     * Name of the neighborhoods' state.
     */
    const STATE = 'IL';

    /**
     * Path of the seed file relative to the `database` directory.
     */
    const DATABASE_FILE_PATH = 'seeds/flat-files/chicago-illinois.csv';

    /**
     * If the file has a header row.
     */
    const HAS_HEADER_ROW = true;

    /**
     * If the neighborhood names should be converted to Title Case.
     */
    const USE_TITLE_CASE = false;

    /**
     * Run the database seeds.
     *
     * @throws \Throwable
     */
    public function run()
    {
        // resolve the path of the seed file
        $file_path = database_path(self::DATABASE_FILE_PATH);

        // seed the neighborhoods from the flat file
        $this->seedFromFlatFile($file_path,
            self::COLUMN_INDEX_NAME,
            self::COLUMN_INDEX_GEOMETRY,
            self::CITY,
            self::STATE,
            self::HAS_HEADER_ROW,
            self::USE_TITLE_CASE);
    }

    /**
     * Parses the geometry to a multipolygon from well-known text.
     *
     * @param mixed $geometry
     * @return \Grimzy\LaravelMysqlSpatial\Types\MultiPolygon
     */
    protected function parseGeometryToMultiPolygon($geometry): MultiPolygon
    {
        return MultiPolygon::fromWKT($geometry);
    }
}

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.

$ php artisan make:seeder BaltimoreMarylandSeeder

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

<?php

use Grimzy\LaravelMysqlSpatial\Types\MultiPolygon;

class BaltimoreMarylandSeeder extends BaseNeighborhoodSeeder
{
    /**
     * Index of the name column.
     */
    const COLUMN_INDEX_NAME = 3;

    /**
     * Index of the geometry column.
     */
    const COLUMN_INDEX_GEOMETRY = 1;

    /**
     * Name of the neighborhoods' city.
     */
    const CITY = 'Baltimore';

    /**
     * Name of the neighborhoods' state.
     */
    const STATE = 'MD';

    /**
     * Path of the seed file relative to the `database` directory.
     */
    const DATABASE_FILE_PATH = 'seeds/flat-files/baltimore-maryland.csv';

    /**
     * If the file has a header row.
     */
    const HAS_HEADER_ROW = true;

    /**
     * If the neighborhood names should be converted to Title Case.
     */
    const USE_TITLE_CASE = false;

    /**
     * Run the database seeds.
     *
     * @throws \Throwable
     */
    public function run()
    {
        // resolve the path of the seed file
        $file_path = database_path(self::DATABASE_FILE_PATH);

        // seed the neighborhoods from the flat file
        $this->seedFromFlatFile($file_path,
            self::COLUMN_INDEX_NAME,
            self::COLUMN_INDEX_GEOMETRY,
            self::CITY,
            self::STATE,
            self::HAS_HEADER_ROW,
            self::USE_TITLE_CASE);
    }

    /**
     * Parses the geometry to a multipolygon from well-known text.
     *
     * @param mixed $geometry
     * @return \Grimzy\LaravelMysqlSpatial\Types\MultiPolygon
     */
    protected function parseGeometryToMultiPolygon($geometry): MultiPolygon
    {
        return MultiPolygon::fromWKT($geometry);
    }
}

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.

$ php artisan make:seeder EastBatonRougeParishSeeder

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

<?php

use Grimzy\LaravelMysqlSpatial\Types\MultiPolygon;
use Grimzy\LaravelMysqlSpatial\Types\Polygon;

class EastBatonRougeParishSeeder extends BaseNeighborhoodSeeder
{
    /**
     * Index of the name column.
     */
    const COLUMN_INDEX_NAME = 2;

    /**
     * Index of the geometry column.
     */
    const COLUMN_INDEX_GEOMETRY = 1;

    /**
     * Name of the neighborhoods' city.
     */
    const CITY = 'Baton Rouge';

    /**
     * Name of the neighborhoods' state.
     */
    const STATE = 'LA';

    /**
     * Path of the seed file relative to the `database` directory.
     */
    const DATABASE_FILE_PATH = 'seeds/flat-files/east-baton-rouge-parish-louisiana.csv';

    /**
     * If the file has a header row.
     */
    const HAS_HEADER_ROW = true;

    /**
     * If the neighborhood names should be converted to Title Case.
     */
    const USE_TITLE_CASE = true;

    /**
     * Run the database seeds.
     *
     * @throws \Throwable
     */
    public function run()
    {
        // resolve the path of the seed file
        $file_path = database_path(self::DATABASE_FILE_PATH);

        // seed the neighborhoods from the flat file
        $this->seedFromFlatFile($file_path,
            self::COLUMN_INDEX_NAME,
            self::COLUMN_INDEX_GEOMETRY,
            self::CITY,
            self::STATE,
            self::HAS_HEADER_ROW,
            self::USE_TITLE_CASE);
    }

    /**
     * Parses the geometry to a multipolygon from well-known text.
     *
     * @param mixed $geometry
     * @return \Grimzy\LaravelMysqlSpatial\Types\MultiPolygon
     */
    protected function parseGeometryToMultiPolygon($geometry): MultiPolygon
    {
        // parse the well-known text into a polygon
        $polygon = Polygon::fromWKT($geometry);

        // return a multipolygon containing the polygon
        return new MultiPolygon([$polygon]);
    }
}

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.

<?php

use Illuminate\Database\Seeder;

class DatabaseSeeder extends Seeder
{
    /**
     * Seed the application's database.
     *
     * @return void
     */
    public function run()
    {
        $this->call([
            ChicagoIllinoisNeighborhoodSeeder::class,
            BaltimoreMarylandSeeder::class,
            EastBatonRougeParishSeeder::class,
        ]);
    }
}

Let’s quickly regenerate our autoload files using composer.

$ composer dump-autoload

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.

$ php artisan migrate --seed

Installing the Second Package

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

$ composer require "toin0u/geocoder-laravel"

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.

GOOGLE_MAPS_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.

GOOGLE_MAPS_API_KEY=

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.

Route::get('/', 'HomeController@show')->name('home.show');
Route::post('/', 'HomeController@submit')->name('home.submit');

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.

$ php artisan make:controller HomeController

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

<?php

namespace App\Http\Controllers;

use App\Models\Neighborhood;
use Grimzy\LaravelMysqlSpatial\Types\Point;
use Illuminate\Http\Request;

class HomeController extends Controller
{
    /**
     * The session key for storing the success message.
     */
    const SESSION_KEY_SUCCESS = 'success';

    /**
     * The session key for storing the error message.
     */
    const SESSION_KEY_ERROR = 'error';

    /**
     * The result message for an address that could not be geocoded.
     */
    const RESULT_BAD_ADDRESS = 'Failed to find a location for that address!';

    /**
     * The result message for an address that does not fall in any exiting Neighborhood's geometry.
     */
    const RESULT_NO_RESULTS = 'No results for that address!';

    /**
     * The result message prefix for a found Neighborhood.
     */
    const RESULT_NEIGHBORHOOD_PREFIX = 'That address is in ';

    /**
     * The route name for showing the home page.
     */
    const ROUTE_NAME_SHOW_HOME = 'home.show';

    /**
     * Shows the home page.
     *
     * @return \Illuminate\View\View
     */
    public function show()
    {
        return view('home');
    }

    /**
     * Handles submission of an address and returns a redirect to the home page with success or error message.
     *
     * @param \Illuminate\Http\Request $request
     * @return \Illuminate\Http\RedirectResponse
     */
    public function submit(Request $request)
    {
        // validate the request
        $this->validate($request, [
            'address' => 'required',
        ]);

        // get the given address from the request
        $address = $request->input('address');

        // make the geocoder
        $geocoder = app('geocoder');

        // geocode the address and get the first result
        $result = $geocoder->geocode($address)->get()->first();

        // if a result couldn't be found, redirect to the home page with a result message flashed to the session
        if (! $result) {
            return redirect(route(self::ROUTE_NAME_SHOW_HOME))->with(self::SESSION_KEY_ERROR, self::RESULT_BAD_ADDRESS);
        }

        // get the coordinates of the geocoding result
        $coordinates = $result->getCoordinates();

        // get the latitude of the coordinates
        $lat = $coordinates->getLatitude();

        // get the longitude of the coordinates
        $lng = $coordinates->getLongitude();

        // create a new point using the coordinates
        $point = new Point($lat, $lng);

        // get the first Neighborhood that has geometry containing the point
        $neighborhood = Neighborhood::contains('geometry', $point)->first();

        // if a Neighborhood couldn't be found, redirect to the home page with a result message flashed to the session
        if (! $neighborhood) {
            return redirect(route(self::ROUTE_NAME_SHOW_HOME))->with(self::SESSION_KEY_ERROR, self::RESULT_NO_RESULTS);
        }

        // format the result message for the found Neighborhood
        $message = $this->formatNeighborhoodResult($neighborhood);

        // redirect to the home page with the result message flashed to the session
        return redirect(route(self::ROUTE_NAME_SHOW_HOME))->with(self::SESSION_KEY_SUCCESS, $message);
    }

    /**
     * Format the result message for a found neighborhood.
     *
     * @param \App\Models\Neighborhood $neighborhood
     * @return string
     */
    private function formatNeighborhoodResult(Neighborhood $neighborhood) {
        return self::RESULT_NEIGHBORHOOD_PREFIX . $neighborhood->name . ', ' . $neighborhood->city . ', ' . $neighborhood->state . '.';
    }
}

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:

SELECT * FROM `neighborhoods` WHERE ST_Contains(`geometry`, ST_GeomFromText('POINT(0 0)')) LIMIT 1

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.

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-alpha.6/css/bootstrap.min.css" integrity="sha384-rwoIResjU2yc3z8GV/NPeZWAv56rSmLldC3R/AZzGRnGxQQKnKkoFVhFQhNUwEyJ" crossorigin="anonymous">

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

.title {
    font-size: 64px;
}

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

<script src="https://code.jquery.com/jquery-3.1.1.slim.min.js" integrity="sha384-A7FZj7v+d/sdmMqp/nOQwliLvUsJfDHW+k9Omg/a/EheAdgtzNs3hpfag6Ed950n" crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/tether/1.4.0/js/tether.min.js" integrity="sha384-DztdAPBWPRXSA/3eYEEUWrWCy7G5KFbe8fFjk5JAIxUYHKkDx6Qin1DkWx51bBrb" crossorigin="anonymous"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-alpha.6/js/bootstrap.min.js" integrity="sha384-vBWWzlZJ8ea9aCX4pEW3rVHjgjt7zpkNpZk+02D9phzyeVkE+jo0ieGizqPLForn" crossorigin="anonymous"></script>

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.

<div class="content">
    <div class="title m-b-md">
        Neighborhood Finder
    </div>
    @if (session('success'))
        <div class="alert alert-success">
            {{ session('success') }}
        </div>
    @elseif (session('error'))
        <div class="alert alert-danger">
            {{ session('error') }}
        </div>
    @endif
    <div class="col-12 text-left">
        <form class="form-horizontal" method="POST" action="{{ route('home.submit') }}">
            @csrf
            <div class="form-group{{ $errors->has('address') ? ' has-error' : '' }}">
                <label for="address" class="control-label">Enter an address</label>

                <input id="address" name="address" type="text" class="form-control" required autofocus>

                @if ($errors->has('address'))
                    <span class="help-block">
                        <strong>{{ $errors->first('address') }}</strong>
                    </span>
                @endif
            </div>
            <div class="form-group text-right">
                <button type="submit" class="btn btn-primary">
                    Submit
                </button>
            </div>
        </form>
    </div>
</div>

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:

1558 N Damen Ave, Chicago, IL 606221001 E Fayette St, Baltimore, MD 21202201 Community College Dr, Baton Rouge, LA 70806123 Fake St, NowhereYour Address

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.