declare @Latitude DECIMAL(17, 14)='-29.72216606140100',@Longitude DECIMAL(17, 14)='31.06697845459000'
SELECT DISTINCT
*,
pm_Latitude,
pm_Longitude,
CASE
WHEN p.latpoint = pm_Latitude
AND p.longpoint = pm_Longitude
THEN CAST(0 AS DECIMAL(8, 2))
ELSE CAST(p.distance_unit * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(pm_Latitude)) * COS(RADIANS(p.longpoint) - RADIANS(pm_Longitude)) + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(pm_Latitude)))) AS DECIMAL(8, 2))
END AS 'pm_Distance'
FROM Pm_PropertyMapping WITH(NOLOCK) CROSS APPLY
(
SELECT @Latitude AS latpoint,
@Longitude AS longpoint,
100 AS radius,
111.045 AS distance_unit
) AS p
WHERE pm_PropertyName LIKE '%' + 'propertyname' + '%'
AND pm_Latitude BETWEEN p.latpoint - (p.radius / p.distance_unit) AND p.latpoint + (p.radius / p.distance_unit)
AND pm_Longitude BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint)))) AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
ORDER BY pm_Distance;
What this code does is it searches in a table for the closest property given a specific lat and long.
My question is. Is this the most efficient way of calculating distance in SQL?
