DROP FUNCTION IFEXISTS func_calcDistance;
CREATE FUNCTION func_calcDistance(
origLng DECIMAL(20,6),
origLat DECIMAL(20,6),
longitude DECIMAL(20,6),
latitude DECIMAL(20,6)
)
RETURNS DOUBLEBEGINDECLARE result DOUBLEDEFAULT0;SET result = round(6378.138*2*asin(
sqrt(pow(sin((origLat*pi()/180-latitude*pi()/180)/2),2)+
cos(origLat*pi()/180)*cos(latitude*pi()/180)*pow(sin((origLng*pi()/180-longitude*pi()/180)/2),2)))*1000);
RETURN result;
END;
IN RUBY MIGRATION
ActiveRecord::Base.connection.execute <<-SQLDROPFUNCTIONIFEXISTS func_calcDistance;
CREATEFUNCTION func_calcDistance(
origLng DECIMAL(20,6),
origLat DECIMAL(20,6),
longitude DECIMAL(20,6),
latitude DECIMAL(20,6)
)
RETURNSDOUBLEBEGINDECLARE result DOUBLEDEFAULT0;
SET result = round(6378.138*2*asin(
sqrt(pow(sin((origLat*pi()/180-latitude*pi()/180)/2),2)+cos(origLat*pi()/180)*cos(latitude*pi()/180)*pow(sin((origLng*pi()/180-longitude*pi()/180)/2),2)))*1000);
RETURN result;
ENDSQLLastestDeviceLocation.select("func_calcDistance() as distance").order("distance")
mysql 函数DROP FUNCTION IF EXISTS func_calcDistance;CREATE FUNCTION func_calcDistance( origLng DECIMAL(20,6), origLat DECIMAL(20,6), longitude DECIMAL(20,6), latitude DECIMAL(20,6))RET