输入启动和结束Geo位置(lat和long)和从数据库(mysql)搜索，输入启动和结束位置之间有多少个位置?

I am so confuse and so tired to goggling to find above trick. Actually i have a database table that have six fields id, Sratlatitude, Startlongitude, EndLatitude, EndLongitude and username. Now i want to query in the database like

我是如此的困惑和如此疲惫的去寻找上面的技巧。实际上，我有一个数据库表，它有6个字段id、Sratlatitude、Startlongitude、EndLatitude、EndLongitude和username。现在我想像这样在数据库中查询

SELECT * FROM LOCATIONS(table name) WHERE USERS BETWEEN STARTING POINT(InputedStartlatitude and InputedStartlongitude) AND ENDING POINT(InputedEndlatitude and InputedEndlongitude);

For example : USERXXX inputted starting point(InputedStartlatitude = 18.9647 and InputedStartlongitude = 72.8258) = Mumbai and Ending point(InputedEndlatitude = 18.9647 and InputedEndlongitude = 72.8258) = Delhi. Now he want to search how many USERS between this Inputted route(Driving Direction) using Inutedlatitudes and Inputedlongitudes. Search query will fire to the mysql database and compare from the stored starting and ending latitude and longitude which are between Mumbai to Delhi.

I have found one solution for this Like making query to the database as :

我找到了一种解决方法，比如向数据库查询:

SELECT * FROM lOCATIONS(tablename) WHERE (InputStartLatitude >= StartLatitude and IntartLongitude >= StartLong) and (InputEndLatitude <= EndLatitude and InuteEndLongitude <= EndLongitude);

But i have faced some problem with above query. Below are the coordinates of some places. Please have look :

但是我在上面的查询中遇到了一些问题。下面是一些地方的坐标。请看看:

The starting and ending co-ordinates are in RED color. Starting position is : Porbandar and Ending is : Mumbai. Now problem is that i m facing is when i trying to searching cities between Porbandar to Mumbai then not all cities comes those are actual situated in driving direction because of my query. I have called only those cities in my query how are greater than starting Lat and Long and Less then of Ending Lat and Long. But here Mumbai's Lat and longitude almost less then of all cities. so how can i make proper search query ?
Hope I have explain well above situation.

起止坐标均为红色。起点是:Porbandar，终点是:Mumbai。现在我要面对的问题是当我试图搜索从Porbandar到Mumbai之间的城市时并不是所有的城市都会因为我的质疑而实际位于驾驶方向。在我的查询中，我只给那些城市打过电话，它们比Lat和Long更大。但这里孟买的纬度和经度几乎比所有城市都要低。那么如何进行正确的搜索查询呢?希望我已经说明了以上情况。

Any response will be very helpful to me.
I am using php-mysql as back-end and android at client side.

任何回应都会对我很有帮助。我在客户端使用php-mysql作为后台，使用android。

1 个解决方案

#1

Your problem is probably that your search expression does not work correctly if your start values are higher than your end values. You have to sort your boundary values before you compare them to your table fields.

您的问题可能是，如果开始值高于结束值，那么搜索表达式就不能正常工作。在将边界值与表字段进行比较之前，必须对它们进行排序。

Here is a query that will work always - no matter whether your start location is east or west (and north or south) of your end location. For convenience I have uses MySQL's BETWEENoperator instead of >= and <=, but the major detail of this query is that the boundary values are sorted using the LEAST and GREATEST operators:

这是一个将永远工作的查询——不管你的起始位置是在你的最终位置的东还是西(以及北或南)。为了方便起见，我使用的是MySQL的between运算符，而不是>=和<=，但是这个查询的主要细节是边界值是使用最小和最大的运算符排序的:

SELECT * 
  FROM `locationtable` 
  WHERE  ( `lat` BETWEEN LEAST(InputStartLatitude, InputEndLatitude) AND GREATEST(InputStartLatitude, InputEndLatitude) )  
  AND    ( `long` BETWEEN LEAST(InputStartLongitude, InputEndLongitude) AND GREATEST(InputStartLongitude, InputEndLongitude) )
;

Hope that is what you are looking for.

希望这就是你想要的。

EDIT:

编辑:

After you have now specified what you really mean by "between", it becomes clear that you first have to think about an algorithm to solve your task - long before you think about the implementation of that algorithm (in MySQL or elsewhere).

在您指定了“between”的真正含义之后，很明显，您首先需要考虑一种算法来解决您的任务——在您考虑该算法的实现之前(在MySQL或其他地方)。

Here is a quick sketch of how I would approach a solution of your puzzle:

以下是我如何解决你的难题的一个简单的草图:

1. Get Google driving directions for the requested ride as well as for all offered rides.
2. 为所要求的乘车路线和所有提供的游乐设施提供谷歌驾驶指南。
3. These driving directions contain "steps" (see the Maps API) which are basically a list of lat/long points that are all crossed by the calculated trip. You will have to store the driving directions for all offered rides in your db because you will need them again and again to check against each new requested ride.
4. 这些驱动方向包含“步骤”(请参阅Maps API)，这些步骤基本上是一个lat/long点的列表，这些点都被计算出的行程所交叉。你将不得不为你的数据库中所有提供的服务存储驱动方向，因为你将需要它们一次又一次地检查每一次新的请求。

5. The basic magic now would be, to find if the steps of a requested ride are a sub-set of the steps of any offered ride in the db. Unfortunately this will nearly never be the case as the first and last mile of the requested ride will always be very specific to the requester's location (e.g. the road from his home to the next highway). So you will have to implement some tolerance here. You could do that by multiple approaches. Let me give you two examples:

   现在最基本的魔法是，找出被请求的骑乘的步数是否是db中任何提供骑乘的步数的子集。不幸的是，这种情况几乎不会发生，因为所要求的旅程的第一英里和最后一英里将始终非常具体地取决于请求者的位置(例如，从他的家到下一个高速公路的道路)。所以你需要在这里实现一些公差。你可以通过多种方法做到这一点。我举两个例子:

   a) Instead of checking whether a requested ride is fully contained in any offered ride (which never happens as explained above) you can try to find which steps the requested ride has in common with any offered ride. From the results take the longest one (with the biggest number of steps). This longest one can be offered to the requester "as is" - it is the best available solution for his request. Finding common sub-parts can be done by 1.) looking for the first common lat/long step coordinate in the driving directions of the requested and any offered ride and 2.) from there on counting all further steps that are identical in both rides. As you have to match a requested ride against all offered rides, the process of finding the first common lat/long coordinate can indeed be very expensive!

   a)你可以试着找出所要求的骑行与所提供的骑行有哪些共同之处。从结果中取最长的(步骤数最多的)。这个最长的可以提供给请求者“as is”——这是他请求的最好的解决方案。找到共同的子部分可以做1.)寻找第一个共同的lat/长步坐标在被请求的驾驶方向和任何提供的骑乘和2.从那里计算所有进一步的步在两个骑乘上是相同的。由于您必须将请求的骑行与所有提供的骑行匹配，因此找到第一个常见的lat/long坐标的过程确实非常昂贵!

   b) Another tolerant algorithm (simpler and cheaper) would be to first discard all steps from the beginning and end of all driving directions (ride request and ride offers) that cover only short distances. These steps will most likely be local driving from and to the next highway. You could then match the shortened lists directly without further tolerance. If the (shortened) request is fully contained in any (shortened) offer, that offer is a hit. This is very cheap to do. You might even be able to do that in MySQL if you create a clever data representation. For example you could create a string representation of all lat/long pairs in the shortened lists and check the db for an offered ride that contains the requested ride as a full sub-string (using MySQL's LIKE '%string%' pattern matching).

   b)另一种宽容的算法(更简单、更便宜)将是先放弃所有的步骤，从开始和结束所有的驾驶方向(骑马请求和乘车优惠)，只覆盖很短的距离。这些步骤很可能是在当地行驶，从下一条公路到下一条公路。然后，您可以直接匹配缩短的列表，而无需进一步容忍。如果(缩短的)请求完全包含在任何(缩短的)要约中，该要约就是一个热门。这样做很便宜。如果您创建一个巧妙的数据表示，您甚至可以在MySQL中实现这一点。例如，您可以在缩短的列表中创建所有lat/long对的字符串表示形式，并检查db是否提供了一个完整的子字符串(使用MySQL的“%string% string%”模式匹配)包含所请求的骑乘。

I hope that this sheds some light onto your problem and puts you on the right track.

我希望这能给你的问题带来一些启示，让你走上正确的道路。