I'm building a web app (just for fun), in which you can tell it where you are and where you want to go, and then you can search for a list of buses you may take.
My db is something like this:
buses
---------------------------------
id | bus_number | bus_description
routes
-----------------------
id | bus_id | lat | lng
routes table, as you may notice, stores the route points that the bus follows, points which I will be displaying with a polyline, if any search results are found. The question is how can I write some SQL, given this 2 parameters (where the user is, and where they want to go) and find and show the correct buses?
I've found this select statement from Google Maps docs, which is nice (and works great!) because it can tell me if a given Lat/Lng is in the radius (in this case 25 miles) of another one:
SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;
But I need this to work with 2 given Lat/Lng so I can tell where the user must take the bus, and where to get down.
Thanks!!
(Oh, I forgot, there is a preview, just plain html and nothing working, but useful if you would like to see how I plan to this app look like. Btw it is in Spanish, here you go in English google translated)
Update: Here is some sample data on the routes table:
+----+-------+------------+------------+
| id | bus_id| lat | lng |
+----+-------+------------+------------+
| 1 | 1 | -31,527273 | -68,521408 |
| 2 | 1 | -32,890182 | -68,844048 |
| 3 | 1 | -31,527273 | -68,521408 |
| 4 | 1 | -32,890182 | -68,844048 |
| 5 | 1 | -31,527273 | -68,521408 |
| 6 | 2 | -32,890182 | -68,844048 |
| 7 | 2 | -31,527273 | -68,521408 |
| 8 | 2 | -32,890182 | -68,844048 |
| 9 | 2 | -31,527273 | -68,521408 |
| 10| 2 | -32,890182 | -68,844048 |
+----+-------+------------+------------+
Just ignore the repeated lat,lng values, the point is that a bus route will have many, hundreds of points to describe the complete route.
Ok, let's get started, using query below you get nearest bus stops in certain radius (miles). Query will return every point within defined radius.
$lat = -31,52;
$lon = -68,52;
$multiplier = 112.12; // use 69.0467669 if you want miles
$distance = 10; // kilometers or miles if 69.0467669
$query = "SELECT *, (SQRT(POW((lat - $lat), 2) + POW((lng - $lng), 2)) * $multiplier) AS distance FROM routes WHERE POW((lat - $lat), 2) + POW((lng - $lng), 2) < POW(($distance / $multiplier), 2) ORDER BY distance ASC";
Result... nearest in 10 mile radius...
farthest but within 10 miles...
Now repeat the same for destination
, and then search your table for buses on that route.
Also check out this link... http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html