<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Jochem Fuchs on Medium]]></title>
        <description><![CDATA[Stories by Jochem Fuchs on Medium]]></description>
        <link>https://medium.com/@jochemfuchs?source=rss-bceaa88877ba------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*WfCLWRH0QKNp6xW4gJGkaA.jpeg</url>
            <title>Stories by Jochem Fuchs on Medium</title>
            <link>https://medium.com/@jochemfuchs?source=rss-bceaa88877ba------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 16:02:18 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@jochemfuchs/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[MySQL 8 Polygonal map search]]></title>
            <link>https://medium.com/spartner/mysql-8-polygonal-map-search-f3caeb614ffd?source=rss-bceaa88877ba------2</link>
            <guid isPermaLink="false">https://medium.com/p/f3caeb614ffd</guid>
            <category><![CDATA[laravel]]></category>
            <category><![CDATA[vuejs]]></category>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[geolocation]]></category>
            <dc:creator><![CDATA[Jochem Fuchs]]></dc:creator>
            <pubDate>Wed, 14 Aug 2019 13:58:33 GMT</pubDate>
            <atom:updated>2019-08-14T14:18:33.123Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2v1F-Q7wVnj8Lrm-MGnpMA.jpeg" /></figure><p><em>A tutorial for building easy locator maps</em></p><h3>Welcome back!</h3><p>Last week I published an article about the powerful combo <a href="https://medium.com/p/mysql-8-and-maps-a-power-combo-cb33f62168ef">MySQL 8 and Maps</a> 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.</p><p>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.</p><p>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:</p><ul><li>You are able to create a new Laravel +Vue application from scratch.</li><li>You understand Docker enough to run it, or are able to set up your own environment to run this application.</li><li>You have a decent understanding of MySQL.</li><li>You are able to create and run a database migration through Laravel.</li><li>You’ve worked with the Google Maps JavaScript API before or have a basic understanding of it.</li><li>You have some idea what I mean by geolocation and what a coordinate is.</li><li>(optional) You’ve read my article about <a href="https://medium.com/maatwebsite/the-best-way-to-locate-in-mysql-8-e47a59892443">MySQL 8 and Geolocation</a></li></ul><p>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.</p><blockquote><em>This is not meant to be copy-pasted into a real world project without some significant changes!</em></blockquote><p>So without further ado, let’s get started!</p><h3>Step 1: What do we need?</h3><p>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.<br>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.</p><p><strong>Tools required:</strong></p><ul><li>Laravel 5.8</li><li>Docker (Unless you set up your own environment)</li><li>VueJS 2</li><li>Google Maps JavaScript API</li></ul><h3>Step 2: Database</h3><p>For this application we really only need one table. Let’s call it markers and set up a migration for it.</p><pre>$ php artisan make:migration create_markers</pre><p>The migration itself is very small. Just a primary key and a coordinate column.</p><pre>Schema::<strong>create</strong>(&#39;markers&#39;, function (Blueprint $table) {<br>    $srid = env(&#39;SPATIAL_REF_ID&#39;, 4326);<br><br>    $table-&gt;bigIncrements(&#39;id&#39;);</pre><pre>    $table-&gt;point(&#39;coordinate&#39;, $srid);</pre><pre>    $table-&gt;string(&#39;lat&#39;)-&gt;<strong>virtualAs</strong>(&#39;ST_X(coordinate)&#39;);<br>    $table-&gt;string(&#39;lon&#39;)-&gt;<strong>virtualAs</strong>(&#39;ST_Y(coordinate)&#39;);<br><br>    $table-&gt;timestamps();<br>});</pre><p>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:</p><pre>$srid = env(‘SPATIAL_REF_ID’, 4326);</pre><p>If you’ve read my other article you will recognise the term SRID but if you haven’t let me explain it briefly.</p><blockquote>A <strong>spatial reference system</strong> (<strong>SRS</strong>) or <strong>coordinate reference system</strong> (<strong>CRS</strong>) is a <a href="https://en.wikipedia.org/wiki/Coordinate_system">coordinate-based</a> local, regional or global system used to locate geographical entities.</blockquote><p>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 <a href="https://en.wikipedia.org/wiki/World_Geodetic_System">WGS 84</a>, 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.</p><p>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.</p><pre>$table-&gt;point(&#39;coordinate&#39;, $srid);</pre><pre>$table-&gt;string(&#39;lat&#39;)-&gt;<strong>virtualAs</strong>(&#39;ST_X(coordinate)&#39;);<br>$table-&gt;string(&#39;lon&#39;)-&gt;<strong>virtualAs</strong>(&#39;ST_Y(coordinate)&#39;);</pre><p>And that’s really all we need to set up the database.</p><h3>Step 3: Data structure &amp; handling</h3><p>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:</p><pre>namespace App;<br><br>use Illuminate\Contracts\Support\Arrayable;<br><br>class Coordinate implements Arrayable<br>{<br>    <em>/**<br>     * </em><strong><em>@var </em></strong><em>float<br>     */<br>    </em>private $lat;<br><br>    <em>/**<br>     * </em><strong><em>@var </em></strong><em>float<br>     */<br>    </em>private $lon;<br><br>    <em>/**<br>     * </em><strong><em>@param </em></strong><em>float $lat<br>     * </em><strong><em>@param </em></strong><em>float $lon<br>     */<br>    </em>public function __construct(float $lat, float $lon)<br>    {<br>        $this-&gt;lat = $lat;<br>        $this-&gt;lon = $lon;<br>    }<br><br>    <em>/**<br>     * </em><strong><em>@return </em></strong><em>float<br>     */<br>    </em>public function getLat(): float<br>    {<br>        return $this-&gt;lat;<br>    }<br><br>    <em>/**<br>     * </em><strong><em>@return </em></strong><em>float<br>     */<br>    </em>public function getLon(): float<br>    {<br>        return $this-&gt;lon;<br>    }<br><br>    <em>/**<br>     * Get the instance as an array.<br>     *<br>     * </em><strong><em>@return </em></strong><em>array<br>     */<br>    </em>public function toArray()<br>    {<br>        return [<br>            &#39;lat&#39; =&gt; $this-&gt;getLat(),<br>            &#39;lon&#39; =&gt; $this-&gt;getLon(),<br>        ];<br>    }<br>}</pre><p>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.</p><h4>Markers Model</h4><p>Next we’ll create a model for our markers table.</p><pre>$ php artisan make:model Marker</pre><p>We’ll use the Coordinate DTO for setting and getting the coordinate from the model.</p><pre>namespace App;<br><br>use Illuminate\Support\Facades\DB;<br>use Illuminate\Database\Eloquent\Model;<br><br>class Marker extends Model<br>{<br>    <em>/**<br>     * </em><strong><em>@var </em></strong><em>array<br>     */<br>    </em>protected $fillable = [<br>        &#39;coordinate&#39;,<br>    ];<br><br>    <em>/**<br>     * </em><strong><em>@param </em></strong><em>Coordinates $coordinates<br>     *<br>     * </em><strong><em>@return </em></strong><em>$this<br>     */<br>    </em>public function setCoordinate(Coordinate $coordinates): self<br>    {<br>        $srid = env(&#39;SPATIAL_REF_ID&#39;, 4326);<br>        $lat  = $coordinates-&gt;getLat();<br>        $lon  = $coordinates-&gt;getLon();<br><br>        $this-&gt;attributes[&#39;coordinate&#39;] = DB::<strong>raw</strong>(&quot;ST_GeomFromText(&#39;POINT($lat $lon)&#39;, $srid)&quot;);<br><br>        return $this;<br>    }<br><br>    <em>/**<br>     * </em><strong><em>@return </em></strong><em>Coordinate<br>     */<br>    </em>public function getCoordinate(): Coordinate<br>    {<br>        return new Coordinate($this-&gt;lat, $this-&gt;lon);<br>    }<br>}</pre><p>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.</p><p>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. <br>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.</p><p>For now we will stick to a simple implementation.</p><h4>Creating a marker</h4><p>With the above two classes we can create our first marker:</p><pre>$coordinate = new Coordinate(50.8815228, 5.7469309);</pre><pre>$marker     = (new Marker)<br>                -&gt;setCoordinate($coordinate)<br>                -&gt;save();</pre><p>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.</p><h3>Step 3b: Creating a random list of coordinates</h3><p>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.</p><p>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!</p><p>To start, let’s generate a console command</p><pre>$ php artisan make:command GenerateRandomLocations</pre><p>The command itself will use the two previous classes we created, and we will make use of Faker to fill in some random data.</p><p>Let’s take a look at the signature first, to show what the command can do:</p><pre><em>/**<br> * The name and signature of the console command.<br> *<br> * </em><strong><em>@var </em></strong><em>string<br> */<br></em>protected $signature = &#39;make:locations <br>                            {startLat=50.884408: Starting latitude} <br>                            {startLon=5.756073: Starting longitude} <br>                            {radius=50: Limiting radius from starting point in Km} <br>                            {amount=100: Amount of locations to generate}&#39;;</pre><p>We’re using the familiar make prefix. Then there are a few arguments to provide:</p><ul><li>startLat: The latitude of the coordinate that will be at the centre of the newly created list of coordinates.</li><li>startLon: The longitude of that same centre coordinate.</li><li>radius: This will determine the maximum distance of the generated coordinates, in kilometers.</li><li>amount: The amount of locations to generate.</li></ul><p>By default, without arguments, the command will generate 100 points in a 5km radius from the Maatwebsite office.</p><p>The “magic” happens in the handle method:</p><pre><em>/**<br> * </em><strong><em>@param </em></strong><em>GeoCalculationService $service<br> */<br></em>public function handle(GeoCalculationService $service)<br>{<br>    $faker         = Factory::<em>create</em>();<br>    $startingPoint = $this-&gt;getStartingPoint();<br><br>    for($i = 0; $i &lt;= $this-&gt;getAmount(); $i++) {<br>        $newCoordinate = $service-&gt;randomCoordinate($startingPoint, $this-&gt;getRadius());<br><br>        (new Marker)<br>            -&gt;setName($faker-&gt;<strong>name</strong>)<br>            -&gt;setCoordinate($newCoordinate)<br>            -&gt;save();<br>    }<br>}</pre><p>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).</p><p>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.</p><h4>The service class</h4><p>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:</p><pre><em>/**<br> * </em><strong><em>@param </em></strong><em>Coordinate $coordinates<br> * </em><strong><em>@param </em></strong><em>int         $maxDistance<br> *<br> * </em><strong><em>@return </em></strong><em>Coordinate<br> */<br></em>public function randomCoordinate(Coordinate $coordinates, int $maxDistance): Coordinate<br>{<br>    $heading  = mt_rand(0, 359);<br>    $distance = mt_rand(0, $maxDistance);<br><br>    return $this-&gt;project($coordinates, $distance, $heading);<br>}</pre><p>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.</p><p>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 <a href="https://www.geocachingtoolbox.com/index.php?lang=en&amp;page=coordinateProjection">projection</a> to calculate a coordinate on the heading and distance from the provided starting point. <br>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.</p><p>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.</p><h3>Step 4: Find locations</h3><p>Ok, so now we are able to create random coordinates within a fixed radius, how should we go about finding the points?</p><p>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.</p><pre><em>/**<br> * </em><strong><em>@param </em></strong><em>array $mapBounds<br> * </em><strong><em>@param </em></strong><em>array|null $markerIds<br> *<br> * </em><strong><em>@return </em></strong><em>Builder<br> */<br></em>public function findByBounds(array $mapBounds)<br>{<br>    ...<br><br>    [   &#39;south&#39; =&gt; $south,<br>        &#39;west&#39;  =&gt; $west,<br>        &#39;north&#39; =&gt; $north,<br>        &#39;east&#39;  =&gt; $east<br>    ] = $mapBounds;<br><br>   ...<br>}</pre><p>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:</p><ul><li>The southern most longitude of the rectangle.</li><li>The western most latitude of the rectangle</li><li>The northern most longitude of the recangle</li><li>The eastern most latitude of the rectangle.</li></ul><p>Let me use this picture I’ve graciously nicked from StackOverflow to illustrate what I mean:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/432/0*JtMYBVexb1_NHmW3.png" /></figure><p>Point C represents the South-West corner of a rectangle, where B represents the North-East corner. <br>When drawing the <a href="https://en.wikipedia.org/wiki/Minimum_bounding_rectangle">minimum bounding rectangle</a> of these two points, you’d basically get this red rectangle.<br>So all we need for the full rectangle are just the two corners, and thankfully Google Maps provides these easily.</p><p>So if we take a look at the full findByBounds method, we can understand how these can fit in the query we need:</p><pre><em>/**<br> * </em><strong><em>@param </em></strong><em>array $mapBounds<br> *<br> * </em><strong><em>@return </em></strong><em>Builder<br> */<br></em>public function findByBounds(array $mapBounds)<br>{<br>    $srid = env(&#39;SPATIAL_REF_ID&#39;, 4326);<br><br>    [   &#39;south&#39; =&gt; $south,<br>        &#39;west&#39;  =&gt; $west,<br>        &#39;north&#39; =&gt; $north,<br>        &#39;east&#39;  =&gt; $east<br>    ] = $mapBounds;<br><br>    $query = Marker::<em>query</em>()<br>        -&gt;whereRaw(&quot;<br>        ST_Contains(<br>            ST_PolygonFromText(&#39;POLYGON(<br>                   ($north $west, $north $east, $south $east, $south $west, $north $west)<br>                )&#39;, {$srid}), <br>            `coordinate` <br>        )&quot;);<br><br>    return $query;<br>}</pre><p>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.</p><p>The query itself takes a bit of explanation, so let me break it down:</p><pre>POLYGON(($north $west, $north $east, $south $east, $south $west, $north $west))</pre><p>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.</p><p>If we move up one step we get ST_PolygonFromText that simply converts our textual representation of a polygon into an actual geometry value.</p><p>One more step up and we get the absolutely brilliantST_Contains function. <br>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.</p><p>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.</p><p>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.</p><p>Last part to actually fetch the data is the controller method, which is obviously quite small:</p><pre>public function getLocations(MapRequest $request, LocationFinderService $finderService)<br>{<br>    if(!$request-&gt;validated()) {<br>        throw new InvalidArgumentException(&#39;Invalid map bounds&#39;);<br>    }<br><br>    $markers = $finderService-&gt;findByBounds(<br>        $request-&gt;getMapBounds()<br>    );<br><br>    return MarkerResource::<em>collection</em>(<br>        $markers-&gt;paginate(100)<br>    );<br>}</pre><p>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.</p><h3>Step 5: Show me the mo… map!</h3><p>We have most of the backend code ready now, so let’s move to the frontend.</p><p>Being a bit lazy, instead of creating a new view file, I’ve simply changed the default welcome.blade.php:</p><pre>&lt;!DOCTYPE html&gt;<br>&lt;html lang=&quot;{{ str_replace(&#39;_&#39;, &#39;-&#39;, app()-&gt;getLocale()) }}&quot;&gt;<br>    &lt;head&gt;<br>        &lt;meta charset=&quot;utf-8&quot;&gt;<br>        &lt;meta name=&quot;viewport&quot; content=&quot;width=device-width, initial-scale=1&quot;&gt;<br>        &lt;meta name=&quot;csrf-token&quot; content=&quot;{{ csrf_token() }}&quot;&gt;<br>        &lt;title&gt;Polygonal location search demo&lt;/title&gt;<br>        &lt;link rel=&quot;stylesheet&quot; href=&quot;https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css&quot; integrity=&quot;sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T&quot; crossorigin=&quot;anonymous&quot;&gt;<br>    &lt;/head&gt;<br>    &lt;body class=&quot;bg-light&quot;&gt;<br>        &lt;div class=&quot;container&quot;&gt;<br>            &lt;div class=&quot;py-5 text-center&quot;&gt;<br>                &lt;h1&gt;Polygonal map search demo&lt;/h1&gt;<br>                &lt;p class=&quot;lead&quot;&gt;<br>                    A demo of the power of MySQL 8 for searching Points within a Polygon for mapping purposes.<br>                &lt;/p&gt;<br>            &lt;/div&gt;<br>            &lt;div class=&quot;d-flex justify-content-center&quot; id=&quot;app&quot;&gt;<br>                &lt;demo-map :lat=&quot;50.884408&quot; :lon=&quot;5.756073&quot; :zoom=&quot;16&quot;&gt;&lt;/demo-map&gt;<br>            &lt;/div&gt;<br>        &lt;/div&gt;<br>        &lt;script src=&quot;{{ mix(&#39;js/app.js&#39;) }}&quot; type=&quot;text/javascript&quot;&gt;&lt;/script&gt;<br>    &lt;/body&gt;<br>&lt;/html&gt;</pre><p>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.</p><p>Being familiar with VueJS you’ll notice the main component here is demo-map.<br>We pass on two props to the component that represent the centre of the map. <br>For your purposes you probably want to change this to whatever your own location is.</p><p><strong>NB</strong>: If you are wondering why I use <a href="https://vuejs.org/v2/guide/components-props.html#Passing-Static-or-Dynamic-Props">dynamic props here</a> (using the short version of <a href="https://vuejs.org/v2/api/#v-bind">v-bind</a>), the reason is that we’re passing floats. If I did not do this, the resulting props in the component would become strings.</p><p>The rest of the template is nothing more than a bit of basing and the inclusion of the actual application JavaScript code.</p><h4>The DemoMap component</h4><p>Which brings us to the demo-map itself:</p><pre>&lt;template&gt;<br>    &lt;div&gt;<br>        &lt;div class=&quot;google-map&quot; id=&quot;map&quot;&gt;&lt;/div&gt;<br>    &lt;/div&gt;<br>&lt;/template&gt;</pre><p>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:</p><pre>&lt;style&gt;<br>    #map {<br>        margin: 0 auto;<br>        height: 600px;<br>        width: 800px;<br>    }<br>&lt;/style&gt;</pre><p>But most of this is window dressing. The actual fun happens here:</p><pre>import GoogleMaps from &quot;../GoogleMaps&quot;;<br>import debounce from &quot;tiny-debounce&quot;;<br>import axios from &quot;axios&quot;;<br><br>export default {<br>    props: {<br>        zoom: {<br>            type: <strong><em>Number</em></strong>,<br>            default: 8<br>        },<br>        lat: {<br>            type: <strong><em>Number<br>        </em></strong>},<br>        lon: {<br>            type: <strong><em>Number<br>        </em></strong>}<br>    },<br>    data() {<br>        return {<br>            googleMapsKey: <strong><em>process</em></strong>.env.MIX_GOOGLE_MAPS_KEY,<br>            loaded: false,<br>            map: null,<br>            markers: {},<br>            markerIds: {},<br>            page: 1,<br>            search: null<br>        }<br>    },<br>    mounted() {<br>        this.load();<br>    },<br>    methods: {<br>        fetchMarkers(postData) {<br>            return axios.post(&#39;find-markers&#39;);<br>        },<br>        fetchMarkersInBounds(mapBounds) {<br>            let that        = this;<br>            this.page       = 1;<br>            this.properties = [];<br><br>            this.fetchMarkers({<br>                &#39;north&#39; : mapBounds.north,<br>                &#39;south&#39; : mapBounds.south,<br>                &#39;east&#39;  : mapBounds.east,<br>                &#39;west&#39;  : mapBounds.west,<br>            })<br>            .then(response =&gt; {<br>                let {data: {data: markers, links}} = response;<br><br>                if (links.prev === null) {<br>                    that.page = 1;<br>                }<br><br>                if (markers.length &gt; 0) {<br>                    that.page += 1;<br>                    that.loadMarkers(markers);<br>                }<br><br>                that.links = links;<br><br>                if (typeof that.links.next !== &#39;undefined&#39; &amp;&amp; that.links.next !== null) {<br>                    that.fetchMarkersInBounds(mapBounds);<br>                }<br>            });<br>        },<br>        async load() {<br>            this.loaded = true;<br><br>            await GoogleMaps.<em>load</em>(this.googleMapsKey, &quot;EN&quot;, &#39;en&#39;);<br><br>            this.initMap();<br>        },<br>        loadMarkers(markers) {<br>            for (let i = 0; i &lt; markers.length; i++) {<br>                let marker = markers[i];<br><br>                this.markerIds[marker.id] = true;<br><br>                if(marker.hasOwnProperty(&#39;coordinates&#39;) &amp;&amp; !this.markers.hasOwnProperty(marker.id)) {<br>                    this.createMarker(marker);<br>                }<br>            }<br>        },<br>        createMarker(item) {<br>            let position = new google.maps.LatLng(item.coordinates.lat, item.coordinates.lon);<br><br>            this.markers[item.id] = new google.maps.Marker({<br>                position: position,<br>                map: this.map<br>            });<br>        },<br>        initMap() {<br>            this.map = new google.maps.Map(<strong><em>document</em></strong>.getElementById(&#39;map&#39;), {<br>                zoom  : this.zoom,<br>                center: new google.maps.LatLng(this.lat, this.lon),<br>                streetViewControl: false,<br>                mapTypeControl: false,<br>                fullscreenControl: false<br>            });<br><br>            this.map.addListener(&#39;bounds_changed&#39;, () =&gt; {<br>                this.boundsChanged();<br>            });<br>        },<br>        boundsChanged: debounce(function() {<br>            this.fetchMarkersInBounds(this.map.getBounds().toJSON());<br>        }, 500),<br>    }<br>};</pre><p>I will break this down in it’s constituent components.</p><pre>async load() {<br>    await GoogleMaps.<em>load</em>(this.googleMapsKey, &quot;EN&quot;, &#39;en&#39;);</pre><pre>this.loaded = true;<br>    this.initMap();<br>},</pre><p>I have a small GoogleMaps helper class created by <a href="https://medium.com/u/60b58299a4">Patrick Brouwers</a>. 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.</p><p>The load function is called once, through the mounted hook. initMap then loads the actual map.</p><pre>initMap() {<br>    this.map = new google.maps.Map(<strong><em>document</em></strong>.getElementById(&#39;map&#39;), {<br>        zoom  : this.zoom,<br>        center: new google.maps.LatLng(this.lat, this.lon),<br>        streetViewControl: false,<br>        mapTypeControl: false,<br>        fullscreenControl: false<br>    });</pre><pre>    this.map.addListener(&#39;bounds_changed&#39;, () =&gt; {<br>        this.boundsChanged();<br>    });<br>}</pre><p>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.</p><p>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.</p><p>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. <br>When that happens we trigger our won boundsChanged method:</p><pre>boundsChanged: debounce(function() {<br>    this.fetchMarkersInBounds(this.map.getBounds().toJSON());<br>}, 500),</pre><p>This will fetch the bounds from the map instance and convert it into JSON to use in our fetchMarkersInBounds method.</p><pre>fetchMarkersInBounds(mapBounds) {<br>    let that        = this;<br>    this.page       = 1;<br>    this.properties = [];</pre><pre>    this.fetchMarkers({<br>        &#39;north&#39; : mapBounds.north,<br>        &#39;south&#39; : mapBounds.south,<br>        &#39;east&#39;  : mapBounds.east,<br>        &#39;west&#39;  : mapBounds.west,<br>    })<br>    .then(response =&gt; {<br>        let {data: {data: markers, links}} = response;</pre><pre>        if (links.prev === null) {<br>            that.page = 1;<br>        }</pre><pre>        if (markers.length &gt; 0) {<br>            that.page += 1;<br>            that.loadMarkers(markers);<br>        }</pre><pre>        that.links = links;</pre><pre>        if (typeof that.links.next !== &#39;undefined&#39; &amp;&amp; that.links.next !== null) {<br>            that.fetchMarkersInBounds(mapBounds);<br>        }<br>    });<br>},</pre><p>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).</p><pre>fetchMarkers(postData) {<br>    if(this.markerIds) {<br>        postData.markerIds = <strong><em>Object</em></strong>.keys(this.markerIds);<br>    }</pre><pre>    return axios.post(&#39;find-markers&#39;, postData);<br>},</pre><p>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.</p><pre>loadMarkers(markers) {<br>    for (let i = 0; i &lt; markers.length; i++) {<br>        let marker = markers[i];</pre><pre>        this.markerIds[marker.id] = true;</pre><pre>        if(marker.hasOwnProperty(&#39;coordinates&#39;) &amp;&amp; !this.markers.hasOwnProperty(marker.id)) {<br>            this.createMarker(marker);<br>        }<br>    }<br>},</pre><p>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.</p><pre>createMarker(item) {<br>    let position = new google.maps.LatLng(item.coordinates.lat, item.coordinates.lon);</pre><pre>    this.markers[item.id] = new google.maps.Marker({<br>        position: position,<br>        map: this.map<br>    });<br>},</pre><p>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.</p><p>A few side notes here:</p><ul><li>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.</li><li>I’ve used <a href="https://github.com/vuejs-tips/tiny-debounce">tiny-debounce</a> 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 setTimeout implementation.</li><li>I’m assuming you know how the Google Maps API works, so I haven’t explained things like new google.maps.Marker or new google.maps.LatLng</li><li>We’re passing the maps api key from the .env file via process.env.MIX_GOOGLE_MAPS_KEY. This is basic Laravel+Vue+Mix magic. Not something I had anything to do with.</li></ul><h3>The end result</h3><p>All of these steps combined have created something similar to this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/836/1*2E7iDPkj1TSpCLY9GMC1TQ.png" /></figure><p>Take a look at the demo project on <a href="https://github.com/jochemfuchs/mysql8-polygonal-geolocator">Github</a> for the full code and some extra’s. And feel free to contact me for questions on any details.</p><p>Written by <a href="https://twitter.com/jochemfuchs">Jochem Fuchs</a>, Software engineer at <a href="https://www.maatwebsite.nl/">Maatwebsite</a></p><p>Do you need help working with geolocation in your app or do you need help building a Laravel application? At <a href="https://www.maatwebsite.nl/?source=post_page---------------------------">Maatwebsite</a>, we are there to help you on a commercial basis. Contact us via <a href="/service/mailto:info@maatwebsite.nl?source=post_page---------------------------">info@maatwebsite.nl </a>or via phone +31 (0)10 744 9312 to discuss the possibilities.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f3caeb614ffd" width="1" height="1" alt=""><hr><p><a href="https://medium.com/spartner/mysql-8-polygonal-map-search-f3caeb614ffd">MySQL 8 Polygonal map search</a> was originally published in <a href="https://medium.com/spartner">Spartner</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[MySQL 8 and maps. A power combo?]]></title>
            <link>https://medium.com/spartner/mysql-8-and-maps-a-power-combo-cb33f62168ef?source=rss-bceaa88877ba------2</link>
            <guid isPermaLink="false">https://medium.com/p/cb33f62168ef</guid>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[geolocation]]></category>
            <dc:creator><![CDATA[Jochem Fuchs]]></dc:creator>
            <pubDate>Wed, 07 Aug 2019 12:01:01 GMT</pubDate>
            <atom:updated>2019-08-07T12:01:01.152Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*q0B8zToJQvZPJ9bA-a2DNQ.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/@dead____artist?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Capturing the human heart.</a> on <a href="https://unsplash.com/?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>Some time ago I wrote a blog about “<a href="https://medium.com/maatwebsite/the-best-way-to-locate-in-mysql-8-e47a59892443">The best way to locate, in MySQL 8</a>”. In that article I promised to follow up with some metrics to show how MySQL 8 performs. I’ve been dragging my feet, for the simple reason that I don’t believe it would be a very interesting read. Feel free to leave a comment if you think otherwise!</p><p>Instead, let’s have some fun with a practical example we’re going to build together!</p><h3>What are we making?</h3><p>You’ve probably all seen them and have used them many times with varying results. Whether you’re looking for stores, real estate or a place to stay, they all work on the same principle. You’re looking for location based items, and you want to be able to scroll through the map to find more.</p><p>In <a href="https://www.funda.nl/en/kaart/koop/meerssen/">some cases</a> these maps are lightning fast, but in <a href="https://www.airbnb.nl/s/Meerssen--Netherlands/homes">many cases</a> it actually takes a few seconds to load the results on the map.</p><p>We’ll first take a look into why that is, then we’ll discuss some often used optimisations to mitigate that, and finally I’ll guide you through building a solution that is fast without any of those optimisations.</p><h3>So why is the traditional approach slow?</h3><p>In a database without (or with limited) spatial functions and without using optimisation or caching techniques, you would have to calculate the distance of each point in your database from the centre of the currently visible map. Scrolling the map would restart that entire process each time.</p><p>If you’ve read my previous article, you know I mentioned the <a href="https://en.wikipedia.org/wiki/Haversine_formula">Haversine formula</a>. It’s this formula that is calculated for EACH and EVERY point in your database, at EVERY request. We’re disregarding caching for now, as this does not solve the issue, just mitigates the resulting slowness to some extent.</p><p>Let’s have a quick look into how this would look:</p><pre>SELECT *<br> ,(6371 * Acos(Cos(Radians(map_center_latitude)) * Cos(Radians(lat)) * Cos(Radians(lng) - Radians(map_center_longitude)) + Sin(Radians(map_center_latitude)) * Sin(Radians(lat)))) AS distance<br>FROM locations<br>HAVING distance &lt; 25<br>ORDER BY distance;</pre><p>Now apart from the actual formula being very hard to read, you can quickly see the issue here. For each row in the database it would need to calculate the distance to the map_center. Then the results of that calculation are sorted and those that match the HAVING clause are returned.</p><p>Even with indexing of lat and lon you’d still see the database scanning all rows. You can see this when we use EXPLAIN:</p><pre>EXPLAIN SELECT * ,(6371 * Acos(Cos(Radians(map_center_latitude)) * Cos(Radians(lat)) * Cos(Radians(lon) - Radians(map_center_longitude)) + Sin(Radians(map_center_latitude)) * Sin(Radians(lat)))) AS `distance` FROM `locations` HAVING `distance` &lt; 25 ORDER BY `distance`\G<br>*************************** 1. row ***************************<br>           id: 1<br>  select_type: SIMPLE<br>        table: locations<br>   partitions: NULL<br>         type: index<br>possible_keys: NULL<br>          key: lat_2<br>      key_len: 10<br>          ref: NULL<br>         rows: 9980<br>     filtered: 100.00<br>        Extra: Using where; Using index; Using filesort<br>1 row in set, 1 warning (0.00 sec)</pre><p>Now imagine having to do this same query over and over, with only the map_center changing. Even with some kind of debounce solution in the frontend and caching in the backend, it will always feel sluggish.</p><h3>Ok, but some maps are very fast, how?</h3><p>There are many ways to approach this, but most of them will be some form of caching, preloading or even NoSQL solutions. <br>Let’s quickly run through a few of the options and their downsides.</p><ol><li><strong>Caching</strong><br>This seems like the obvious first step. It’s quick, and it’s easy to implement in most frameworks like Laravel. But how would you implement caching if your only variable is every possible coordinate within your search area? In the end, this solution isn’t really viable.</li><li><strong>Preload all the things!</strong><br>While I haven’t been able to prove it, some of the maps I’ve studied for this article seem to load with the entire list of markers already there. There is no additional loading when scrolling, it’s just already there. And this is probably a perfect solution if you have a fairly limited area to search (though the Netherlands seems to be small enough), and with a set of points that don’t change very often and can therefore be cached easily. <br>The limit, of course, is the size of the dataset. The bigger that is, the longer it will take for the frontend to load and interpret it. Until at some point you reach a tipping point and suddenly your frontend is the bottleneck. And you can’t really control the different browsers and internet connections the end users will possess.</li><li><strong>NoSQL / ElasticSearch / Algolia and others</strong><br>This is a well known and very viable solution for our problem. Many of these kinds of services/systems have built-in features for quickly searching in location based data. And if you’re already using something like this, then there really is no need for looking any further. <br>If you aren’t already using such a system, then the investment of implementing one might not be worth it. After all, the location based searching is just one of many features they offer, and many of these services are paid subscriptions, or they might simply be very expensive to run and implement yourself. That’s budget you might not have or a system you might not be able to support.</li><li><strong>PostgreSQL with PostGis<br></strong>Ok, so this is cheating a bit. If you have the know-how and experience to use PostgreSQL, then by all means go for it. In fact ALMOST ALL of the advantages MySQL 8 offers over older versions have already existed in PostgreSQL for a long time. <br>So what’s the downside you may ask? It’s still a niche product, relatively speaking. There is far less information, far less tools, and a far smaller community than there is for MySQL. The learning curve is steeper and if you don’t already have experience with it, it’s most likely too costly to implement it for a fairly simple feature like we’re going to build here. <br>But that’s pretty much the only downside I can think of. So if you do have the knowledge and experience, you’re pretty much set and you can probably follow my guide here and implement the same app with PostgreSQL as well.</li></ol><h3>So where does MySQL 8 come in?</h3><p>Well I’m glad you asked! Next week we’ll continue with a quick tutorial to build a demo implementation of this concept. The end result will look something like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/824/1*_UWJUAO6Q6o3oIWDIHgQWQ.png" /></figure><p>Written by <a href="https://twitter.com/jochemfuchs">Jochem Fuchs</a>, Software engineer at <a href="https://www.maatwebsite.nl/">Maatwebsite</a>.</p><p>Do you need help working with geolocation in your app or do you need help building a Laravel application? At <a href="https://www.maatwebsite.nl/?source=post_page---------------------------">Maatwebsite</a>, we are there to help you on a commercial basis. Contact us via <a href="/service/mailto:info@maatwebsite.nl?source=post_page---------------------------">info@maatwebsite.nl </a>or via phone +31 (0)10 744 9312 to discuss the possibilities.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=cb33f62168ef" width="1" height="1" alt=""><hr><p><a href="https://medium.com/spartner/mysql-8-and-maps-a-power-combo-cb33f62168ef">MySQL 8 and maps. A power combo?</a> was originally published in <a href="https://medium.com/spartner">Spartner</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[The best way to locate, in MySQL 8]]></title>
            <link>https://medium.com/spartner/the-best-way-to-locate-in-mysql-8-e47a59892443?source=rss-bceaa88877ba------2</link>
            <guid isPermaLink="false">https://medium.com/p/e47a59892443</guid>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[geocoding]]></category>
            <category><![CDATA[laravel]]></category>
            <category><![CDATA[programming]]></category>
            <dc:creator><![CDATA[Jochem Fuchs]]></dc:creator>
            <pubDate>Wed, 07 Nov 2018 16:08:26 GMT</pubDate>
            <atom:updated>2018-11-21T11:05:52.439Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pybSvuZQQAZiqSb36FmAWg.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/photos/_94HLr_QXo8?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Jamie Street</a> on <a href="https://unsplash.com/search/photos/gps?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>MySQL 8 has brought a lot of awesome new features, as I’m sure you all know. I will not go into detail about all of these here, there are many others who can do and have done this much better than I can. (<a href="https://medium.com/u/ab1c7c2208e0">Gabriela D&#39;Ávila Ferrara</a> made a nice <a href="https://www.youtube.com/watch?v=KEkWVcJRl30">roundup</a> that I can most certainly recommend!)</p><p>Instead I will focus on geolocation, which is one of my favorite improvements in MySQL 8 as I’ve had to deal with this complexity far too many times:</p><pre>SELECT ( 6371 * acos( cos( radians(some_latitude) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(some_longitude) ) + sin( radians(some_latitude) ) * sin(radians(lat)) ) ) AS distance  FROM     locations  HAVING     distance &lt; 25  ORDER BY     distance;</pre><h4>Law of Haversine</h4><p>If you don’t recognize this, consider yourself lucky. But those who have had a need to calculate distances before will recognize this as the <em>Haversine formula</em>.</p><blockquote>The H<strong>aversine formula</strong> determines the <a href="https://en.wikipedia.org/wiki/Great-circle_distance">great-circle distance</a> between two points on a <a href="https://en.wikipedia.org/wiki/Sphere">sphere</a> given their <a href="https://en.wikipedia.org/wiki/Longitude">longitudes</a> and <a href="https://en.wikipedia.org/wiki/Latitude">latitudes</a>. Important in <a href="https://en.wikipedia.org/wiki/Navigation">navigation</a>, it is a special case of a more general formula in <a href="https://en.wikipedia.org/wiki/Spherical_trigonometry">spherical trigonometry</a>, the <strong>Law of Haversine</strong>, that relates the sides and angles of spherical triangles.</blockquote><p>If math is neither your hobby nor your job, likely you will have trouble understanding how this works exactly. Using this formula, debugging incorrect distance calculation will be hell.</p><h3>Spatial data types</h3><p>To understand the changes related to geolocation, you first need to know about spatial data types. Let’s look at the official explanation according to Microsoft:</p><blockquote>Spatial data represents information about the physical location and shape of geometric objects. These objects can be point locations or more complex objects such as countries, roads, or lakes.</blockquote><p>In other terms it’s a representation of geometric shapes within a spatial reference system. MySQL 8 supports multiple spatial types but we will concentrate on the Point type as that’s the most important type for geolocation.</p><pre>CREATE TABLE locations (<br>    location POINT NOT NULL<br>);</pre><p>A Point is a collection of two floating point values. One stands for X and one for Y. Within a spherical reference they are called Latitude and Longitude.</p><blockquote>Hey that sounds familiar!</blockquote><p>So you have been paying attention? Very good. Yes indeed we are now speaking a type of data we more commonly refer to as a GPS coordinate.</p><p>In the old days we would create two columns for this like so:</p><pre>CREATE TABLE locations (<br>    lat FLOAT( 10, 6 ) NOT NULL,  <br>    lng FLOAT( 10, 6 ) NOT NULL<br>);</pre><p>If you’re lucky, any existing system you need to jump into will have that. But for some reason I’ve often seen people use strings for this. Oh dear…</p><p>The Point column is a definite improvement over this, but it’s still less than perfect without an SRID.</p><h3>What’s a SRID and what do we need it for?</h3><p>A SRID is a numerical ID that refers to a Spatial reference system. I mentioned spatial/spherical references above, but I haven’t explained what I meant with that.</p><p>Having something to represent a coordinate available natively in the database is nice and all, but we need a correct spherical reference to ensure the coordinates we calculate are correct, or transferring these to a map and calculating distances will not work.</p><p>We need a spatial system that matches the dimensions we need as a basis for all our calculations. Now where would we find that ?</p><blockquote>Oh you mean like earth?</blockquote><p>Again, very astute! However it’s not as simple as that. There are quite a few <a href="https://en.wikipedia.org/wiki/Geographic_coordinate_system">systems</a> used all over the globe and we need the one that’s relevant to our needs.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/576/1*beqtZqSafANHLKMD_8DTNg.png" /></figure><p>This brings us to 1984. Instead of the Orwellian dystopia, that year actually brought forth quite some good things. And one of these is <a href="https://en.wikipedia.org/wiki/World_Geodetic_System">WGS84</a>:</p><blockquote>The <strong>World Geodetic System</strong> (<strong>WGS</strong>) is a standard for use in <a href="https://en.wikipedia.org/wiki/Cartography">cartography</a>, <a href="https://en.wikipedia.org/wiki/Geodesy">geodesy</a>, and <a href="https://en.wikipedia.org/wiki/Satellite_navigation">satellite navigation</a> including <a href="https://en.wikipedia.org/wiki/GPS">GPS</a>. It comprises a standard <a href="https://en.wikipedia.org/wiki/Geographic_coordinate_system">coordinate system</a> for the <a href="https://en.wikipedia.org/wiki/Earth">Earth</a>, a standard <a href="https://en.wikipedia.org/wiki/Spheroid">spheroidal</a> reference surface (the <em>datum</em> or <a href="https://en.wikipedia.org/wiki/Reference_ellipsoid"><em>reference ellipsoid</em></a>) for raw <a href="https://en.wikipedia.org/wiki/Altitude">altitude</a> data, and a <a href="https://en.wikipedia.org/wiki/Gravitation">gravitational</a> <a href="https://en.wikipedia.org/wiki/Equipotential_surface">equipotential surface</a> (the <a href="https://en.wikipedia.org/wiki/Geoid"><em>geoid</em></a>) that defines the <em>nominal sea level</em>.</blockquote><p>Before there were web standards, there were mapping standards, and this lovely system is something I’ve personally had a lot of <a href="https://www.geocaching.com/play">fun</a> with when GPS devices became readily available and affordable for the public. And now we can use this exact system in our database! All we need is the matching SRID and link it to our column.</p><h4>SRID coupling in the database</h4><p>Coming back to the Point type. By default, creating one of these columns will automatically assume an SRID of 0.</p><blockquote>By default, MySQL uses SRID 0, which represents an “infinite flat Cartesian plane with no units assigned to its axes”.<br>- <a href="https://tighten.co/authors/logan-henson">Logan Henson</a> Tighten.co</blockquote><p>Doing calculations with that SRID will give vastly different results. See the example below to see the difference. In the same database I created two tables with Point columns. One has a SRID of 0 and the other has SRID 4326, which refers to the WGS84 system:</p><pre>mysql&gt; SELECT ST_distance_sphere( <br>( SELECT location FROM locations_without_srsid LIMIT 1), <br>(SELECT location FROM locations_without_srsid LIMIT 1, 1) <br>)/1000 as distance_in_km;<br>+--------------------+<br>| distance_in_km     |<br>+--------------------+<br>| 10.980785022158566 |<br>+--------------------+<br>1 row in set (0,00 sec)<br><br>mysql&gt; SELECT ST_distance_sphere( <br>( SELECT location FROM locations_with_srsid LIMIT 1), <br>(SELECT location FROM locations_with_srsid LIMIT 1, 1) <br>)/1000 as distance_in_km;<br>+--------------------+<br>| distance_in_km     |<br>+--------------------+<br>|  7.221216762563222 |<br>+--------------------+<br>1 row in set (0,00 sec)</pre><p>The actual distance is indeed 7.22km as you can see here:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0YJ-IzbtMn3SyZtrn8_CCw.png" /></figure><p>So imagine you have a system based on this, but didn’t set a correct SRID, what the impact would be if this was set incorrectly!</p><p>So let me show you how to set the SRID correctly when creating the column:</p><pre>CREATE TABLE locations (<br>    location POINT SRID 4326 NOT NULL<br>);</pre><p>If you are using Laravel, like we do at <a href="https://www.maatwebsite.nl/">Maatwebsite</a>, then a<a href="https://github.com/laravel/framework/pull/24320">s of Laravel 5.6</a>, it’s really easy to do this in a migration as well:</p><pre>Schema::create(&#39;locations&#39;, function (Blueprint $table) {<br>    $table-&gt;point(&#39;location&#39;, 4326);<br>    $table-&gt;spatialIndex(&#39;location&#39;);<br>});</pre><p>A sharp eye will have noticed the spatialIndex method called above. <br>Yes indeed, spatial columns can be <a href="https://dev.mysql.com/doc/refman/8.0/en/using-spatial-indexes.html">indexed</a>!</p><h3>Location based searches in MySQL in the past</h3><p>Now we’ve seen how we can create the needed columns and we know what an SRID is. Let’s quickly look at some examples on how to use this to calculate some distances. I’ve secretly already shown both ways before, but we’ll look at them again in more detail in this section and the next.</p><h4>First we have the Haversine method</h4><pre>SELECT <br>  (<br>    6371 * acos(<br>      cos(<br>        radians(some_latitude)<br>      ) * cos(<br>        radians(lat)<br>      ) * cos(<br>        radians(lng) - radians(some_longitude)<br>      ) + sin(<br>        radians(some_latitude)<br>      ) * sin(<br>        radians(lat)<br>      )<br>    )<br>  ) AS distance <br>FROM <br>  locations;</pre><p>Even formatting this query doesn’t make it more readable. But let’s take it one at a time.</p><h4><strong>Coordinates conversion</strong></h4><p>We have two coordinates here we’re trying to calculate the distance between. There is “lat,lng” and there is “some_lat,some_lng”. In practice the latter would be entered in your query as the search parameters, where the former represents the value already in the database.</p><p>We use radians to convert all coordinates in Radians format. If you’ve had a calculator in school that could switch between RAD and DEG, you know how important it is to use the right one! Below we assume the values have already been converted.</p><h4><strong>Calculating with </strong><a href="http://www.mathwords.com/s/sohcahtoa.htm"><strong>SOHCAHTOA</strong></a></h4><pre>SELECT <br>  (<br>    6371 * acos(<br>      cos(some_lat) * cos(lat) * <br>      cos(lng - some_lng) + <br>      sin(some_lat) * <br>      sin(lat)<br>    )<br>  ) AS distance <br>FROM <br>  locations;</pre><p>So the next step is to do some cosine and sine calculations on our converted values. You see that it’s not simply a matter of X-Y=D. And quite frankly, I assume most will be quite lost at this point. Most people I know who’ve used this function just copied it from somewhere and assume it’s correct.</p><p><em>The exact inner workings of the formula itself are not important for the comparison I’m making here. However if you are interested in how this works, there is a </em><a href="https://en.wikipedia.org/wiki/Haversine_formula"><em>wikipedia article</em></a><em> about it and its mathematical background.</em></p><h4><strong>Correcting for your respective metric</strong></h4><p>That brings us to the last step, multiplying the results of the formula by 6371 is used to make sure the results are in meters. (Multiplying by 3959 gets you miles).</p><p>As you can see, there are quite a few steps involved, and we haven’t even delved into the full depth of the formula. For someone without a Maths background, this is nothing more than a black box. Luckily, there is a better way now!</p><h3>The better way</h3><p>With MySQL 8, it all changed into this:</p><pre>SELECT <br>    ST_distance_sphere(<br>        location_a, <br>        location_b<br>) as distance<br>FROM <br>  locations;</pre><p>Well that’s hard to misunderstand, isn’t it? Do you want to calculate the distance between two points on a sphere? Use ST_distance_sphere. Easy as that.</p><p>But to make it a bit harder, let’s not assume locations that are already in a database, but locations entered in the query directly. That’s a use case you’ll encounter quite often.</p><pre>SELECT <br>    ST_distance_sphere(<br>        point(lat, lng), <br>        point(some_lat, some_lng)<br>) as distance;</pre><blockquote>But how does it know what SRID to use?</blockquote><p>That’s a good question! And the answer is, it doesn’t! Or rather it will assume SRID 0.</p><pre>SELECT <br>    ST_distance_sphere(<br>        ST_GeomFromText(&#39;POINT(lat lng)&#39;, 4326), <br>        ST_GeomFromText(&#39;POINT(some_lat, some_lng)&#39;, 4326)<br>) as distance;</pre><p>There are other ways to get the same results, but this is the easiest I think. <br>ST_GeomFromText converts a textual representation of a geometry into the matching geometry format for the database. It can receive an extra parameter that then sets the SRID.</p><p>And there you have it, distance calculation with MySQL 8.</p><h3>Things to consider</h3><p>While MySQL implements OpenGis compatible formats like PostgreSQL PostGIS for example. It’s the ordering of arguments that does not match. MySQL uses POINT(lat lng) but many others, like PostgreSQL PostGIS use POINT(lng lat).</p><p>You’ll not get an error when entering it the wrong way, but the location will then of course be on the other side of the planet.</p><p>This is especially clear when fetching the separate lat and lng from a point column.</p><pre>SELECT <br>    ST_X(location) as lat, <br>    ST_Y(location) as lng<br>FROM <br>  locations;</pre><p>Intuitively this would be how you’d expect to get those respective values. Assuming POINT(lat lng) is the same as POINT(X Y).</p><p>In practice however, ST_Y gives you the latitude where ST_X gives you the longitude. Which is, of course, correct, as latitude is the Y axis, where as longitude is the X axis.</p><pre>SELECT <br>    ST_X(location) as lng, <br>    ST_Y(location) as lat<br>FROM <br>  locations;</pre><p>So the above is the correct way to fetch the respective values.</p><h3>Conclusion</h3><p>So working with geolocations in MySQL has improved greatly in version 8. Apart from it being a lot simpler to understand, it’s also index-able and a whole lot faster. I plan to do a comprehensive benchmark in the near future to demonstrate the speed difference.</p><p>Other services have jumped in, in the past, to enable fast and easy geo calculations and they are still very viable (Especially combined with a NoSql solution for fast and fuzzy searching). PostgreSQL PostGIS offers much of the same and has been for a while. But from the get-go MySQL 8 has everything you need, and it’s damn fast!</p><p>It even offers NoSql, but that’s a topic for a different day…</p><h3>Sources</h3><ul><li><a href="https://mysqlserverteam.com/spatial-reference-systems-in-mysql-8-0/">https://mysqlserverteam.com/spatial-reference-systems-in-mysql-8-0/</a></li><li><a href="https://dev.mysql.com/doc/refman/8.0/en/spatial-types.html">https://dev.mysql.com/doc/refman/8.0/en/spatial-types.html</a></li><li><a href="https://tighten.co/blog/a-mysql-distance-function-you-should-know-about">https://tighten.co/blog/a-mysql-distance-function-you-should-know-about</a></li><li><a href="https://mysqlserverteam.com/geographic-spatial-reference-systems-in-mysql-8-0/">https://mysqlserverteam.com/geographic-spatial-reference-systems-in-mysql-8-0/</a></li></ul><p>Written by <a href="https://www.linkedin.com/in/jochemfuchs/">Jochem Fuchs</a>, Software engineer at <a href="https://www.maatwebsite.nl/">Maatwebsite</a></p><p>Do you need help working with geolocation in your app or do you need help building a Laravel application? At <a href="https://www.maatwebsite.nl/">Maatwebsite</a>, we are there to help you on a commercial basis. Contact us via <a href="/service/mailto:info@maatwebsite.nl">info@maatwebsite.nl </a>or via phone +31 (0)10 744 9312 to discuss the possibilities.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e47a59892443" width="1" height="1" alt=""><hr><p><a href="https://medium.com/spartner/the-best-way-to-locate-in-mysql-8-e47a59892443">The best way to locate, in MySQL 8</a> was originally published in <a href="https://medium.com/spartner">Spartner</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>