Last active
August 11, 2021 04:45
-
-
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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?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; | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?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'); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Can you explain about the "max-distance" variable? in "miles" ?