patri - 1 year ago 39

SQL Question

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

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...

Answer Source

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.