MySQL 8 Polygonal map search
A tutorial for building easy locator maps
Welcome back!
Last week I published an article about the powerful combo MySQL 8 and Maps can be. Like I promised at the end of that article, this week I published a tutorial on how to implement a simple example of this technique.
In this quick tutorial I will guide you through building a very simple locator map. Maps like these are common for finding stores, properties, AirBNB homes or even Geocaches. This map won’t be as fully featured as those examples of course, but it will implement the base features in a much simpler way than we commonly see.
I’m making a few assumptions about the knowledge of you as reader, and if you don’t think you fit that image, you may need to research these techniques first:
- You are able to create a new Laravel +Vue application from scratch.
- You understand Docker enough to run it, or are able to set up your own environment to run this application.
- You have a decent understanding of MySQL.
- You are able to create and run a database migration through Laravel.
- You’ve worked with the Google Maps JavaScript API before or have a basic understanding of it.
- You have some idea what I mean by geolocation and what a coordinate is.
- (optional) You’ve read my article about MySQL 8 and Geolocation
Also because this is just a technical demo, I will dispense some best practises like TDD, or tests in general. And in some places the code is not the way how I would usually write it, because additional abstraction would just be a distraction here.
This is not meant to be copy-pasted into a real world project without some significant changes!
So without further ado, let’s get started!
Step 1: What do we need?
For the purposes of this demo, we’ll be making a Laravel + Vue application. From here on out I will assume that you have at least a moderate level of understanding of these tools.
Also, I work with Docker and the demo application I will provide at the end does come with a Docker setup, but you can ignore all that if you prefer to run the application in any other way.
Tools required:
- Laravel 5.8
- Docker (Unless you set up your own environment)
- VueJS 2
- Google Maps JavaScript API
Step 2: Database
For this application we really only need one table. Let’s call it markers and set up a migration for it.
$ php artisan make:migration create_markersThe migration itself is very small. Just a primary key and a coordinate column.
Schema::create('markers', function (Blueprint $table) {
$srid = env('SPATIAL_REF_ID', 4326);
$table->bigIncrements('id'); $table->point('coordinate', $srid); $table->string('lat')->virtualAs('ST_X(coordinate)');
$table->string('lon')->virtualAs('ST_Y(coordinate)');
$table->timestamps();
});
As a coordinate always consists of two values (latitude and longitude), we need a point column. This type of column is a spatial field with an x and y value. Attentive readers may have noticed the first line:
$srid = env(‘SPATIAL_REF_ID’, 4326);If you’ve read my other article you will recognise the term SRID but if you haven’t let me explain it briefly.
A spatial reference system (SRS) or coordinate reference system (CRS) is a coordinate-based local, regional or global system used to locate geographical entities.
MySQL 8 allows you to define the ‘Spatial Reference ID’ per column. In use cases like store locators, you will almost always want to use WGS 84, which is identified by 4326. By using the correct reference system you will ensure that a coordinate in your database matches the same location as intended when projecting it on a map.
Looking at the actual column setup, you’ll also see I have added two virtual columns. Because the actual coordinate column contains binary data, it is hard to interpret in your application. So for ease of use, we add these virtual columns to directly access the latitude and longitude values.
$table->point('coordinate', $srid);$table->string('lat')->virtualAs('ST_X(coordinate)');
$table->string('lon')->virtualAs('ST_Y(coordinate)');
And that’s really all we need to set up the database.
Step 3: Data structure & handling
The coordinate is the most important data object in this demo application, and we want to ensure we can rely on it being correct. So let’s start by creating a simple DTO:
namespace App;
use Illuminate\Contracts\Support\Arrayable;
class Coordinate implements Arrayable
{
/**
* @var float
*/
private $lat;
/**
* @var float
*/
private $lon;
/**
* @param float $lat
* @param float $lon
*/
public function __construct(float $lat, float $lon)
{
$this->lat = $lat;
$this->lon = $lon;
}
/**
* @return float
*/
public function getLat(): float
{
return $this->lat;
}
/**
* @return float
*/
public function getLon(): float
{
return $this->lon;
}
/**
* Get the instance as an array.
*
* @return array
*/
public function toArray()
{
return [
'lat' => $this->getLat(),
'lon' => $this->getLon(),
];
}
}You will notice I always use floats for the two coordinate parts. This may seem obvious, but I’ve seen far too many examples of using strings (and consequently varchars in the database) to not point this out.
Markers Model
Next we’ll create a model for our markers table.
$ php artisan make:model MarkerWe’ll use the Coordinate DTO for setting and getting the coordinate from the model.
namespace App;
use Illuminate\Support\Facades\DB;
use Illuminate\Database\Eloquent\Model;
class Marker extends Model
{
/**
* @var array
*/
protected $fillable = [
'coordinate',
];
/**
* @param Coordinates $coordinates
*
* @return $this
*/
public function setCoordinate(Coordinate $coordinates): self
{
$srid = env('SPATIAL_REF_ID', 4326);
$lat = $coordinates->getLat();
$lon = $coordinates->getLon();
$this->attributes['coordinate'] = DB::raw("ST_GeomFromText('POINT($lat $lon)', $srid)");
return $this;
}
/**
* @return Coordinate
*/
public function getCoordinate(): Coordinate
{
return new Coordinate($this->lat, $this->lon);
}
}For this demo app, we’ll not create a repository or factory or similar, but of course some of this logic may be written very differently or in different places in a real life application.
Take note of the use of ST_GeomFromText in the setter. This is required to be able to set your coordinate. Because Laravel and Eloquent are awesome, it’s quite easy to hook into the saving model event and do this logic there.
If you have multiple models that use coordinates, you may even want to create an abstract class, trait, factory or some other higher order class for.
For now we will stick to a simple implementation.
Creating a marker
With the above two classes we can create our first marker:
$coordinate = new Coordinate(50.8815228, 5.7469309);$marker = (new Marker)
->setCoordinate($coordinate)
->save();
In the demo code I have shared on Github I have also added a name field and a setter for that field. But it’s optional.
Step 3b: Creating a random list of coordinates
A demo isn’t much of a one without some actual data to test it with. So we’re taking a small sidetrack here to create a random marker generator.
Why a generator and not a seeder? Because I want you to be able to create a list of markers that’s actual in an area around you, and not just around the Maatwebsite office. And because it’s fun!
To start, let’s generate a console command
$ php artisan make:command GenerateRandomLocationsThe command itself will use the two previous classes we created, and we will make use of Faker to fill in some random data.
Let’s take a look at the signature first, to show what the command can do:
/**
* The name and signature of the console command.
*
* @var string
*/
protected $signature = 'make:locations
{startLat=50.884408: Starting latitude}
{startLon=5.756073: Starting longitude}
{radius=50: Limiting radius from starting point in Km}
{amount=100: Amount of locations to generate}';We’re using the familiar make prefix. Then there are a few arguments to provide:
- startLat: The latitude of the coordinate that will be at the centre of the newly created list of coordinates.
- startLon: The longitude of that same centre coordinate.
- radius: This will determine the maximum distance of the generated coordinates, in kilometers.
- amount: The amount of locations to generate.
By default, without arguments, the command will generate 100 points in a 5km radius from the Maatwebsite office.
The “magic” happens in the handle method:
/**
* @param GeoCalculationService $service
*/
public function handle(GeoCalculationService $service)
{
$faker = Factory::create();
$startingPoint = $this->getStartingPoint();
for($i = 0; $i <= $this->getAmount(); $i++) {
$newCoordinate = $service->randomCoordinate($startingPoint, $this->getRadius());
(new Marker)
->setName($faker->name)
->setCoordinate($newCoordinate)
->save();
}
}We are using the Faker factory for generating names (remember I said they were optional before? If you don’t want names, just forget about this part).
Within a for loop (based on the chosen amount value) we will then use a service class to generate a random coordinate based on the provided arguments.
The service class
The complete service class will be provided within the demo app on Github. But for the purpose of this article I’d like to point out this small part:
/**
* @param Coordinate $coordinates
* @param int $maxDistance
*
* @return Coordinate
*/
public function randomCoordinate(Coordinate $coordinates, int $maxDistance): Coordinate
{
$heading = mt_rand(0, 359);
$distance = mt_rand(0, $maxDistance);
return $this->project($coordinates, $distance, $heading);
}To create a random coordinate we first determine a random heading (within the 360 degrees of a compass). Then we determine a random distance within the limit provided.
The project function, which I will not explain here (as it could almost merit an article in and of itself) will use a technique (surprisingly) called projection to calculate a coordinate on the heading and distance from the provided starting point.
Those of you who have dabbled with Geocaching may recognise it. It can also be done with a simple compass and walking, but for our purposes we don’t really have the time for that.
I’ve mentioned the haversine formula in my other article, and the formula to project a new coordinate is closely related to that one. It takes into account things like the radius of the earth and then does a whole lot of sin/cos/tan calculations that I barely understand myself. So I will not bother you with an explanation. The link above will allow you to test the projections we make to see if they are correct, if you’d like to make sure.
Step 4: Find locations
Ok, so now we are able to create random coordinates within a fixed radius, how should we go about finding the points?
We are skipping ahead a bit, because I haven’t explained how to get the input arguments we need for this, but let me first explain how we can find point within a known polygon.
/**
* @param array $mapBounds
* @param array|null $markerIds
*
* @return Builder
*/
public function findByBounds(array $mapBounds)
{
...
[ 'south' => $south,
'west' => $west,
'north' => $north,
'east' => $east
] = $mapBounds;
...
}The findByBounds methods expects an array of points that represent the two edges of the rectangle. We can get this info from Google Maps, and I’ll show you how later. For now let’s assume that array will always contain the following exact data and in this exact order:
- The southern most longitude of the rectangle.
- The western most latitude of the rectangle
- The northern most longitude of the recangle
- The eastern most latitude of the rectangle.
Let me use this picture I’ve graciously nicked from StackOverflow to illustrate what I mean:
Point C represents the South-West corner of a rectangle, where B represents the North-East corner.
When drawing the minimum bounding rectangle of these two points, you’d basically get this red rectangle.
So all we need for the full rectangle are just the two corners, and thankfully Google Maps provides these easily.
So if we take a look at the full findByBounds method, we can understand how these can fit in the query we need:
/**
* @param array $mapBounds
*
* @return Builder
*/
public function findByBounds(array $mapBounds)
{
$srid = env('SPATIAL_REF_ID', 4326);
[ 'south' => $south,
'west' => $west,
'north' => $north,
'east' => $east
] = $mapBounds;
$query = Marker::query()
->whereRaw("
ST_Contains(
ST_PolygonFromText('POLYGON(
($north $west, $north $east, $south $east, $south $west, $north $west)
)', {$srid}),
`coordinate`
)");
return $query;
}Of course we need the SRID value again, though normally we’d probably use a config value for it, or even inject it through a service provider. For now let’s just assume that’s what we did instead of a dirty copy-paste.
The query itself takes a bit of explanation, so let me break it down:
POLYGON(($north $west, $north $east, $south $east, $south $west, $north $west))First, in the innermost part of the query, we use the $mapBounds values we extracted to create our Polygon. If you’re wondering if you miscounted, yes we are using 4 distinct values to fill 10 values in the query. From the 2 coordinates we can easily determine the other 2 corners of the rectangle. That still leaves 2 values, which are basically the first 2 values repeated. You always need to “close” a polygon, so the starting point must always match the end point.
If we move up one step we get ST_PolygonFromText that simply converts our textual representation of a polygon into an actual geometry value.
One more step up and we get the absolutely brilliantST_Contains function.
The only input this function requires if a polygon within which it must search, and a coordinate to search for. The coordinate in this case is simply the column of our markers table. The polygon is the one we just created.
And that is all you need really. These methods combined will used the indexed spatial column to search within the generated geometry. The geometry itself is generated for each record, but the impact of that is negligible. However there are ways to generate it one time, then using the previously generated value to calculate the matching points.
The demo code also contains the possibility to add in a list of marker id’s to exclude from the search, because there is no need to fetch the same markers we’ve already fetched before. We’ll briefly come to that when I explain the frontend code.
Last part to actually fetch the data is the controller method, which is obviously quite small:
public function getLocations(MapRequest $request, LocationFinderService $finderService)
{
if(!$request->validated()) {
throw new InvalidArgumentException('Invalid map bounds');
}
$markers = $finderService->findByBounds(
$request->getMapBounds()
);
return MarkerResource::collection(
$markers->paginate(100)
);
}The MapRequest class simply guarantees the $mapBounds array we discussed earlier. Then all we need to do is provide that data to the LocationFinderService. If you were paying attention, you noticed the service returns a Builder instance which allows us to do stuff like pagination from the controller.
Step 5: Show me the mo… map!
We have most of the backend code ready now, so let’s move to the frontend.
Being a bit lazy, instead of creating a new view file, I’ve simply changed the default welcome.blade.php:
<!DOCTYPE html>
<html lang="{{ str_replace('_', '-', app()->getLocale()) }}">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<meta name="csrf-token" content="{{ csrf_token() }}">
<title>Polygonal location search demo</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
</head>
<body class="bg-light">
<div class="container">
<div class="py-5 text-center">
<h1>Polygonal map search demo</h1>
<p class="lead">
A demo of the power of MySQL 8 for searching Points within a Polygon for mapping purposes.
</p>
</div>
<div class="d-flex justify-content-center" id="app">
<demo-map :lat="50.884408" :lon="5.756073" :zoom="16"></demo-map>
</div>
</div>
<script src="{{ mix('js/app.js') }}" type="text/javascript"></script>
</body>
</html>Not much happening here except loading in the Bootstrap CDN code for an easy start without needing to write any CSS. Tailwind would have done the job as well of course. Just work with what you know or like best.
Being familiar with VueJS you’ll notice the main component here is demo-map.
We pass on two props to the component that represent the centre of the map.
For your purposes you probably want to change this to whatever your own location is.
NB: If you are wondering why I use dynamic props here (using the short version of v-bind), the reason is that we’re passing floats. If I did not do this, the resulting props in the component would become strings.
The rest of the template is nothing more than a bit of basing and the inclusion of the actual application JavaScript code.
The DemoMap component
Which brings us to the demo-map itself:
<template>
<div>
<div class="google-map" id="map"></div>
</div>
</template>It starts with a very easy template that’s nothing more than a Google Maps div. And we’re using a small bit of custom styling, because Google Maps doesn’t really like dynamic sizing:
<style>
#map {
margin: 0 auto;
height: 600px;
width: 800px;
}
</style>But most of this is window dressing. The actual fun happens here:
import GoogleMaps from "../GoogleMaps";
import debounce from "tiny-debounce";
import axios from "axios";
export default {
props: {
zoom: {
type: Number,
default: 8
},
lat: {
type: Number
},
lon: {
type: Number
}
},
data() {
return {
googleMapsKey: process.env.MIX_GOOGLE_MAPS_KEY,
loaded: false,
map: null,
markers: {},
markerIds: {},
page: 1,
search: null
}
},
mounted() {
this.load();
},
methods: {
fetchMarkers(postData) {
return axios.post('find-markers');
},
fetchMarkersInBounds(mapBounds) {
let that = this;
this.page = 1;
this.properties = [];
this.fetchMarkers({
'north' : mapBounds.north,
'south' : mapBounds.south,
'east' : mapBounds.east,
'west' : mapBounds.west,
})
.then(response => {
let {data: {data: markers, links}} = response;
if (links.prev === null) {
that.page = 1;
}
if (markers.length > 0) {
that.page += 1;
that.loadMarkers(markers);
}
that.links = links;
if (typeof that.links.next !== 'undefined' && that.links.next !== null) {
that.fetchMarkersInBounds(mapBounds);
}
});
},
async load() {
this.loaded = true;
await GoogleMaps.load(this.googleMapsKey, "EN", 'en');
this.initMap();
},
loadMarkers(markers) {
for (let i = 0; i < markers.length; i++) {
let marker = markers[i];
this.markerIds[marker.id] = true;
if(marker.hasOwnProperty('coordinates') && !this.markers.hasOwnProperty(marker.id)) {
this.createMarker(marker);
}
}
},
createMarker(item) {
let position = new google.maps.LatLng(item.coordinates.lat, item.coordinates.lon);
this.markers[item.id] = new google.maps.Marker({
position: position,
map: this.map
});
},
initMap() {
this.map = new google.maps.Map(document.getElementById('map'), {
zoom : this.zoom,
center: new google.maps.LatLng(this.lat, this.lon),
streetViewControl: false,
mapTypeControl: false,
fullscreenControl: false
});
this.map.addListener('bounds_changed', () => {
this.boundsChanged();
});
},
boundsChanged: debounce(function() {
this.fetchMarkersInBounds(this.map.getBounds().toJSON());
}, 500),
}
};I will break this down in it’s constituent components.
async load() {
await GoogleMaps.load(this.googleMapsKey, "EN", 'en');this.loaded = true;
this.initMap();
},
I have a small GoogleMaps helper class created by Patrick Brouwers. It basically loads the GoogleMaps library within a Promise so we can load it when needed. It’s included in the demo code, but I won’t go into it here.
The load function is called once, through the mounted hook. initMap then loads the actual map.
initMap() {
this.map = new google.maps.Map(document.getElementById('map'), {
zoom : this.zoom,
center: new google.maps.LatLng(this.lat, this.lon),
streetViewControl: false,
mapTypeControl: false,
fullscreenControl: false
}); this.map.addListener('bounds_changed', () => {
this.boundsChanged();
});
}
Most of this code is vanilla JavaScript or course, because that’s how the maps library works. But if you use Maps a lot, you may want to use or make a dedicated component for that. There are a few on Github.
We initialize an instance of google.maps.Map and bind it to our component instance. The lat and lon props we provide from the main blade file are used to centre the map. The zoom can also be set through the props, but it has a default value of 16.
Then we add a listener to the bounds_changed event. Google Maps will trigger this event whenever the visible bounds are changed. This happens when the map is loaded, the size is changed or when you scroll or zoom the map.
When that happens we trigger our won boundsChanged method:
boundsChanged: debounce(function() {
this.fetchMarkersInBounds(this.map.getBounds().toJSON());
}, 500),This will fetch the bounds from the map instance and convert it into JSON to use in our fetchMarkersInBounds method.
fetchMarkersInBounds(mapBounds) {
let that = this;
this.page = 1;
this.properties = []; this.fetchMarkers({
'north' : mapBounds.north,
'south' : mapBounds.south,
'east' : mapBounds.east,
'west' : mapBounds.west,
})
.then(response => {
let {data: {data: markers, links}} = response; if (links.prev === null) {
that.page = 1;
} if (markers.length > 0) {
that.page += 1;
that.loadMarkers(markers);
} that.links = links; if (typeof that.links.next !== 'undefined' && that.links.next !== null) {
that.fetchMarkersInBounds(mapBounds);
}
});
},
This takes the mapBounds object we just fetched from the map instance and passes it through the fetchMarkers method. Then when the promise of that method finishes it will add the markers to the component instance and set the links, and page accordingly (as you have seen before we are using pagination).
fetchMarkers(postData) {
if(this.markerIds) {
postData.markerIds = Object.keys(this.markerIds);
} return axios.post('find-markers', postData);
},
The fetchMarkers method simply adds the already existing markers to the postData and then uses axios to request a new list of markers from the api endpoint we created earlier.
loadMarkers(markers) {
for (let i = 0; i < markers.length; i++) {
let marker = markers[i]; this.markerIds[marker.id] = true; if(marker.hasOwnProperty('coordinates') && !this.markers.hasOwnProperty(marker.id)) {
this.createMarker(marker);
}
}
},
The loadMarkers method loops over the markers received from the API, then adds the id’s to a list (which we use in fetchMarkers to make sure we don’t receive the same marker twice). It then checks if the marker has coordinates, doesn’t already exist, and if both of these statements are true, it will create the marker on the map.
createMarker(item) {
let position = new google.maps.LatLng(item.coordinates.lat, item.coordinates.lon); this.markers[item.id] = new google.maps.Marker({
position: position,
map: this.map
});
},
Finally we’ve reached the point where the marker will be shown on the map itself. We bind the created marker to this.markers to keep a reference to all markers in case you need to access them later of need to remove them from the map.
A few side notes here:
- The actual demo code also contains a method to do a search by location which will centre the map on the location it finds, and because that changes the map bounds, it will also trigger an API call to find markers in that area.
- I’ve used tiny-debounce in the boundsChanged method to minimize the amount of calls to the API. You can also use the lodash version or any other similar method. Or even write a small
setTimeoutimplementation. - I’m assuming you know how the Google Maps API works, so I haven’t explained things like
new google.maps.Markerornew google.maps.LatLng - We’re passing the maps api key from the
.envfile viaprocess.env.MIX_GOOGLE_MAPS_KEY. This is basic Laravel+Vue+Mix magic. Not something I had anything to do with.
The end result
All of these steps combined have created something similar to this:
Take a look at the demo project on Github for the full code and some extra’s. And feel free to contact me for questions on any details.
Written by Jochem Fuchs, Software engineer at Maatwebsite
Do you need help working with geolocation in your app or do you need help building a Laravel application? At Maatwebsite, we are there to help you on a commercial basis. Contact us via info@maatwebsite.nl or via phone +31 (0)10 744 9312 to discuss the possibilities.
