patri patri - 6 months ago 16
SQL Question

Calculate MySQL coordonate and update rows

I have the following scenario, and since I am new to mysql and no one to guide me, I'll ask you.

I need to loop through a table with coordonates(lat,long in the same column) and compare them with a given value. If the distance is lower than 1 km, another column of the row that respect the condition is updated.

Something like this:

Foreach row in Table
if CalculateDistance(lat1,long1,lat2,long2) < 1
Update row.Column2

I found a formula to calculate, here:

At this moment I am trying to test the formula.

Thank you


So I created the procedure but I am stuck.

I created a split string function:

CREATE DEFINER=`root`@`localhost` FUNCTION `strSplit`(x varchar(255), delim varchar(12), pos int) RETURNS varchar(255) CHARSET utf8
return replace(substring(substring_index(x, delim, pos), length(substring_index(x, delim, pos - 1)) + 1), delim, '')

And my stored procedure of update the table is this:

CREATE DEFINER=`root`@`localhost` PROCEDURE `turn_camera_on`(coordonates varchar(50))

DECLARE lat1 varchar(50);
DECLARE long1 varchar(50);

SET lat1 = strSplit(coordonates, ',', 1);
SET long1 = strSplit(coordonates, ',', 2);

UPDATE deviceinfo
SET CameraOn = 1
where CalculateDistance(lat1,long1,**lat2,long2**) < 1;


The issue is that lat2 and long2 are a string in a row, in the table...


Presumably, this does what you want:

DECLARE v_lat1 varchar(50);
DECLARE v_long1 varchar(50);

SET lat1 = strSplit(v_coordinates, ',', 1);
SET long1 = strSplit(v_coordinates, ',', 2);
UPDATE deviceinfo di
    SET CameraOn = 1
WHERE CalculateDistance(v_lat1, v_long1, strSplit(di.coordinates, ',', 1), strSplit(di.coordinates, ',', 2)) < 1;

However, defining your own split functions seems quite superfluous in MySQL. You can just write:

UPDATE deviceinfo di
    SET CameraOn = 1
WHERE CalculateDistance(substring_index(v_coordinates, ',', 1),
                        substring_index(v_coordinates, ',', -1),
                        substring_index(di.coordinates, ',', 1),
                        substring_index(di.coordinates, ',', -1);

Also, some notes:

  • Always define table aliases (the di in the above queries).
  • Always qualify column names (the di. in the above queries).
  • Prefix variable and parameter names so they are not confused with columns (the v_) in the above queries.