I have a JSON column in my database that stores longitude and latitude coordinates set up as:
_geoloc:{lat: 33.6556324, lng: -117.7015161}.
I am trying to query the database with a user's longitude & latitude coordinates to retrieve all records that fall within a certain distance. Below is the function that I am using, however all results are returning the same distance of 7797.5012454514 with each model. I would like to pass the $distance
parameter as miles.
protected function get_locations( $latitude, $longitude, $distance = 8000 ) {
$results= my_table::select( 'my_table.*' )
->selectRaw( '(3959 * (acos( cos( radians(?) )
* cos( radians( "_geoloc->lat" ) )
* cos( radians( "_geoloc->lng" ) - radians(?))
+ sin( radians( ? ) )
* sin( radians( "_geoloc->lat" ) ) ) ) )
AS distance', [ $latitude, $longitude, $latitude ] )
->havingRaw( "distance< ?", [ $distance ] )
->groupBy( 'first_name' )
->get();
return $results;
}
In case someone else comes across the same problem. The solution requires a JSON_EXTRACT to be used in the query along with a $ symbol to tokenize the object.
protected function get_locations( $latitude, $longitude, $distance = 20 ) {
$results = ( new my_table)->having( 'distance', '<', $distance )
->select( DB::raw( "*,
(3959 * ACOS(COS(RADIANS($latitude))
* COS(RADIANS(JSON_EXTRACT(`_geoloc`,'$.lat')))
* COS(RADIANS($longitude) - RADIANS(JSON_EXTRACT(`_geoloc`,'$.lng')))
+ SIN(RADIANS($latitude))
* SIN(RADIANS(JSON_EXTRACT(`_geoloc`,'$.lat'))))) AS distance" )
)->orderBy( 'distance', 'asc' )
->get();
return $results
}