在Google地图上搜索附近的地点，PHP& MySQL的 [英] Near location search on Google Maps, PHP & MySQL

问题描述

我正在构建一个网络应用程序（只是为了好玩xD），在这里你可以告诉它你在哪里以及你想去的地方，然后你可以搜索你可能采用的巴士列表。

我的db是这样的：

 巴士
  - ------------------------------- 
 id | bus_number | bus_description 
 
 routes 
 ----------------------- 
 id | bus_id | lat | lng 
  

路线表，正如您可能会注意到的那样，将存储公交车沿线的路线点，将显示一条折线，如果有搜索结果被发现。
问题是，如果给出这2个参数（用户在哪里，他想去哪里）并找到并显示正确的总线，我该如何编写一些SQL？

我从谷歌地图文档中找到了这条select语句，这很好（并且效果很棒！），因为它可以告诉我给定的经纬度是否在另一个纬度（在本例中为25英里）：

  SELECT id，（3959 * acos（cos（弧度（37））* cos（弧度（lat））* cos弧度（-122））+ sin（弧度（37））* sin（弧度（lat））））AS距距标记距离< 25 ORDER BY距离LIMIT 0,20; 
  

但我需要使用2给定的纬度/经度，所以我可以告诉用户必须在哪里公交车，哪里下车。

谢谢!!

（哦，我忘了，有一个

I'm building a web app (just for fun xD), in wich 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 wich 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 he wanna go) and find and show the correct buses?

I've found this select statement from Google Maps docs, wich 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

这篇关于在Google地图上搜索附近的地点，PHP& MySQL的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！