I am trying to find the closest locations of pre-defined entries within a table. So far, the query I am using works fine and provides a fairly accurate result set.
How it works is: 1 table contains pre-defined estimations from plumbers that set their estimated cost for a specific price range. However, each plumber has their own working radius that can be different to other plumbers. Here is an example.
Table: Pre-Defined Quotation
ID NAME PRICERANGE ESTIMATED-COST WORKING-RADIUS LAT LONG
1 JOHN £500-£800 £560 20 MILES 51.50 -0.118
My current query loops over the full pre-defined quotations table and will order the results by distance being closest first.
$quotes = Quote::all()->where('latitude', '!=', null)->where('longitude', '!=', null)->where('estCost', '!=', null);
foreach ($quotes as $quote) {
$tableName = "quotes";
$origLat = 52.5002721395; // Customers lat
$origLon = -1.98032029216; // Customers long
$dist = 45; // This needs to be dynamic like: $quote->working_radius
$query = DB::select("SELECT id, latitude, longitude, estCost, working_radius, 3956 * 2 *
ASIN(SQRT( POWER(SIN(($origLat - latitude)*pi()/180/2),2)
+COS($origLat*pi()/180 )*COS(latitude*pi()/180)
*POWER(SIN(($origLon-longitude)*pi()/180/2),2)))
as distance FROM $tableName WHERE
longitude between ($origLon-$dist/cos(radians($origLat))*69)
and ($origLon+$dist/cos(radians($origLat))*69)
and latitude between ($origLat-($dist/69))
and ($origLat+($dist/69))
having distance < $dist ORDER BY distance limit 25");
}
$json = json_encode($query);
$obj = json_decode($json, TRUE);
for ($i = 0; $i < count($obj); $i++) {
echo "Distance is " . round($obj[$i]["distance"], 1) . " from est id:" . $obj[$i]["id"] . " <br />";
}
The problem I have is that the query is using the variable $dist to define the maximum distance from the customers lat/long that the plumber should be. This would be ok apart from I need this to be defined by the working_radius value set within the same table or the plumbers_profile table.
I am using laravel here and i have tried several different ways to find a solution but seem to be failing every time.
The basic way I can explain this would be that I need to select all the pre-defined quotes that are no further in "miles" than the value set for each plumber in the working_radius column.
Right now the query above will return the following example:
Distance is 4.4 from quote id:1617
In an ideal situation i need to assign the $dist variable something like this: $quote->working_distance because the working distance is different for every plumber and the customer should not be shown a pre-defined quotation for a plumber that does not cover the customer's location.
If I assign $quote->working_radius to the $dist variable the results are incorrect and are shown like this:
Distance is 3.1 from quote id:1508
However the plumber with id: 1508 only has a 2 mile working_radius and should not be shown. If I set $dist = 2; then that same plumber does not get shown, So the issue lays with assigning the $quote->working_radius dynamically and is failing to use the value set in the database to determine the result.
Maybe i am going about this the totally wrong way? As a learner i have tried my best so far so hopefully someone with better experience can provide a helping hand on this. Thanks.
Updated (17/11/2016)
After working on the code since posting my question i have come up with a solution that works however im not sure it works for the good or for the bad! Maybe i have gone to overboard and maybe i have overly complicated things by assuming this is more complicated than it actually needs to be?
$quotes = Quote::orderBy('working_radius')->where('latitude', '!=', null)->where('longitude', '!=', null)->where('estCost', '!=', null)->get();
$userLat = 52.5002721395; // Random selected lat and long for demo -- this acts as the customers property location
$userLong = -1.98032029216;
$tableName = "quotes";
$dist = 250; // This is the maximum it can be through the app
$query = DB::select("SELECT id, working_radius, ( 3959 * acos( cos( radians($userLat) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians($userLong) ) + sin( radians($userLat) ) * sin( radians( latitude ) ) ) )
AS distance
FROM $tableName
HAVING distance < $dist
ORDER BY distance LIMIT 0 , 25");
$json = json_encode($query);
$obj = json_decode($json, TRUE);
for ($i = 0; $i < count($obj); $i++) {
$plumbers[] = array('distance' => round($obj[$i]["distance"], 1), 'id' => $obj[$i]["id"], 'working_radius' => $obj[$i]["working_radius"]);
}
foreach ($plumbers as $plumber) {
if($plumber['distance'] <= $plumber['working_radius']){
var_dump($plumber);
}
}
Hopefully i have done things half right? As a learner im trying to self educate at the same time as progressing on with the app! Thanks.
