0

I have read all related questions to this issue but could not found solution.

I have saved coordinates in mysql colum type point, in laravel model i have saved like this

use Spatial;
protected $spatial = ['location'];

To get coordinates, i can do like this 

$model->getCoordinates();

But i want to select all those records with is 10km distance, how i can create laravel eloquent query for that?

Note: there is no lat,lon columns separately in my table so this query wont work

$results = DB::select(DB::raw('SELECT id, ( 3959 * acos( cos( radians(' . $lat . ') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(' . $lng . ') ) + sin( radians(' . $lat .') ) * sin( radians(lat) ) ) ) AS distance FROM articles HAVING distance < ' . $distance . ' ORDER BY distance') );

Updated: I have used laravel Voyager to save map locations, here is link

Doe
  • 13
  • 1
  • 9
  • can you provide what $model->getCoordinates(); function actually does. and how the actual location is stored. – ThataL Mar 14 '19 at 10:42
  • $model->getCoordinates(); This will return an array of coordinates with lat as the latitude and lng as the longitude. please see the voyager link for more details – Doe Mar 14 '19 at 10:50

2 Answers2

0

Try Like this 

$lon = 96.14344130;$lat = 16.86677920;$distance=10;

    MyNode::whereRaw(DB::raw( "(3959 * acos( cos( radians($lat) ) 
                     * cos( radians( ST_Y(geom) ) )  
                     * cos( radians( ST_X(geom) ) - radians($lon) ) 
                     + sin( radians($lat) ) 
                     * sin( radians( ST_Y(geom) ) ) ) ) < $distance "))
                     ->take(10)->get();
Amy
  • 131
  • 1
  • 12
  • I do not have lat,lng in my database table. there is only one field which save spatial data – Doe Mar 14 '19 at 10:52
  • Oh Sorry! Misunderstanding :-D .But Can't you convert geom to lat,lng from your spatial database? – Amy Mar 14 '19 at 11:29
  • Voyger function [REF](https://github.com/the-control-group/voyager/blob/bb25c3f7369eb324e0a1b012458326e8c08fc942/src/Traits/Spatial.php) same function need to implimented in mySql. – ThataL Mar 14 '19 at 11:31
  • I would like to know geom column type.Line String Or Point? – Amy Mar 14 '19 at 11:34
  • If your geom column is point you can query like DB::raw('ST_X(geom) as lng, ST_Y(geom) as lat')] or If your geom column is LineString Or Multilinestring you can query like this DB::raw('ST_AsGeoJSON(geom) as coordinates')] you will get Json Object with lat,lng. – Amy Mar 14 '19 at 11:35
  • @Amy: As i mentioned already, geom column is POINT., how i can select as per distance? – Doe Mar 14 '19 at 11:51
  • @ThataL: i can getCoordinates from that for single result. i want to select result with in distance. how to calculate distance on that? – Doe Mar 14 '19 at 11:55
0

$model->getCoordinates() returns array of coordinates. You can dump that and inspect array data. I think this exporting works.

list($lat, $lng) = $model->getCoordinates();

So you can have $lat, $lng variables.

Enver Arslan
  • 709
  • 3
  • 13