Skip to content

Instantly share code, notes, and snippets.

@tscheepers
Last active August 11, 2021 04:45
Show Gist options
  • Save tscheepers/dd366c0b93c9b1b04c64 to your computer and use it in GitHub Desktop.
Save tscheepers/dd366c0b93c9b1b04c64 to your computer and use it in GitHub Desktop.
Geospatial sort by distance using Laravel and MySQL. I'm using a point column named geolocation in a table called things.
<?php
class GeoSpatialThingController extends \BaseController {
/**
* Display a listing of the resource.
*
* @return Response
*/
public function index()
{
// Using fairly new ST_Distance and ST_Within function in MySQL
// 60 nautical miles is approx 69 miles
$lat = 6.0;
$lon = 52.0;
$maximumDistance = 1000; // Distance in Miles
$lonBound1 = $lon - $maximumDistance / abs(cos(deg2rad($lat)) * 69);
$lonBound2 = $lon + $maximumDistance / abs(cos(deg2rad($lat)) * 69);
$latBound1 = $lat - ($maximumDistance / 69);
$latBound2 = $lat + ($maximumDistance / 69);
$things = Thing::whereRaw("
ST_Within(
geolocation,
envelope(
linestring(
point($lonBound1, $latBound1),
point($lonBound2, $latBound2)
)
)
)")->orderByRaw("
ST_Distance(
geolocation,
GeomFromText('POINT($lat $lon)')
)")->get();
return $things;
}
}
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
class CreateTable extends Migration {
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('things', function(Blueprint $table) {
$table->increments('id');
$table->timestamps();
});
DB::statement("ALTER TABLE things ADD COLUMN geolocation POINT");
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::drop('things');
}
}
@vafrcor
Copy link

vafrcor commented Nov 23, 2017

Can you explain about the "max-distance" variable? in "miles" ?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment