If you have 2 points (Lang and Lat) and you want to calculate distance between them in KM using SQL function, you can follow the following steps:
1. Execute the following script to declare a function to convert Degree to Radius:
CREATE FUNCTION [dbo].[ConvertDegreeToRadius](@Degree float)
RETURNS float
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
RETURN (
SELECT @Degree * (PI() / 180)
)
END
2. Execute the following script to declare a function to calculate the distance in KM:
CREATE FUNCTION [dbo].[CalculateDistance](@firstLat float, @firstLang float, @secondLat float, @secondLang float)
RETURNS float
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
-- static value, it is earch radius
DECLARE @EarchRadius int = 6371
DECLARE @Lat float = SearchTables.ConvertDegreeToRadius(@secondLat - @firstLat)
DECLARE @Lang float = SearchTables.ConvertDegreeToRadius(@secondLang - @firstLang)
DECLARE @a float = ( SIN(@Lat / 2) * SIN( @Lat / 2) + COS(SearchTables.ConvertDegreeToRadius(@firstLat)) * COS(SearchTables.ConvertDegreeToRadius(@secondLat)) * SIN (@Lang / 2) * SIN(@Lang / 2))
DECLARE @c float = 2 * ATN2(SQRT(@a), SQRT(1 - @a))
DECLARE @d float = @EarchRadius * @c
RETURN (
SELECT @d
)
END
3. Now, let us try to test the function and see the result:
1. Execute the following script to declare a function to convert Degree to Radius:
CREATE FUNCTION [dbo].[ConvertDegreeToRadius](@Degree float)
RETURNS float
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
RETURN (
SELECT @Degree * (PI() / 180)
)
END
2. Execute the following script to declare a function to calculate the distance in KM:
CREATE FUNCTION [dbo].[CalculateDistance](@firstLat float, @firstLang float, @secondLat float, @secondLang float)
RETURNS float
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
-- static value, it is earch radius
DECLARE @EarchRadius int = 6371
DECLARE @Lat float = SearchTables.ConvertDegreeToRadius(@secondLat - @firstLat)
DECLARE @Lang float = SearchTables.ConvertDegreeToRadius(@secondLang - @firstLang)
DECLARE @a float = ( SIN(@Lat / 2) * SIN( @Lat / 2) + COS(SearchTables.ConvertDegreeToRadius(@firstLat)) * COS(SearchTables.ConvertDegreeToRadius(@secondLat)) * SIN (@Lang / 2) * SIN(@Lang / 2))
DECLARE @c float = 2 * ATN2(SQRT(@a), SQRT(1 - @a))
DECLARE @d float = @EarchRadius * @c
RETURN (
SELECT @d
)
END
3. Now, let us try to test the function and see the result: