How to store distances between places?

Question

I have a database containing places, and I need to show the distances from any place to another on my web page. Having distances saved somewhere will save a lot of work (loading them should be easier than calculating them again). But how to save a square matrix of distances? Creating a new column every time I insert a new row does not seem to be a good solution, but I did not find a better solution (although I might think of workarounds such as calculating about 10 or 20 closest distances and assuming that I rarely need more).

What is the best way to save square tables with variable (and growing) size in PHP / MySQL? Or is there no good solution, and my (or other) workaround is better?

Answer 1

Change Note. As mentioned in the commentary, once you get enough seats, it may make sense to just store long / lat values ​​and calculate the distance on the fly based on these factors. However, the solution given here may still be relevant to other applications.

---

The best way to handle this is with a pivot table, where each row has two place identifiers and a distance value.

Now, since the distance AB is the same as BA, we only need to save each pairing once. We can only do this by keeping a distance if the identifier A is less than B.

---

Customization

First a places table to store your places

 id | name ---+--------- 1 | Place_A 2 | Place_B 3 | Place_C 4 | Place_D 

Then a places_distances pivot table:

 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 

Note that pivot tables do not require their own identifier field (although some may argue that it is still useful to have it sometimes). You will configure a unique key as follows (you want to see the documentation for proper use):

 UNIQUE KEY `UNIQUE_placesDistances_primary`(`place_id_1`,`place_id_2`) 

This ensures that you cannot have the same place / place in the table twice.

You will also want to set foreign keys:

 CONSTRAINT FOREIGN KEY `FK_placesDistances_place1` (`place_id_1`) REFERENCES `places`(`id`), CONSTRAINT FOREIGN KEY `FK_placesDistances_place2` (`place_id_2`) REFERENCES `places`(`id`) 

This ensures that you can only add entries for the place that you actually defined in places . it also means (if you use the default foreign key behavior) that you cannot delete a place if you have a distance string referencing that place.

---

Examples of using

Finding the distance between two places

(Given the two variables @id_1 as the identifier of the first place and @id_2 as the identifier of the second place)

 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; 

We use OR to take into account the case when we try to find a distance of 2 to 1 , and not 1 to 2 - remember, we only save values ​​where the identifier of the first place is less than the second to avoid duplicates.

---

Insert new distance

(Given the three variables @id_1 as the identifier of the first place and @id_2 as the identifier of the second place, and @distance is the distance)

 INSERT `places_distances`(`place_id_1`,`place_id_2`,`distance`) VALUES(LEAST(@id_1, @id_2),GREATEST(@id_1, @id_2), @distance) 

We use the built-in LEAST and GREATEST comparison functions to help maintain our rule that we only save places where the first identifier is less than the second to avoid duplication.

---

Displays a list of place names sorted by their distance from the farthest to the nearest

To get the original names from the places table to display places_distances in our query, we need to combine them. In this case, LEFT JOIN is the best choice, since we only care about what is in the places_distances table. Learn more about connecting MySQL 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 

Which should return the table as follows:

 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 

---

showing a table of places and their distance to a specific place

This is a bit more complicated, since we need to show the name in a line that is not our input location, but we can use another useful IF(CONDITION,'TRUE_OUTPUT','FALSE_OUTPUT') function IF(CONDITION,'TRUE_OUTPUT','FALSE_OUTPUT') for this.

( @place_name is a variable containing the name of the place, in this case "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 

Which should return the table as follows:

  name | distance --------+----------- Place_C | 12.0 Place_A | 10.0 Place_D | 8.0 

Answer 2

I would save lat / long for all places and write a function to calculate the distance between them using lat / long.

Thus, there is no need to calculate distances for new places that you want to add to your database.

In addition, if you have many places, using a pivot table to store only distances, you should know that this table can grow very quickly. Because you need to cover all combinations of places.

For example: for 1000 places you will have 1000 * 1000 - 1000 = 999000 rows in the table. Do the math for a larger number, but this table can contain many rows, depending on how many places you have.

Answer 3

Divide it into another table called “distance”, which refers to the original “Place” table:

create a distance in the table (place_id_1 int, place_id_2 int, distance int);

That is, for each place, calculate the distance for another place and save it in this new table.

Answer 4

You can create a new table with two columns as foreign keys for locations and one column for the distance between them.

  |place1 | place2 | distance -+-------|--------|--------- |.... |..... | ..... 

Depending on how many places you have, this table can grow very quickly.

Answer 5

The easiest way is to make another table that will contain two id places and a distance like

 place1 place2 distance ab 20 cd 30 

while fetching data, just attach it to the place table.

Answer 6

I think something like this can do the job.

  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 

You can easily get distances to other places, and you do not need to save the names of cities at every distance that you know.

 SELECT 'CITY 2' FROM DISTANCES WHERE ORIGIN='CITY 5'