محاسبه فاصله جغرافیایی با Haversine در SQL Server

محاسبه دقیق فاصله بین دو نقطه جغرافیایی در SQL Server با فرمول هاورسین

در بسیاری از سناریوها، نیاز به محاسبه فاصله بین دو نقطه جغرافیایی بر اساس عرض و طول جغرافیایی (Latitude و Longitude) وجود دارد. فرمول Haversine روشی دقیق برای محاسبه فاصله روی یک کره (مانند زمین) ارائه می‌دهد و نسبت به تقریب‌های مسطح برای فواصل طولانی‌تر مناسب‌تر است.

برای شروع، لازم است عرض و طول جغرافیایی نقاط را به رادیان تبدیل کنیم، زیرا توابع مثلثاتی در Haversine بر اساس رادیان عمل می‌کنند. اختلاف عرض و طول جغرافیایی بین دو نقطه به صورت زیر محاسبه می‌شود:

(Δlat = lat2 – lat1)

(Δlon = lon2 – lon1)

که در اینجا Δlat نشان‌دهنده تغییر در عرض جغرافیایی و Δlon نشان‌دهنده تغییر در طول جغرافیایی است. سپس، فرمول Haversine از این مقادیر استفاده می‌کند:

(a = sin²(Δlat/2) + cos(lat1) * cos(lat2) * sin²(Δlon/2))

این بخش از فرمول، مربع نصف وتر مرکزی (chord) بین دو نقطه را محاسبه می‌کند.

(c = 2 * atan2(√a, √(1−a)))

این قسمت از فرمول Haversine، قوس مرکزی (angular distance) بین دو نقطه را در واحد رادیان محاسبه می‌کند.

(d = R * c)

در نهایت، برای به دست آوردن فاصله نهایی (d)، شعاع زمین (R) در قوس مرکزی (c) ضرب می‌شود. شعاع متوسط زمین تقریباً 6371 کیلومتر (3959 مایل) است.

برای پیاده‌سازی این منطق در SQL Server، می‌توانیم یک تابع سفارشی ایجاد کنیم. این تابع عرض و طول جغرافیایی چهار نقطه را به عنوان ورودی دریافت کرده و فاصله بین آنها را بر اساس فرمول Haversine بازمی‌گرداند.

CREATE FUNCTION [dbo].[fnGetDistanceBetweenLatLong]
(
    @lat1 float,
    @lon1 float,
    @lat2 float,
    @lon2 float
)
RETURNS float
AS
BEGIN
    DECLARE @R float = 6371; -- Radius of Earth in kilometers (use 3959 for miles)
    DECLARE @dLat float = RADIANS(@lat2 - @lat1);
    DECLARE @dLon float = RADIANS(@lon2 - @lon1);

    SET @lat1 = RADIANS(@lat1);
    SET @lat2 = RADIANS(@lat2);

    DECLARE @a float = SIN(@dLat / 2) * SIN(@dLat / 2) +
                       COS(@lat1) * COS(@lat2) *
                       SIN(@dLon / 2) * SIN(@dLon / 2);

    DECLARE @c float = 2 * ATN2(SQRT(@a), SQRT(1 - @a));

    DECLARE @d float = @R * @c;

    RETURN @d;
END;

این تابع `fnGetDistanceBetweenLatLong` چهار پارامتر ورودی برای عرض و طول جغرافیایی دو نقطه دریافت می‌کند. در ابتدا، شعاع زمین را به کیلومتر (یا مایل) تعریف می‌کند. سپس، اختلاف عرض و طول جغرافیایی را محاسبه و به رادیان تبدیل می‌کند. در ادامه، مقادیر `a` و `c` را طبق فرمول Haversine محاسبه کرده و در نهایت، فاصله نهایی را با ضرب `c` در شعاع زمین به دست می‌آورد و بازمی‌گرداند.

مثال‌هایی برای استفاده از این تابع برای محاسبه فاصله بین شهرهای مختلف:

-- New York to Los Angeles
SELECT dbo.fnGetDistanceBetweenLatLong(40.7128, -74.0060, 34.0522, -118.2437) AS DistanceInKm_NY_LA;

-- London to Paris
SELECT dbo.fnGetDistanceBetweenLatLong(51.5074, 0.1278, 48.8566, 2.3522) AS DistanceInKm_London_Paris;

-- Mumbai to Delhi
SELECT dbo.fnGetDistanceBetweenLatLong(19.0760, 72.8777, 28.7041, 77.1025) AS DistanceInKm_Mumbai_Delhi;

با اجرای این کوئری‌ها، می‌توانید فواصل دقیق جغرافیایی را بین جفت شهرهای مختلف مشاهده کنید که با استفاده از فرمول Haversine و در تابع SQL Server محاسبه شده‌اند.

SQL Server همچنین یک نوع داده داخلی به نام `GEOGRAPHY` را برای کار با داده‌های فضایی جغرافیایی ارائه می‌دهد. این نوع داده دارای متد `STDistance()` است که می‌تواند فاصله بین دو نقطه جغرافیایی را محاسبه کند. استفاده از این روش اغلب ساده‌تر و بهینه‌تر است، به خصوص برای کاربردهای GIS (سیستم اطلاعات جغرافیایی).

-- Using GEOGRAPHY data type for New York to Los Angeles
DECLARE @Point1 GEOGRAPHY = GEOGRAPHY::Point(40.7128, -74.0060, 4326); -- NY
DECLARE @Point2 GEOGRAPHY = GEOGRAPHY::Point(34.0522, -118.2437, 4326); -- LA

SELECT @Point1.STDistance(@Point2) / 1000 AS DistanceInKm_GEOGRAPHY_NY_LA; -- Result in meters, convert to km

-- Using GEOGRAPHY data type for London to Paris
DECLARE @London GEOGRAPHY = GEOGRAPHY::Point(51.5074, 0.1278, 4326);
DECLARE @Paris GEOGRAPHY = GEOGRAPHY::Point(48.8566, 2.3522, 4326);

SELECT @London.STDistance(@Paris) / 1000 AS DistanceInKm_GEOGRAPHY_London_Paris;

در این مثال، `GEOGRAPHY::Point()` برای ایجاد نقاط جغرافیایی با استفاده از عرض و طول جغرافیایی و SRID (Spatial Reference Identifier) 4326 (که نمایانگر WGS 84 است) به کار رفته است. متد `STDistance()` فاصله را به متر برمی‌گرداند، بنابراین برای تبدیل به کیلومتر بر 1000 تقسیم می‌شود. هر دو روش، هم فرمول Haversine دستی و هم نوع داده `GEOGRAPHY`، ابزارهای قدرتمندی برای محاسبه فاصله در SQL Server هستند که بسته به نیاز و پیچیدگی پروژه می‌توانند مورد استفاده قرار گیرند.