Excel公式使用纬度/经度坐标计算多个点之间的距离
我目前正在使用两个表绘制一个模拟数据库架构:Booking和Waypoint.
I'm currently drawing up a mock database schema with two tables: Booking and Waypoint.
- 预订存储出租车预订信息.
- Waypoint会存储旅途中的上落点以及纬度位置.每个序列都是旅程中的一站.
我如何在Excel中计算每个旅程中不同站点之间的距离(使用纬度/经度数据)?
How would I calculate the distance between the different stops in each journey (using the lat/lon data) in Excel?
是否有一种方法可以在Excel中以编程方式定义此方法，即可以将公式放入mileage列(Booking表)，在该过程中查找匹配顺序(通过bookingId) Waypoint表并返回结果?
Is there a way to programmatically define this in Excel, i.e. so that a formula can be placed into the mileage column (Booking table), lookup the matching sequence (via bookingId) for that journey in the Waypoint table and return a result?
示例1:
有2个停靠点的旅程:
1 1 1 MK4 4FL, 2, Levens Hall Drive, Westcroft, Milton Keynes 52.002529 -0.797623
2 1 2 MK2 2RD, 55, Westfield Road, Bletchley, Milton Keynes 51.992571 -0.72753
根据Google的
4.1英里，在Booking表的mileage列(其中id = 1
4.1 miles according to Google, entry made in mileage column in Booking table where id = 1
示例2:
三站旅程
6 3 1 MK7 7DT, 2, Spearmint Close, Walnut Tree, Milton Keynes 52.017486 -0.690113
7 3 2 MK18 1JL, H S B C, Market Hill, Buckingham 52.000674 -0.987062
8 3 1 MK17 0FE, 1, Maids Close, Mursley, Milton Keynes 52.040622 -0.759417
根据Google的说法，
27.7英里，是在Booking表的mileage列(其中id = 3
27.7 miles according to Google, entry made in mileage column in Booking table where id = 3
直到最近，通过三角剖分构造了精确的地图，本质上是毕达哥拉斯定理的应用.对于任何一对坐标之间的距离，取x坐标差之平方和y坐标差之平方之和的平方根.但是，x和y坐标必须使用相同的单位(例如，英里)，这涉及到对纬度和经度值的分解.这可能会很复杂，因为经度的因素取决于纬度(绕北极走比绕赤道走的距离要短)，但在您的情况下，应该使北纬52 o 起作用.由此得出的结果(可以在此处进行检查)与示例的差异约为20％您给出(在第二种情况下，将ID配对为6和7，并将结果与ID配对为7和8相加).
Until quite recently, accurate maps were constructed by triangulation, which in essence is the application of Pythagoras’s Theorem. For the distance between any pair of co-ordinates take the square root of the sum of the square of the difference in x co-ordinates and the square of the difference in y co-ordinates. The x and y co-ordinates must however be in the same units (eg miles) which involves factoring the latitude and longitude values. This can be complicated because the factor for longitude depends upon latitude (walking all round the North Pole is less far than walking around the Equator) but in your case a factor for 52o North should serve. From this the results (which might be checked here) are around 20% different from the examples you give (in the second case, with pairing IDs 6 and 7 and adding that result to the result from pairing IDs 7 and 8).