In this lesson, we're going to learn how to use geospatial functions to get the distance between two geographic points. To illustrate this, I've set up the stores demo app, which lists all the locations for a franchise. And I've seeded the database with almost four and a half thousand stores. If we look at our stores table database migration, we can see that each store includes an address and the corresponding longitude and latitude coordinates. What we want to do in this lesson is update our page to include a distance away value in kilometers for each store in the current user. Let's start by simply updating our stores blade view to display this new column. We'll duplicate the city heading and update it for the distance. And then we'll do the same thing for the column. And if we hit refresh in the browser, we can see our new column has been added. But of course, no value is being displayed yet. Next, let's go to our StoresController and add

for the column. And if we hit refresh in the browser, we can see our new column has been added. But of course, no value is being displayed yet. Next, let's go to our StoresController and add a selectDistanceTo scope call to our query, which takes some coordinates as the first argument. For the purpose of this lesson, I'm not going to set up the entire layer of Auth scaffolding. We'll simply hard code in some coordinates. But of course, in a real app, this would be data you'd get from your current user. Now let's go to our Store model and create this new scope public function scopeSelectDistanceTo and that'll take an instance of the QueryBuilder, as well as the coordinates. Now, whenever I create scopes for select statements, I always check to see if any other columns have been selected first. And if none have, I'll add a select * to the query to get all the columns. And the reason I do this is because otherwise

check to see if any other columns have been selected first. And if none have, I'll add a select * to the query to get all the columns. And the reason I do this is because otherwise Laravel will only select the single extra column that we're about to add. OK, now let's calculate our distance. To do this, we're going to use the ST_Distance spatial function that's available in MySQL. And to do that, we'll need to use a selectRaw query builder method query->selectRaw, and then we'll call the ST_Distance function. And this function takes two arguments, the first location and the second location. So in our case, the first argument will be the store's location and the second argument will be the coordinates provided to our scope. However, we can't simply pass in the longitude and latitude values here. The ST_Distance function requires that they are provided as valid geometry objects.

Now we can alias a calculated value to the distance column and pass in our coordinates as the bindings. And now if we go back to the browser and hit refresh, we can see that we're successfully getting our calculated distance. How cool is that? Now you might be wondering what units is this calculated distance using? It's in meters. Let's update our store's blade view to convert this to kilometers. We'll use the number_format function and then divide the distance by a thousand to get kilometers. And finally, we'll round it to two decimal places. And if we hit refresh in the browser, we can see that's much more readable. In the next lesson, we'll update this page to limit the results to only stores within a 10 kilometer distance.