Re: Can someone convert sql function to mysql?

[Devart Team]

Something like this -

DELIMITER $$

CREATE FUNCTION DistanceBetween(Lat1 FLOAT, Long1 FLOAT, Lat2 FLOAT, Long2 FLOAT)
RETURNS FLOAT
BEGIN
  DECLARE dLat1InRad   DOUBLE; -- FLOAT(53) becomes DOUBLE
  DECLARE dLong1InRad  DOUBLE;
  DECLARE dLat2InRad   DOUBLE;
  DECLARE dLong2InRad  DOUBLE;
  DECLARE dLongitude   DOUBLE;
  DECLARE dLatitude    DOUBLE;
  DECLARE a            DOUBLE;
  DECLARE c            FLOAT;
  DECLARE kEarthRadius FLOAT;
  DECLARE dDistance    FLOAT;

  SET @dLat1InRad = @Lat1 * (PI() / 180.0);
  SET @dLong1InRad = @Long1 * (PI() / 180.0);
  SET @dLat2InRad = @Lat2 * (PI() / 180.0);
  SET @dLong2InRad = @Long2 * (PI() / 180.0);
  SET @dLongitude = @dLong2InRad - @dLong1InRad;
  SET @dLatitude = @dLat2InRad - @dLat1InRad;
  /* Intermediate result a. */

  SET @a = POWER(SIN(@dLatitude / 2.0), 2) + COS(@dLat1InRad)
  * COS(@dLat2InRad)
  * POWER(SIN(@dLongitude / 2.0), 2);
  /* Intermediate result c (great circle distance in Radians). */
  SET @c = 2.0 * ATAN2(SQRT(@a), SQRT(1.0 - @a));
  /* SET kEarthRadius = 3956.0 miles */
  SET @kEarthRadius = 6376.5; /* kms */

  SET @dDistance = @kEarthRadius * @c;
  RETURN @dDistance;
END$$

DELIMITER ;

Read about MySQL numeric types - Overview of Numeric Types.

Devart Company,
MySQL management tools
http://www.devart.com/dbforge/mysql/