Wsrtjtyk

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

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