Raw DB query to check if any one point is within a certain distance of any points in provided array

There's an array of arrays containing latitude, longitude of job locations.

JobLocationsArray = array([lat: $lat, lng: $lng],[lat: $lat, lng: $lng]);

There's an Employee model

There's a CoveredArea model which belongs to Employee. An Employee can have many covered areas.

CoveredArea

    int id
    employee_id
    float lat
    float lng
    time from
    time to
    string suburb

I need to check if any of the covered areas belonging to each employee are within 12km of any of the jobs whose lat and lng are contained in JobLocationsArray, then return these Employees.

The pseudo code for this would look something like this:

Employees.where(e => e.coveredAreas
.where(ca => JobLocationsArray
.where(jl => distanceCalc(jl.lat,jl.lng,ca.lat,ca.lng)))).toArray();

I am wondering what would be the best way of writing such query on Larvel/PHP/MySQL stack. In particular where to store the distanceCalc function and how to pass it in to the query.

The distance calculation function I am wanting to use looks as below, I am open to suggestions if there's anything better/faster, I am calculating over small distances so the function doesn't have to be too precise:

SELECT  *
FROM    table
WHERE   MBRContains
                (
                LineString
                        (
                        Point (
                                @lon + 10 / ( 111.1 / COS(RADIANS(@lat))),
                                @lat + 10 / 111.1
                              ),
                        Point (
                                @lon - 10 / ( 111.1 / COS(RADIANS(@lat))),
                                @lat - 10 / 111.1
                              ) 
                        ),
                mypoint
                )

Also would I need to alter my coveredAreas model to use POINT type to store the latitude and longitude ? Or can I get by with float ?

from Newest questions tagged laravel-5 - Stack Overflow http://ift.tt/290gtCu
via IFTTT