Input starting and ending Geo locations(lat and long) and search from database(mysql) how many places between inputed starting and ending location?

Question

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

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.

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

Answer 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:

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).

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. 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.

3. 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:

   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!

   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).

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