不知道如何构建这个,所以向专家开放:
我在db中有一个客户列表,这些客户正在寻找其位置半径内的事件。我可以存储他们的邮政编码(或lat/lng)和他们参加活动的最大距离。因此列lat, lng,距离(例如:lat = '22.7447858', lng = '-82.1398589',距离= 25)。
事件全天发布,并存储其邮政编码/lat/lng。
我想运行一个查询(每天一次),为一个事件获取客户。我在看CyberJunkies张贴Mysql在距离查询,但问题是,我正在运行查询相反的方向。我需要找到那些"圈子距离"涵盖当前事件的客户,而不是相反。不确定如何存储圆距离(上面的3列是否足够好,或者是否有更好的方法来存储此类查询的数据)?不知道如何查询每个事件的客户。
提前感谢!
我认为有两种主要方法:动态计算距离,以及一次预计算距离,然后将其存储在查找表中。
选项1,动态计算。Tom van der Woerdt的答案很好地解释了如何做到这一点。伪代码查询是这样的:
SELECT * FROM customer, event WHERE (<calc distance>) < customer.distance
选项2,预先计算所有距离。您将创建一个表(在本例中称为distance
)来存储每个客户和每个事件之间的距离。它有三列:customerid
, eventid
,和miles
(或者你想要的任何距离度量)循环遍历每个客户,计算到每个事件的距离,并将每个客户存储在distance
中。每次添加新客户或事件时,都要向distance
表添加相应的记录。一旦这个结构就位,查找事件就像这样简单:
SELECT * FROM distance WHERE miles < [[some number you pick]]
那么哪个更好呢?这是CPU时间和磁盘空间之间的权衡,所以答案取决于您的资源。选项1(实时计算)将需要DBMS做更多的工作(更多的CPU时间)。随着人员和事件数量的增加,该查询将需要更长的时间来运行。选项2(预计算距离)将使查找非常快,但代价是您必须将所有这些预计算的距离存储在磁盘上。您还需要努力确保查找表是最新的。每次添加、删除客户或事件或更改其时间/长度时,都需要相应地更新查找表。触发器可以帮助你使这个过程自动化;只要确保您尝试测试每个场景(添加,删除,移动),以确保查找表得到更新,就像它应该的那样。
简短的回答:选择选项1(动态计算),如果您的数据库负载非常小,或者磁盘空间有限。如果您的负载很重,但磁盘空间很充裕,请选择选项2。选项2是更可能的场景,而且它更具可扩展性。
你想要的是:
SELECT * FROM customer, event WHERE (<calc distance>) < customer.distance
这将简单地获得所有客户和所有事件,将它们组合在一起以获得所有可能的组合(100个客户和10个事件产生1000种组合),然后检查它们是否在范围内。*
我个人建议创建一个DISTANCE(customer,event)
函数来计算它。这样更容易管理查询,并且可以重用它。
*不一定按照这个顺序
从A点到B点的距离将与从B点到A点的距离相同(除非你处理的是道路方向和不同的路径)
基本上你会做(在sql伪代码)
SELECT distance(event_loc, user_loc) <= user_max_distance
如果您的距离计算与此解决方案中的计算相似,那么您可以这样做:
select id1 from Distances
join EventTable on id2=EventTable.eventid
join UserTable on id1=UserTable.userid
where type2=<EVENT_TYPE> and type1=<USER_TYPE>
and geodistance_km_by_obj(id1,<USER_TYPE>,id2,<EVENT_TYPE>) < UserTable.max_distance
除了其他答案之外,您可能会考虑使用笛卡尔坐标(x, y和z)而不是lat/lng用于db存储,因为结果查询表达式在db服务器上的加载/时间方面比lat/lng距离查询更简单。
一个PHP实现的例子可以在下面找到:
http://headers-already-sent.com/geodistance/方法"getCartesian"将把lat/lng转换为笛卡尔坐标,方法"getDistanceByCartesian"显示如何计算实际距离。您需要做的是将这个距离计算从PHP转移到SQL查询(不应该那么复杂)。
编辑,因为我发现时间给一个更实际的例子
基于你可以在上面的链接下找到的类,我为我的公司位置和我们附近的所有麦当劳餐厅设置了2个演示表,并将lat/lng从谷歌地图转换为笛卡尔坐标x, y, z:
CREATE TABLE `locations` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL DEFAULT '',
`lat` double NOT NULL,
`lng` double NOT NULL,
`x` double NOT NULL,
`y` double NOT NULL,
`z` double NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `locations` (`id`, `title`, `lat`, `lng`, `x`, `y`, `z`)
VALUES
(1,'Ida-Ehre-Platz 10, 20095 Hamburg',53.55053,9.99949,3727600.05477,657242.251356,5124712.81705),
(2,'Kieler Straße 191-193, 22525 Hamburg',53.57731,9.93686,3725956.4981,652753.812254,5126481.40905),
(3,'Reeperbahn 42, 20359 Hamburg',53.549951,9.964937,3728046.74189,655003.113578,5124674.56664),
(4,'Theodor-Heuss-Platz 3, 20354 Hamburg',53.56083,9.99038,3726797.15378,656489.722425,5125393.17725),
(5,'Mundsburger Damm 67, 22087 Hamburg',53.57028,10.02642,3725550.98379,658686.623655,5126017.24553),
(6,'Paul-Nevermann-Platz 1, 22765 Hamburg',53.552602,9.936678,3728135.78521,653123.397726,5124849.69505),
(7,'Friedrich-Ebert-Damm 101, 22047 Hamburg',53.58753,10.08958,3723303.02881,662522.688778,5127156.05819),
(8,'Amsinckstraße 73, 20097 Hamburg',53.54271,10.02654,3727978.07563,659123.791421,5124196.16112),
(9,'Eiffestraße 440, 20537 Hamburg',53.55214,10.04638,3726919.13256,660267.521487,5124819.17553);
CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL DEFAULT '',
`lat` double NOT NULL,
`lng` double NOT NULL,
`x` double NOT NULL,
`y` double NOT NULL,
`z` double NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `user` (`id`, `name`, `lat`, `lng`, `x`, `y`, `z`)
VALUES
(1,'Ministry.BBS, Cremon 36, 20457 Hamburg',53.545943,9.988761,3728127.10678,656615.385203,5124409.77226),
(2,'BBS, Dorotheenstraße 60, 22301 Hamburg',53.583231,10.008315,3724617.80169,657307.963226,5126872.28974);
根据这两个表,查找到每个用户(我们公司的办公室)在一定距离内(在本例中以米为单位为2000)的所有位置(餐馆)的sql查询如下:
SELECT locations.*,
2 * 6371000.785 *
asin(
sqrt(
pow(locations.x - user.x, 2)
+ pow(locations.y - user.y, 2)
+ pow(locations.z - user.z, 2)
) / (2 * 6371000.785)
) AS distance
FROM locations, user
HAVING distance < 2000
ORDER BY distance ASC
如果你需要"米"以外的东西,你将不得不改变地球半径的约。6371000.785(以米为单位)到您需要的任何值,也可以将所需的距离2000更改为您喜欢的任何值,或者为每个用户存储在用户表中。