0

Here is my SQLFiddle and here is my procedure

I want to rewrite this procedure to simple operation.

It should take the input as Lat, Lng and return the nearest driverid, lat, long (Order by desc and taking the first record) with respect to the given Lat and Long. It contains someother tables which i don't need.

(The problem is i can't able to find which field is required and which is not)

DELIMITER //
CREATE  PROCEDURE `driver_latlong`(IN `lat` DECIMAL(20,9), IN `lng` DECIMAL(20,9))
BEGIN
SET @sql = CONCAT('SELECT sd.DriverId, sd.Lat, sd.Long,  sd1.RadiusOfService, @dist:= ( 6371 * acos( cos( radians(',lat,') ) * cos( radians( sd.Lat ) ) * cos( radians( sd.Long ) - radians(',lng,') ) + sin( radians(',lat,') ) * sin( radians( sd.Lat ) ) ) ) AS distance 
        , Case When @dist <= 1 Then sd1.DeliveryFeeOne When @dist<= 2 Then sd1.DeliveryFeeTwo When @dist<= 3 Then sd1.DeliveryFeeThree Else sd1.DeliveryFeeMore End as deliveryfee
        , Case When @dist <= 1 Then sd1.DeliveryTimeOne When @dist<= 2 Then sd1.DeliveryTimeTwo When @dist<= 3 Then sd1.DeliveryTimeThree Else sd1.DeliveryTimeMore End as deliverytime
        , sd.ShopName, sd.Address, sd.LogoFile FROM driver_latlong sd LEFT JOIN  WHERE sd.Status = \'1\' and sd.Lat != 0 and sd.Long != 0 AND ( 6371 * acos( cos( radians(',lat,') ) * cos( radians( sd.Lat ) ) * cos( radians( sd.Long ) - radians(',lng,') ) + sin( radians(',lat,') ) * sin( radians( sd.Lat ) ) ) ) <= sd1.RadiusOfService  ORDER BY distance ASC;');                
                PREPARE stmt FROM @sql;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
    END //
    DELIMITER ;

Note : Actually i got this procedure from previous project so other tables are not required only table we refer is driver_latlong

AngularAngularAngular
  • 3,589
  • 5
  • 25
  • 41
  • If you can determine what 5km of Lat & 5km of Long are equal to you might try querying the DB for values that are between Current Lat - 5km and Current Lat + 5km (and also for Long), which might reduce your result set substantially, but you'll still need to run distancecalc() against the results. – BigScar Jan 23 '15 at 15:03
  • I don't understand your problem. You get the distance as a return. so this example: `echo distancecalc('11.0183', '76.9625', '11.0183', '76.9725');` shows you a result of '1.0913992930028' . so what is the problem now? – goldlife Jan 23 '15 at 15:03
  • getting distance is not my target, i need to get the record from the table which match the least distance – AngularAngularAngular Jan 23 '15 at 15:08

1 Answers1

1

use below query to fetch result and use your hard coded value in place of $lat and $lon 

 SELECT ((ACOS(SIN($lat * PI() / 180) * SIN(lat * PI() / 180) + COS($lat * PI() / 180) * COS(lat * PI() / 180) * COS(($lon – lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS `distance` FROM `members` HAVING `distance`<5
amit gupta
  • 1,167
  • 12
  • 29