如何使用纬度和经度找到两点之间的距离 [英] How to find distance between two points using latitude and longitude

问题描述

我有一个 ROUTES 表，其中包含 SOURCE_AIRPORT 和 DESTINATION_AIRPORT 列，并描述了飞机从一个到另一个的特定路线.

I have a ROUTES table which has columns SOURCE_AIRPORT and DESTINATION_AIRPORT and describes a particular route that an airplane would take to get from one to the other.

我有一个 AIRPORTS 表，其中包含描述机场地理位置的 LATITUDE 和 LONGITUDE 列.

I have an AIRPORTS table which has columns LATITUDE and LONGITUDE which describes an airports geographic position.

我可以使用它们共享的列连接这两个表，这些列在路由表中称为 SOURCE_AIRPORT_ID 和 DESTINATION_AIRPORT_ID，在机场表中称为 IATA(一个 3 字母代码代表一个机场，例如伦敦希思罗机场的 LHR).

I can join the two tables using columns which they both share called SOURCE_AIRPORT_ID and DESTINATION_AIRPORT_ID in the routes table, and called IATA in the airports table (a 3 letter code to represent an airport such as LHR for London Heathrow).

我的问题是，如何使用所有这些信息编写 SQL 查询以查找离开特定机场(如 LHR)的最长路线?

My question is, how can I write an SQL query using all of this information to find, for example, the longest route out of a particular airport such as LHR?

我相信我必须加入这两个表，并且对于源机场为LHR的路由表中的每一行，查看目的地机场的经纬度，计算离LHR有多远，将其保存为字段称为距离"，然后首先按最高距离对数据进行排序.但就 SQL 语法而言，我不知所措.

I believe I have to join the two tables, and for every row in the routes table where the source airport is LHR, look at the destination airport's latitude and longitude, calculate how far away that is from LHR, save that as a field called "distance", and then order the data by the highest distance first. But in terms of SQL syntax i'm at a loss.

推荐答案

我认为就解决方法而言，您已经了解了大约 90%.我将添加一些关于潜在 SQL 查询的额外细节以获得您的答案.因此，您需要执行 2 个步骤来计算距离 - 第 1 步是创建一个包含将 ROUTES 表连接到 AIRPORTS 表的表，以获取路线上 SOURCE_AIRPORT 和 DESTINATION_AIRPORT 的纬度/经度.这可能看起来像这样:

I think you're about 90% there in terms of the solution method. I'll add in some additional detail regarding a potential SQL query to get your answer. So there's 2 steps you need to do to calculate the distances - step 1 is to create a table containing joining the ROUTES table to the AIRPORTS table to get the latitude/longitude for both the SOURCE_AIRPORT and DESTINATION_AIRPORT on the route. This might look something like this:

SELECT t1.*, CONVERT(FLOAT, t2.LATITUDE) AS SOURCE_LAT, CONVERT(FLOAT, t2.LONGITUDE) AS SOURCE_LONG, CONVERT(FLOAT, t3.LATITUDE) AS DEST_LAT, CONVERT(FLOAT, t3.LONGITUDE) AS DEST_LONG, 0.00 AS DISTANCE_CALC
INTO ROUTE_CALCULATIONS 
FROM ROUTES t1 LEFT OUTER JOIN AIRPORTS t2 ON t1.SOURCE_AIRPORT_ID = t2.IATA 
               LEFT OUTER JOIN AIRPORTS t3 ON t1.DESTINATION_AIRPORT_ID = t3.IATA;

结果输出应创建一个名为 ROUTE_CALCULATIONS 的新表，该表由所有 ROUTES 列、SOURCE 和 DESTINATION 机场的经度/纬度以及值为 0 的占位符 DISTANCE_CALC 列组成.

The resulting output should create a new table titled ROUTE_CALCULATIONS made up of all the ROUTES columns, the longitude/latitude for both the SOURCE and DESTINATION airports, and a placeholder DISTANCE_CALC column with a value of 0.

第 2 步是计算距离.这应该是一个相对简单的计算和更新.

Step 2 is calculating the distance. This should be a relatively straightforward calculation and update.

UPDATE ROUTE_CALCULATIONS 
SET DISTANCE_CALC = 2 * 3961 * asin(sqrt((sin(radians((DEST_LAT- SOURCE_LAT) / 2))) ^ 2 + cos(radians(SOURCE_LAT)) * cos(radians(DEST_LAT)) * (sin(radians((DEST_LONG- SOURCE_LONG) / 2))) ^ 2))

这应该会在 DISTANCE_CALC 表中给出数据中看到的所有路线的计算距离.从那里您应该能够进行任何您想要的与距离相关的路线分析.

And that should give the calculated distance in the DISTANCE_CALC table for all routes seen in the data. From there you should be able to do whatever distance-related route analysis you want.

这篇关于如何使用纬度和经度找到两点之间的距离的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！