Laravel, MySQL Spatial Point distance: orderBy and get distance

Question

Can anyone help me get this query right.

I have a Concert model and another Location model. Concert belongs to a location. Location table has a spatial point field.

I need to do three things:

order by distance from users location
display distance from users location
get concerts that are in some given distance from the users location

So far I have managed to set the database and to get the concerts in a certain distance from the user as:

$condition = ($lat != null && $lon != null && $distance != null);
Concert::when($condition, function ($query) use ($distance,$lat,$lon) {
      return $query->whereHas('location', function($query) use ($distance,$lat,$lon) {
          $query->whereRaw('st_distance(location,POINT('.$lat.','.$lon.')) < '.$distance);
      });
  })
->get();

Can you help me how to display this st_distance in the view?

How would I order this by distance?

What is the proper way to get the $distance in miles/kilometers as I read in the docs that st_distance is given in degrees?

score 6 · Answer 1 · edited May 23 '17 at 12:25

You can select ST_DISTANCE or ST_DISTANCE_SPHERE (MySQL Spatial Convenience Functions) and then:

Order results using ORDER BY sql clause
Add distance constraint using HAVING sql clause (why can't use WHERE clause here)

$raw = 'SELECT
  ST_X(location) AS longitude,
  ST_Y(location) AS latitude,
  ST_DISTANCE_SPHERE(location, POINT(?, ?)) AS dist
FROM
  concerts,
  locations
WHERE
  concerts.id = locations.id
HAVING
  dist < ?
ORDER BY
  dist';
$pointsWithDist = DB::select($raw, [$lon, $lat, $distance]);

Notes:

store longitude as first param and latitude as second param of POINT spatial function
both ST_DISTANCE and ST_DISTANCE_SPHERE return distance in meters by default

you can use google maps to display data

Laravel, MySQL Spatial Point distance: orderBy and get distance

1 Answers1