patri - 3 months ago 5x
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: https://gist.github.com/Usse/4086343

At this moment I am trying to test the formula.

Thank you

EDIT 1:

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))
BEGIN

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;

END
``````

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.
Source (Stackoverflow)