MySQL Cursor Looping to Find Minimum

Question

Here is the task I am trying to accomplish:

Find the closest available vehicle for a given location.

I have a table for vehicle and a table for location as follows:

CREATE TABLE location
  (location_id numeric(8,0) UNIQUE NOT NULL,
   address varchar(100), 
   latitude float,
   longitude float,
   PRIMARY KEY(location_id)
 );

CREATE TABLE vehicle
  (license_plate char(6) UNIQUE NOT NULL,
   make varchar(30) NOT NULL,
   model varchar(30) NOT NULL,
   year numeric(4,0) NOT NULL CHECK(year>1990),
   state char(2) NOT NULL, 
   capacity int NOT NULL,
   last_location numeric(8,0) DEFAULT NULL,
   FOREIGN KEY(last_location) REFERENCES location(location_id) ON DELETE 
   CASCADE ON UPDATE CASCADE,
   PRIMARY KEY(license_plate)
 );

I wrote a query that calls a function to loop through the vehicle table, calculate the distance with a given location and return the license_plate of the car with the minimum distance.

SELECT @locationA := 11111111;
SET @loc_lat = (SELECT latitude FROM location WHERE location_id = 
@locationA);
SET @loc_long = (SELECT longitude FROM location WHERE location_id = 
@locationA);
SELECT license_plate, make, model FROM vehicle
WHERE license_plate = find_car(@loc_lat, @loc_long); 

DELIMITER $$
CREATE FUNCTION find_car(loc_lat float, loc_long float) RETURNS char  
BEGIN
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    DECLARE CUR_DIST float DEFAULT 1000000000; 
    DECLARE car_lat NUMERIC; 
    DECLARE car_long NUMERIC; 
    DECLARE dist float; 
    DECLARE closest_car char(6); 
    DECLARE car_temp char(6);
    DECLARE loc_temp numeric; 
    DECLARE car_cursor CURSOR FOR SELECT license_plate, last_location FROM 
    vehicle;
    OPEN car_cursor; 
    car_loop: LOOP
        FETCH car_cursor INTO car_temp, loc_temp;
        SET car_lat = (SELECT latitude FROM location WHERE location_id = 
        loc_temp);
        SET car_long = (SELECT longitude FROM location WHERE location_id = 
        loc_temp);
        SET dist = (SELECT ST_Distance_Sphere(
            point(loc_lat, loc_long),
            point(car_lat, car_long)
            ) * .000621371192);
        IF dist < CUR_DIST THEN 
            SET closest_car = car_temp;
            SET CUR_DIST = dist; 
        END IF; 
    END LOOP;
    CLOSE car_cursor; 
    RETURN(closest_car);
END $$ 
DELIMITER ;

Right now, this returns nothing and I am not quite sure why. I'm still fairly new to SQL so thanks in advance!

Jon Jaussi · Answer 1 · 2018-12-16T04:22:00.730

My advice is to first try a set based approach and see how it performs.

Some good insights on why found here.

The general strategy: For each location_id, find the next closest location_id

Some specific tactics:

Use jpgunter's GETDISTANCE function (code found below)
Click here to see his code in context
Calculate the distance between all location_id values using a self join to to the location table
Determine an arbitrary distance that is "too far" and exclude everything beyond that distance
This can help improve performance
Pick the smallest distance from the result

This is a starter script for the self join to the location table and the "too far" criteria...

SELECT l1.location_id as l1_location_id
      ,l1.latitude as l1_latitude
      ,l1.longitude as l1_longitude
      ,l2.location_id as l2_location_id
      ,l2.latitude as l2_latitude
      ,l2.longitude as l2_longitude
      ,GETDISTANCE(l1.latitude, l1.longitude, l2.latitude, l2.longitude) as l1_12_distance
  FROM location AS l1
  JOIN location AS l2 ON l1.location_id <> l2.location_id
 WHERE GETDISTANCE(l1.latitude, l1.longitude, l2.latitude, l2.longitude) <= 1000; -- JJAUSSI: arbitrary "too far"

This is jpgunter's GETDISTANCE function...

DELIMITER $$
/*
Takes two latitudes and longitudes in degrees. You could comment out the conversion if you want to pass as radians.
Calculate the distance in miles, change the radius to the earth's radius in km to get km.
*/

DROP FUNCTION IF EXISTS GETDISTANCE$$
CREATE FUNCTION GETDISTANCE 
  (deg_lat1 FLOAT, deg_lng1 FLOAT, deg_lat2 FLOAT, deg_lng2 FLOAT) 
  RETURNS FLOAT 
  DETERMINISTIC 
BEGIN 
  DECLARE distance FLOAT;
  DECLARE delta_lat FLOAT; 
  DECLARE delta_lng FLOAT; 
  DECLARE lat1 FLOAT; 
  DECLARE lat2 FLOAT;
  DECLARE a FLOAT;

  SET distance = 0;

  /*Convert degrees to radians and get the variables I need.*/
  SET delta_lat = radians(deg_lat2 - deg_lat1); 
  SET delta_lng = radians(deg_lng2 - deg_lng1); 
  SET lat1 = radians(deg_lat1); 
  SET lat2 = radians(deg_lat2); 

  /*Formula found here: http://www.movable-type.co.uk/scripts/latlong.html*/
  SET a = sin(delta_lat/2.0) * sin(delta_lat/2.0) + sin(delta_lng/2.0) * sin(delta_lng/2.0) * cos(lat1) * cos(lat2); 
  SET distance = 3956.6 * 2 * atan2(sqrt(a),  sqrt(1-a)); 

  RETURN distance;
END$$
DELIMITER ;

You may want to consider revising the field name year as it is a reserved word.

You may find that if/when your database grows that these names are too general:

location
address
latitude
longitude

However, I do not know your database. It could be that your table and field names are a precise fit for your need. Hope this helps!

MySQL Cursor Looping to Find Minimum

1 Answers1