Laravel eloquent scope: Efficient geographic queries in MySQL

Published 21 September 2023 14:30 (1-minute read)

Searching a table in MySQL based on latitude and longitude columns. But how do you make this as efficient as possible?

So, Aaron Francis from PlanetScale made a video about this:

Here is the scope you can use in your Laravel application:

public function scopeDistanceSphere($query, $lat, $lng, $radiusInMeter = 100)
{
  // Approx 1 meter in coordinates
  $offsetOneMeter = 0.000111;

  // Calculate radius with offset for the boundary box
  $radiusOffset = ($radiusInMeter * $offsetOneMeter) + $offsetOneMeter;

  // Set boundary box points
  $latOffsetMinus = $lat - $radiusOffset;
  $latOffsetPlus = $lat + $radiusOffset;
  $lngOffsetMinus = $lng - $radiusOffset;
  $lngOffsetPlus = $lng + $radiusOffset;

  return $query
      ->whereBetween('lat', [$latOffsetMinus, $latOffsetPlus])
      ->whereBetween('lng', [$lngOffsetMinus, $lngOffsetPlus])
      ->whereRaw("
          ST_distance_sphere(
              point($lng, $lat),
              point(lng, lat)
          ) > $radiusInMeter
      ");
}

Thanks to my colleague Coen de Rijter, for making this scope.

Robin Dirksen

Follow me on Twitter, there I post web-related content, tips/tricks, and other interesting things.

On my blog, you can find articles that I've found useful or wanted to share with anyone else.

If you want to know more about this article or just want to talk to me, don't hesitate to reach out.