Order by distance on POINT in Laravel 5.1 via associated tables

Question

I'm attempting to order events by their distance from a user submitted postcode and distance.

I've attached a sample of my database tables and their relationships, as you can see geom is associated with multiple addresses via postcode and addresses can be associated to multiple tables (in this instance the events table).

I'm taking a postcode from the end user as well as a radius in miles to retrieve appropriate events, here is a sample of how I am achieving this in Eloquent.

/**
 * Extend locale method which initially only gets lat/long for given postcode to search
 *
 * @param \Illuminate\Database\Eloquent\Builder $query The query builder
 * @param \App\Http\Requests\SearchRequest $request The search request
 * @return void
 */
protected function locale(Builder $query, SearchRequest $request)
{
    $postcode = $this->formatPostcode($request->postcode);

    $geom = Geom::query()->where('postcode', $postcode)->first();
    if (! $geom || Cache::has('postcodeAPIFailed')) {
        return;
    }

    $lat = $geom->geo_location['lat'];
    $long = $geom->geo_location['long'];

    // Top-left point of bounding box
    $lat1 = $lat - ($request->within / 69);
    $long1 = $long - $request->within / abs(cos(deg2rad($lat)) * 69);

    // Bottom-right point of bounding box
    $lat2 = $lat + ($request->within / 69);
    $long2 = $long + $request->within / abs(cos(deg2rad($lat)) * 69);

    $query->whereHas('address', function (Builder $query) use ($request, $lat, $long, $lat1, $long1, $lat2, $long2) {
        $query->whereHas('geom', function (Builder $query) use ($request, $lat, $long, $lat1, $long1, $lat2, $long2) {
            $query->whereRaw('st_within(geo_location, envelope(linestring(point(?, ?), point(?, ?))))', [$long1, $lat1, $long2, $lat2]);
        });
    });
}

In the controller after we have retrieved the search results, we calculate the distances for each of the results.

if ($request->has('postcode')) {
    $postcodeDistances = $this->getDistances($results, $request);
}

This produces an array with a key of postcode and value of distance, i.e $postcodeDistances['L1 0AA'] = '3';, we send this array to the view.

In the view we then use the following logic to display distance on a record where applicable

@if($postcodeDistances)
    <span>
        {{ $postcodeDistances[$result->address->postcode] }}
        mile{{ $postcodeDistances[$result->address->postcode] != 1 ? 's' : '' }} away
    </span>
@endif

I've tried a few methods but I've been unable to update my function locale() to do the ordering by distance. I've considered maybe I can attached the distance to the collection and use a Laravel method to order the collections that way but achieving this from the database layer would be ideal if the latter is even possible.

My first attempt was to addSelect a distance field after whereHas('geom') and order by the new field

$query->addSelect(\DB::raw("ST_DISTANCE_SPHERE(geo_location, POINT({$long}, {$lat})) AS distance"));

I receive the following error:

SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 2 column(s) (SQL: select count(*) as aggregate from `event` where (select count(*) from `address` where `address`.`addressable_id` = `event`.`id` and `address`.`addressable_type` = event and (select count(*), ST_DISTANCE_SPHERE(geo_location, POINT(-2.717472, 53.427078)) AS distance from `geom` where `geom`.`postcode` = `address`.`postcode` and st_within(geo_location, envelope(linestring(point(-3.6903924055016, 52.847367855072), point(-1.7445515944984, 54.006788144928))))) >= 1) >= 1 and (select count(*) from `organisation` where `event`.`organisation_id` = `organisation`.`id` and `status` = 1) >= 1 and `event_template_id` is not null and `date_start` >= 2018-07-31 00:00:00 and `status` in (1, 5))

I also attempted to use orderByRaw in the same place instead, whilst I did not receive an error the results were not ordered accordingly.

$query->orderByRaw('ST_DISTANCE_SPHERE(geo_location, POINT(?, ?)) ASC', [$long, $lat]);

Answer 1

I'll have a go for solving your issue. But. As I mentioned in a comment, you will have to split up geo_location to lat and lng.

When that is done, the formula is as follows. This will calculate distance in km.

$distance = 50; //max distance in km
$limit = 100; //the amount of selected records
$earthRadiusKm = 6371;
$earthRadiusMiles = 3959;
$postcode = $this->formatPostcode($request->postcode);
$geom = Geom::query()->where('postcode', $postcode)->first();
$lat = $geom->lat;
$lng = $geom->lng;

//assuming your Geom model db name is geoms and the 'id' is id
$postcodeDistances = \DB::table('geoms')->selectRaw("
        geoms.id,  ( $earthRadiusKm * acos( cos( radians($lat) ) * cos( radians( geoms.lat ) ) 
        * cos( radians( geoms.lng ) - radians($lng) ) + sin( radians($lat) ) *
        sin(radians(geoms.lat)) ) ) AS distance, lat, lng
    ")->havingRaw("distance < $distance")->orderBy('distance')->limit($limit)->get();

For your interest, if you want the results in imperial miles, I have added the earth radius in both metrics. As the formula does make use of the earth's radius, longer distances will be more accurate.

The result (json) should look like this (2 records result, the first coord is always the starting point). Fyi, the coords are in the Philippines.

all: [
   {#3627
     +"id": 1128,
     +"distance": 0.0,
     +"lat": "15.6672998",
     +"lng": "120.7349950",
   },
   {#3595
     +"id": 1535,
     +"distance": 9.564007130831,
     +"lat": "15.6732128",
     +"lng": "120.6458749",
   },
]