Có rất nhiều yêu cầu về tính khoảng cách theo đường chim bay và thực tế, từ các hộ kinh doanh nhỏ cho đến những công ty giao vận lớn, hệ thống bưu cục, hay đơn giản là nhu cầu cá nhân để tra cứu… Tuy nhiên, không phải ai cũng biết cách cũng như không có được đội ngũ lập trình để xây dựng nên hệ thống tính toán. Dù vậy, ta hoàn có thể tự làm một giải pháp đơn giản sử dụng Google Sheets hoặc Excel. Học Excel Online sẽ hướng dẫn bạn làm điều đó qua bài viết này.
Tính khoảng cách theo đường chim bay và thực tế
Tính toán khoảng cách theo đường chim bay giữa 2 điểm bằng công thức Haversine
Một công thức không quá phức tạp được sử dụng để tính khoảng cách theo đường chim bay giữa 2 tọa độ mà ta có thể sử dụng là công thức Haversine. Công thức được mô tả:
Một cách tổng quát, ta viết lại công thức này trong Excel như sau:
=6371*2*ASIN(SQRT(POWER(SIN(PI()/180*(lat2–lat1)/2),2)+COS(PI()/180*lat1)*COS(PI()/180*lat2)*POWER(SIN(PI()/180*(lng2–lng1)/2),2)))
Trong đó:
6371 là bán kính trái đất (đơn vị: Km)
lat1, lat2: Vĩ độ lần lượt của tọa độ 1 (lattitude 1) và tọa độ 2 (lattitude 2)
lng1, lng2: Kinh độ lần lượt của tọa độ 1 (longitude 1) và tọa độ 2 (longitude 2)
Ví dụ, ta tính khoảng cách với 2 điểm có tọa độ 1 (20.98705, 105.8138) và tọa độ 2 (21.00433, 105.8155)
Áp dụng công thức ta có:
=6371*2*ASIN(SQRT(POWER(SIN(PI()/180*(21.00433-20.98705)/2),2)+COS(PI()/180*20.98705)*COS(PI()/180*21.00433)*POWER(SIN(PI()/180*(105.8155-105.8138)/2),2)))
=1.929536024
Các bạn có thể thử tra trên google map để kiểm tra lại kết quả.
Ngoài ra, ta có thể tạo 1 UDF bằng VBA với tên Haversine cho mục đích trên:
Public Function Haversine(Lat1 As Variant, Lon1 As Variant, Lat2 As Variant, Lon2 As Variant)
Dim R As Integer, dlon As Variant, dlat As Variant, Rad1 As Variant
Dim a As Variant, c As Variant, d As Variant, Rad2 As Variant
R = 6371
dlon = Excel.WorksheetFunction.Radians(Lon2 - Lon1)
dlat = Excel.WorksheetFunction.Radians(Lat2 - Lat1)
Rad1 = Excel.WorksheetFunction.Radians(Lat1)
Rad2 = Excel.WorksheetFunction.Radians(Lat2)
a = Sin(dlat / 2) * Sin(dlat / 2) + Cos(Rad1) * Cos(Rad2) * Sin(dlon / 2) * Sin(dlon / 2)
c = 2 * Excel.WorksheetFunction.Asin(Sqr(a))
d = R * c
Haversine = d
End Function
Ta sử dụng công thức như sau:
=Haversine(20.98705, 105.8138, 21.00433, 105.8155)
Đối với Google sheets, ta cũng có thể viết một công thức tương tự trên chính trang tính, hoặc bằng Google Apps Script để làm việc này:
function haversine(lat1, lng1, lat2, lng2) {
const R = 6371
var dlon = Math.PI/180*(lng2 - lng1)
var dlat = Math.PI/180*(lat2 - lat1)
var ra1 = Math.PI/180*lat1
var ra2 = Math.PI/180*lat2
var a = Math.sin(dlat / 2) * Math.sin(dlat / 2) + Math.cos(ra1) * Math.cos(ra2) * Math.sin(dlon / 2) * Math.sin(dlon / 2)
var c = 2 * Math.asin(Math.sqrt(a))
var d = R * c
return d
}
Với công thức trên, ta có thể tính được khoảng cách giữa 2 tọa độ. Lưu ý, tọa độ ở đây là tọa độ thập phân – Decimal degrees. Trong trường hợp ta sử dụng tọa độ đi kèm phút giây – Degrees Minutes Seconds, ta cần thêm một bước chuyển tọa độ về dạng thập phân. Ví dụ: 72*17’55 sẽ cần được chuyển về 72.29861.
Ngoài công thức Haversine, bạn có thể tham khảo sử dụng một công thức khác có phần phức tạp hơn là công thức Vincenty.
Tính toán quãng đường di chuyển thực tế giữa 2 tọa độ sử dụng API
“Đi từ nhà lên bờ hồ hết bao nhiêu km?” – khi hỏi câu hỏi này, chắc hẳn chúng ta không mong chờ một đáp án kiểu như “3km theo đường chim bay” phải không. Trong trường hợp này, hầu hết mọi người sẽ sử dụng các công cụ bản đồ, chẳng hạn như Google Maps, để tra cứu quãng đường và nói ra quãng đường tốt nhất được gợi ý.
Tương tự tình huống trên, giả sử chúng ta có 2 địa chỉ (hoặc nhiều hơn) lưu trong Excel/Google Sheets và cần biết quãng đường giữa 2 địa chỉ đó thì sao?
Trong trường hợp này, có nhiều bên đã cung cấp các API để ta dễ dàng tra cứu dữ liệu liên quan tới quãng đường mà ta có thể tìm kiếm thông qua từ khóa “distance matrix”. Ở đây Học Excel Online sẽ sử dụng Bing Maps API để lấy dữ liệu về quãng đường từ tọa độ 20.98705, 105.8138 tới 21.00433, 105.8155. Các bước tiến hành sẽ được nói dưới đây.
Lấy Bing Maps Key
Bước đầu tiên đó là ta cần có một “mã cá nhân”. Để làm điều này, ta truy cập địa chỉ: https://www.bingmapsportal.com/ và đăng nhập bằng tài khoản Microsoft (hoặc đăng kí nếu chưa có).
Sau khi đã đăng nhập thành công, truy cập vào My account và chọn My keys:
Bước tiếp theo, ta chọn “Click here to create a new key” sau đó chọn “Show key” để hiển thị mã. Lưu mã đó lại.
Khi đã có mã, bước tiếp theo ta sẽ tiến hành sử dụng Bing Maps API để lấy dữ liệu về quãng đường.
Bây giờ, ta sẽ ngó qua một chút về API, cụ thể ở đây trong trường hợp này ta sẽ sử dụng Distance Matrix tại link này.
Url ta sử dụng sẽ có dạng:
https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins=lat1,lng1&destinations=lat2,lng2&travelMode=driving&key=BingMapsAPIKey
Trong đó:
lat1, lng1, lat2, lng2: lần lượt là vĩ độ và kinh độ của tọa độ 1 và tọa độ 2
travelMode=driving: loại hình di chuyển, tương tự như chọn lái xe hoặc đi bộ khi bạn thao tác trên Google Map. Mục này có thể bỏ qua.
BingMapsAPIKey: key bạn vừa lấy được ở portal trên
Sau khi điền đầy đủ thông tin vào Url trên với tọa độ 20.98705, 105.8138 và 21.00433, 105.8155 ta sẽ nhận được thông tin hiển thị như sau:
Ta để ý đến dòng “TravelDistance”. Đây là quãng đường (đơn vị km) giữa 2 tọa độ. Khi kiểm tra lại bằng Bing Maps, ta có kết quả tương tự.
Công việc tiếp theo ta cần phải làm đó là lấy được dữ liệu từ TravelDistance về.
Sử dụng Google Apps Script để thu thập dữ liệu về quãng đường
Bạn cần có kiến thức cơ bản của Google Apps Script để hiểu cách thức hoạt động. Tham gia ngay khóa học GAS101: Tự động hóa công việc với Google Apps Script
Đoạn code sau sẽ giúp bạn làm điều này:
function getlocation(lat1,lng1,lat2,lng2){
var link = 'https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?'
var key = 'điền key của bạn vào đây'
var response = UrlFetchApp.fetch(link + 'origins=" + lat1 + ",' + lng1 + '&destinations=" + lat2 + ",' + lng2 + '&travelMode=driving' + '&key=' + key);
var json = response.getContentText();
var parse = JSON.parse(json);
var result = parse.resourceSets[0].resources[0].results[0].travelDistance;
return result
}
Sau khi đã lưu và cấp quyền, ta mở lại Google Sheet và viết cú pháp: =getlocation(20.98705, 105.8138, 21.00433, 105.8155) để lấy ra quãng đường giữa 2 tọa độ
Ta cũng hoàn toàn có thể làm được với VBA trong Excel. Hướng dẫn chi tiết cho phần này đã được giới thiệu trong khóa học VBA201: Lập trình VBA nâng cao trong Excel, bài số 1004. Hoặc đơn giản hơn bạn có thể truy cập Youtube và xem video này:
Trên đây là toàn bộ hướng dẫn về cách tính khoảng cách giữa 2 điểm đơn giản mà từ đó chúng ta có thể mở rộng ra cho nhiều trường hợp, chẳng hạn như để tính toán tổng quãng đường 1 shipper đi trong ngày, thời gian đi hay thậm chí có thể trực quan hóa dữ liệu thành một bản đồ đường đi nữa. Các bạn cũng đừng quên ghé thăm trang web https://dtnguyen.business/ để tham khảo các khóa học mới nhất nhé.
