我有一个包含地点的数据库，我需要在我的网页中显示从任何地点到其他地点的距离。将距离存储在某处将节省大量工作(加载它们应该比重新计算它们更容易)。但是如何保存距离的方阵呢？每次插入新行时都创建一个新列似乎不是一个好的解决方案，但我没有找到更好的解决方案(尽管我可以想到一些变通方法，例如计算大约 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 字段(尽管有些人可能会争辩说，有时拥有一个仍然很好)。您将按如下方式设置唯一 key (您需要查看 documentation 以了解正确用法):

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)

我们正在使用内置的 comparison functions LEAST 和 GREATEST 有助于维护我们的规则，即我们只存储第一个 ID 小于第二个的位置，以避免重复。

显示地名列表，按距离从远到近排序

要从 places 表中获取原始名称以显示在我们的 places_distances 查询中，我们必须将它们连接在一起。在这种情况下，LEFT JOIN 是最佳选择，因为我们只关心 places_distances 表中的内容。 For more info on MySQL joins check here .

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

关于php - 如何存储地点之间的距离？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/18786193/