CODEWITHSUNDEEP
mysqlsqlloopscoordinatesdistance
patri
patri

Reputation: 343

Calculate MySQL coordinate and update rows

I need to loop through a table with coordinates (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...

Upvotes: 2

Views: 77

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

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.

Upvotes: 2

Related Questions