MYSQL - table not updating from Procedure

Question

I want to get distance between two GeoPoints (using LatLong) for that I wrote GETDISTANCE function from solution provided [MySQL Function to calculate distance between two latitudes and longitudes. If I call function independently it works like charm. As per my understanding I cannot return ResultSet from Function in MySQL so I created Procedure and called function inside procedure As follows:

  DELIMITER $$
     CREATE PROCEDURE GetNearByGeoPoints(IN Lat REAL, IN Longi REAL)
       BEGIN
       DECLARE v_max int;
       DECLARE v_counter int unsigned default 0;


       SET @v_max = (SELECT COUNT(*) FROM TransmitterPointsData);

       START TRANSACTION;

       WHILE v_counter < v_max
       DO

       SELECT @coverageID :=CoverageID, @tableLatitude := Latitude, @tableLongitude :=Longitude FROM TransmitterPointsData LIMIT v_counter,1;

       SET @Dist= GETDISTANCE(Lat, Longi, tableLatitude, tableLongitude);

       UPDATE TransmitterPointsData SET DynamicDistance = @Dist WHERE CoverageID= @coverageID;

       set v_counter=v_counter+1;

       END WHILE;
       COMMIT;

       SELECT * FROM TransmitterPointsData;
       END $$
     DELIMITER ;

What I am trying to do is taking a set of LatLong parameters from user and comparing it with each set of LatLong from table. And after getting output from function I am updating TransmitterPointsData table with where condition on coverageID.

This is my first MySQL query so far I was following syntax but I do not know why I am getting all null values in DynammicDistance Column.

Thank You in Advance

Shouldn't it be `GETDISTANCE(Lat, Longi, @tableLatitude, @tableLongitude)`? I'm surprised that the procedure compiles ... — Henning Koehler, Nov 05 '18 at 02:13
I treid that also, but it still giving me null values at end — Pruthviraj Mohite, Nov 05 '18 at 02:15
My best guess at this point is that your computation of the input values to GETDISTANCE is faulty. Try adding some debug info (storing / printing the inputs computed along with the results). — Henning Koehler, Nov 05 '18 at 02:19
I tried calling function like GETDISTANCE(10, 12, 12, 12) from stored procedure by giving hard coded values, but still its null in DynammicDistance column. But if I call function independently GETDISTANCE(10, 12, 12, 12) I am getting 138.11138916015625 as output. As per mysql query it should update 138.111*** value in DynamicDistance column — Pruthviraj Mohite, Nov 05 '18 at 02:27

score 0 · Answer 1 · answered Nov 05 '18 at 07:21

Try replacing the while loop with this:

UPDATE TransmitterPointsData
SET DynamicDistance = GETDISTANCE(Lat, Longi, Latitude, Longitude)

Much shorter, and you avoid potential issues with row selection via limit + offset (which is poor style at best, and gives you a random row each time at worse).

MYSQL - table not updating from Procedure

1 Answers1