In this lesson, we're going to learn how to filter results by geospatial area. To illustrate this, I've set up this demo app which includes a map of Canada. On the map, I'm displaying all the customers in our database. They're the little white dots. As well as five custom-defined sales regions. They're the big colored shapes. I'm using Mapbox to create this map. If you'd like to try it yourself, you'll need to register for a free API token at Mapbox.com. Then, simply set your Mapbox token as an environment variable. If we look at the customers database table migration,

Then, simply set your Mapbox token as an environment variable. If we look at the customers database table migration, we can see that we're saving the customer's location as a point. A point simply represents a specific longitude and latitude location. And if we look at the regions database table migration, we can see that we're saving the geometric shape using the geometry column type. Both of these data types are supported by MySQL and Postgres, and the layer about migrations have built-in support for them. So, getting a longitude and latitude point for each customer is simple enough. However, you might be wondering, how do we set up the geometric shapes for the regions?

So, getting a longitude and latitude point for each customer is simple enough. However, you might be wondering, how do we set up the geometric shapes for the regions? Well, if we look at the database seeder, you can see that I'm seeding the region's geometry column with a multi-polygon shape. A multi-polygon shape is simply a series of longitude and latitude points that define an area on a map. And there are different ways to acquire these shapes. For example, there are tons of shape files available on the internet for common things like countries, provinces and states, municipalities, and more. However, for these example sales regions, I've actually created them myself using QGIS,

for common things like countries, provinces and states, municipalities, and more. However, for these example sales regions, I've actually created them myself using QGIS, which is a free cross-platform geographic information system GIS application. I literally drew the shapes and then exported them to use in my database seeder. For this lesson, I don't want to focus too much on how to get the geographic points and shapes into your database. Rather, I want to look at how to query this data using Eloquent once you do. The first thing I want to do is filter the customers to only those in a specific sales region. Imagine a CRM style app where you'd want to find certain customers in a specific geographic region. A query like this can be very useful. Let's start by going to the CustomersController and adding a new inRegion scope call to the customer query.

A query like this can be very useful. Let's start by going to the CustomersController and adding a new inRegion scope call to the customer query. And this scope will take an instance of a Region as an argument. Let's pass in the Prairies region by loading it from the database. Next, let's go to the Customer model and add this new scope. public function scopeInRegion. And that'll take an instance of the QueryBuilder as well as the Region. So how exactly are we going to write a query that limits our customers to only those within a specific region? Well, we can use the ST_Contains spatial function to do this. And to do that, we'll need to write a whereRaw query.

Well, we can use the ST_Contains spatial function to do this. And to do that, we'll need to write a whereRaw query. query whereRaw. And then we'll call the ST_Contains function. The first argument required by this function is the area we want to search in. So in our case, that's the region which we passed in as an argument to our scope method. So we'll just put it in as a bounded parameter. And the second argument is the point we're testing. And in our case, that's the customer's location. And finally, we just need to pass in the region's geometry as our bounded value.

And in our case, that's the customer's location. And finally, we just need to pass in the region's geometry as our bonded value. And now if we hit refresh in the browser, we can see that this is working. We're now getting customers only within the prairie sales region. How neat is that? And if we look at the Laravel debug bar, we can see the customers query we just generated. Select from customers where ST contains. And the first argument is some binary data, which is the sales region geometry that we passed in as our bonded parameter. And the second argument is the customer's location. Okay, let's try the inverse this time.

And the second argument is the customer's location. Okay, let's try the inverse this time. Instead of finding the customers that exist in a specific sales region, let's find the region based on a customer. Let's go back to our CustomersController and remove our existing queries. Now let's select just one customer randomly from the database. Next, let's find the region for this customer. We'll call a new hasCustomer scope passing in a random customer. Now let's go to the Region model and add this new scope. public function scopeHasCustomer, and that'll take an instance of the query builder as well as the customer. Again, we'll use the STContains spatial function to do this.

public function scopeHasCustomer($query, $customer) { Again, we'll use the ST_Contains spatial function to do this. Query::whereRaw(ST_Contains(regions.geometry, $customer->location)); And this time for the first argument, we'll pass in the regions.geometry column. And the second argument will be our $customer->location, which has been passed in as an argument to our scope method. So we'll add it as a bonded parameter. And finally, we just need to pass through the $location as the bonded value. Now, if we go back to the browser and hit refresh a few times, we can see that we're randomly selecting a customer from the database and then getting only the region where that customer exists. Very, very cool.

we can see that we're randomly selecting a Customer from the database and then getting only the region where that Customer exists. Very, very cool.