mysql查询以获取2 Latlngs之间的路由，在问题中解释 [英] mysql query to get route between 2 Latlngs explaining in the question

问题描述

我正在寻找一个查询来获取两点之间的路线，这很难解释，但是，

I'm looking for a query to get the route between 2 points its complicated to explain but,

我有这个链接可以说

example.com/any? lat1 = 31.0000 & lng1 = 31.0000 & lat2 = 31.0000 & lng2 = 31.0000

因此在函数内部，我想编写一个查询到表示例，

So inside the function, I want to write a query that goes to the table example,

id | lat1    | lng1    | lat2    | lng2     | time | distance_between_inKm
1  | 31.0000 | 31.0000 | 31.0000 | 31.0000   | 30   | 20

所以现在我想转到链接并获取最接近的||.最近的路线或行

So now I want to go to the link and get the nearest || Closest* route or row

我从Link lat1& lng1和lat2& lng2，所以我想最接近2.抱歉，很难解释我尽力了吗?

I'm getting these from Link lat1 & lng1 and lat2 & lng2 so I want to get the closest for the 2 I'm sorry it's so hard to explain I tried my best any help?

所以我想要的是我有一个表，其中包含折线的静态数据和2点的信息，所以我想像Google矩阵API一样使用它们，但是从我的服务器说起..

So what I want is that I have a table contains static data for polylines and info for 2 points so I want to use them like google matrix api but from my server lets say..

更新:

我尝试了此查询，但是它忽略了AND的任何帮助?

I tried this query but it ignore the AND any help?

    SELECT id,distance_between,time_between,start_latitude, start_longitude,end_latitude,end_longitude, SQRT(
        POW(69.1 * (start_latitude - 31.908482676577364), 2) +
        POW(69.1 * (35.1666776731924 - start_longitude) * COS(start_latitude / 57.3), 2)) 

AND
        SQRT(
        POW(69.1 * (end_latitude - 31.966051), 2) +
        POW(69.1 * (35.894587 - end_longitude) * COS(end_latitude / 57.3), 2))
        AS distance
    FROM matrix_api USE INDEX (start_latitude,start_longitude,end_latitude,end_longitude,distance_between,time_between) HAVING distance < 0.2 ORDER BY distance LIMIT 1;

所以我想要类似的东西给输入1和2并给我包含2的行

So I want something like that give input 1 and 2 and give me the row that has the 2

同时关闭开始和结束

更新，我也尝试过此操作:

Update I tried this too:

    SELECT id,distance_between,time_between,start_latitude, start_longitude,end_latitude,end_longitude, 
SQRT(
    POW(69.1 * (start_latitude - 32.016659), 2) +
    POW(69.1 * (35.727839 - start_longitude) * COS(start_latitude / 57.3), 2) AND

POW(69.1 * (end_latitude - 31.966051), 2) +
    POW(69.1 * (35.894587 - end_longitude) * COS(end_latitude / 57.3), 2)

) 

    AS distance
FROM matrix_api USE INDEX (start_latitude,start_longitude,end_latitude,end_longitude,distance_between,time_between) HAVING distance < 0.2 ORDER BY distance LIMIT 1;

推荐答案

{
   "destination_addresses" : [ "New York, NY, USA" ],
   "origin_addresses" : [ "Washington, DC, USA" ],
   "rows" : [
      {
         "elements" : [
            {
               "distance" : {
                  "text" : "225 mi",
                  "value" : 361715
               },
               "duration" : {
                  "text" : "3 hours 49 mins",
                  "value" : 13725
               },
               "status" : "OK"
            }
         ]
      }
   ],
   "status" : "OK"
}

这是从Google Matrix API获得的响应，为了提供距离，您将使用我在评论中谈到的方法(我将在下面链接).

This is a response obtained from Google Matrix API, to provide the distance, you'd use the methods I spoke about in my comments (I'll link below).

对于持续时间，您将使用距离/速度=持续时间.

For duration, you'd use distance/speed = duration.

如果您想知道如何获取speed，我会使用该国家/地区的平均速度限制，甚至是国家速度限制.对于我的国家/地区来说，国民时速通常为60英里/小时.

If you're wondering how to get speed, I'd use an average speed limit for that country or even national speed limit. For my country, national is generally accepted to be 60mph.

因此，如果我的两点之间的距离为225英里，并且我以每小时60英里的速度行驶，则持续时间为3.75或3小时45分钟.

So if my distance between points is 225 miles and I travel at 60 miles per hour, then the duration is 3.75 or 3 hours and 45 minutes.

我想为您提供至少一个从哪里开始的指导，我强烈建议您研究解决位置问题，因为这将为您提供解决方案.

I wanted to provide you with at-least some direction on where to begin, I'd highly recommend researching solving location problems as this will help you with your solution.

• https://stackoverflow.com/a/24372831/2932298
• https://medium.com /maatwebsite/the-best-way-to-locate-in-mysql-8-e47a59892443

• https://stackoverflow.com/a/24372831/2932298
• https://medium.com/maatwebsite/the-best-way-to-locate-in-mysql-8-e47a59892443

我建议阅读第二个链接，我快速浏览了一下，但是它提供了相当深入的解释来解决您的问题.

I recommend reading the second link, I had a quick look over but it provides a pretty in-depth explanation to solve your problem.

忽略使用PHP的事实，因为MySQL提供的大多数功能都可以用任何一种语言重新实现.

Ignore the fact that PHP is used, you can re-implement this in whichever language as most of the functionality is provided by MySQL.

忽略使用MySQL的事实，您可以使用任何一种语言重新实现它，因为MySQL中的大多数功能都存在于编程语言中(尤其是数学函数).

Ignore the fact that MySQL is used, you can re-implement this in whichever language, as most of the functionality in MySQL exists in programming languages (the mathematical functions especially).

有很多方法可以解决此问题.希望这会帮助您入门.

There's many ways to solve this problem. Hopefully this will go someway toward getting you started.

这篇关于mysql查询以获取2 Latlngs之间的路由，在问题中解释的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！