我有一个包含地点的数据库,我需要在我的网页中显示从任何地方到其他地方的距离。将距离存储在某个地方将节省大量工作(加载它们应该比重新计算它们更容易)。但是如何保存距离的方阵呢?每次插入新行时创建一个新列似乎不是一个好的解决方案,但我没有找到更好的解决方案(尽管我可以想到解决方法,例如计算大约 10 或 20 个最近的距离并假设我很少需要更多)。
在 PHP/MySQL 中保存可变(和不断增长的)大小的方表的最佳方法是什么?还是没有好的解决方案,而我(或其他一些)解决方法更好?
编辑 注意:正如评论中提到的,一旦您获得足够的位置,仅存储长/纬度值并根据这些值动态计算距离可能更有意义。但是,此处解释的解决方案可能仍适用于其他应用。
处理此问题的最佳方法是使用数据透视表,其中每行有两个位置 ID 和一个距离值。
现在,由于距离 A-B 与 B-A 相同,我们只需要存储每个配对一次。我们可以通过仅在 A 的 ID 小于 B 时才存储距离来做到这一点。
设置
首先是一张places
表来存放您的位置
id | name
---+---------
1 | Place_A
2 | Place_B
3 | Place_C
4 | Place_D
然后是places_distances
数据透视表:
place_id_1 | place_id_2 | distance
-----------+------------+----------
1 | 2 | 10.0
1 | 3 | 20.0
1 | 4 | 15.0
2 | 3 | 12.0
2 | 4 | 8.0
3 | 4 | 14.0
请注意,数据透视表不需要自己的 ID 字段(尽管有些人可能会争辩说有时拥有一个 ID 字段仍然很好)。您将按如下方式设置一个唯一键(您需要查看文档以了解正确用法):
UNIQUE KEY `UNIQUE_placesDistances_primary`(`place_id_1`,`place_id_2`)
这可确保不能在表中两次具有相同的位置/位置配对。
您还需要确保设置外键:
CONSTRAINT FOREIGN KEY `FK_placesDistances_place1` (`place_id_1`)
REFERENCES `places`(`id`),
CONSTRAINT FOREIGN KEY `FK_placesDistances_place2` (`place_id_2`)
REFERENCES `places`(`id`)
这将确保您只能为在 places
中实际定义的位置添加条目。 这也意味着(如果使用默认外键行为),如果有引用某个地点的距离行,则无法删除该地点。
使用示例
查找两个地方之间的距离
(给定两个变量@id_1
作为第一位的 id,@id_2
作为第二位的 id)
SELECT `distance`
FROM `places_distances`
WHERE (`place_id_1` = @id_1 AND `place_id_2` = @id_2)
OR (`place_id_2` = @id_1 AND `place_id_11` = @id_2)
LIMIT 1;
我们使用 OR 来解释我们尝试查找距离2
到1
而不是1
到2
的情况 - 请记住,我们只存储第一个位置的 id 小于第二个位置的值,以避免存储重复项。
插入新距离
(给定三个变量@id_1
作为第一名的 id,@id_2
作为第二名的 id,@distance
是距离)
INSERT `places_distances`(`place_id_1`,`place_id_2`,`distance`)
VALUES(LEAST(@id_1, @id_2),GREATEST(@id_1, @id_2), @distance)
我们使用内置的比较函数 LEAST
和 GREATEST
来帮助维护我们的规则,即我们只存储第一个 ID 小于第二个 ID 的位置,以避免重复。
显示地名列表,按地名从最远到最近的距离排序
要从places
表中获取原始名称以显示在我们的places_distances
查询中,我们必须将它们连接在一起。在这种情况下,LEFT JOIN
是最佳选择,因为我们只关心places_distances
表中的内容。有关 MySQL 联接的更多信息,请查看此处。
SELECT
`p_1`.`name` AS `place_1`,
`p_2`.`name` AS `place_2`,
`distance`
FROM `places_distances`
LEFT JOIN `places` AS `p_1`
ON `distances`.`place_id_1` = `p_1`.`id`
LEFT JOIN `places` AS `p_2`
ON `distances`.`place_id_2` = `p_2`.`id`
ORDER BY `distance` DESC
它应该返回一个这样的表:
place_id_1 | place_id_2 | distance
-----------+------------+----------
Place_A | Place_C | 20.0
Place_A | Place_D | 15.0
Place_C | Place_D | 14.0
Place_B | Place_C | 12.0
Place_A | Place_B | 10.0
Place_B | Place_D | 8.0
显示地点表及其到特定给定地点的距离
这有点棘手,因为我们需要在不是输入位置的行中显示名称,但我们可以使用另一个有用的函数IF(CONDITION,'TRUE_OUTPUT','FALSE_OUTPUT')
来做到这一点。
(@place_name
是包含地名的变量,在本例中为"Place_B")
SELECT
IF(`p_1`.`name`=@place_name, `p_2`.`name`, `p_1`.`name`) AS `name`,
`distance`
FROM `places_distances`
LEFT JOIN `places` AS `p_1`
ON `distances`.`place_id_1` = `p_1`.`id`
LEFT JOIN `places` AS `p_2`
ON `distances`.`place_id_2` = `p_2`.`id`
WHERE `p_1`.`name` = @place_name OR `p_2`.`name` = @place_name
ORDER BY `distance` DESC
它应该返回一个这样的表:
name | distance
--------+-----------
Place_C | 12.0
Place_A | 10.0
Place_D | 8.0
我会存储所有地方的经度/经度,并编写一个函数来使用经度/经度信息计算它们之间的距离。
这样,无需计算要添加到数据库中的新地点的距离。
此外,如果您有很多地方,使用数据透视表仅存储距离,则必须注意该表可以增长得非常快。因为您需要涵盖所有地方的组合。
例如:对于 1000个位置,表中将有 1000 * 1000 - 1000 = 999000 行。对较大的数字进行数学计算,但此表可能包含很多行,具体取决于您有多少个位置。
将其分解为另一个称为"距离"的表,该表与原始"地点"表相关:
创建表距离(place_id_1 int、place_id_2 int、距离 int);
也就是说,对于每个地点,计算另一个地点的距离并将其保存在此新表中。
您可以创建一个新表,其中两列作为位置的外键,一列作为它们之间的距离。
|place1 | place2 | distance
-+-------|--------|---------
|.... |..... | .....
根据您拥有的位置数量,此表可能会增长非常快。
最简单的方法是制作另一个表,其中包含两个位置 id 和距离,例如
place1 place2 distance
a b 20
c d 30
在获取数据时,只需将其与位置表连接即可。
我认为这样的事情可以完成这项工作。
ORIGIN | CITY 1 | CITY 2 | CITY 3 | CITY 4 | CITY 5
+++++++++++++++++++++++++++++++++++++++++++++++++++++++
CITY 1 0 20 40 20
CITY 5 10 50 20 0
CITY 3 10 0 10 40
您可以轻松获取到其他地方的距离,并且不需要存储您知道的每个距离的城市名称。
SELECT 'CITY 2' FROM DISTANCES WHERE ORIGIN='CITY 5'