0

There is a table distances(city1, city2, dist) that list couple of cities and distances between them. The deal is, that from this information we can get more distances, like there is in table distances dist A -> B and dist B -> C so we can get A -> C that is not in the distances.

Task is to generate table distances_tr(city1, city2, dist) with transitive closure of the 1st table, that will include (A, C, dist(A,C)).

How can I achive that using PROCEDURE and simple SQL SELECT, INSERT, UPDATE commands? I know it can be done recursively, but Professor told us to do it both ways.

  • Are you only looking for second-order distance calculations or is this the travelling salesman problem? (BTW there's a tag for that) – symcbean Nov 17 '15 at 21:58

2 Answers2

0

Join the table to itself on city2 from one side to city1 on the other. The assumption here is that there are no records where the combination of city1 and city2 are in there twice with the cities flipped. If they are, include a filter to filter out where city1 on one side is equal to city2 on the other.

Rabbit
  • 507
  • 3
  • 9
  • But if in `distances` there is distance A-B, B-C and C-D then with your method I will get only A-C (A-B-C) and B-D (B-C-D), yes? But it will also be possible to generate distance from A-D (A-B-C-D) –  Nov 17 '15 at 22:11
0

Create a procedure that first copies distances into distances_tr, then repeatedly inserts the join of distances with distances_tr until no more rows are updated. Make sure you properly define the primary key on the combination of city1 and city2, and when inserting rows with duplicate keys, you can update distances_tr with the least of the existing value and the sum of distances from the join.

reaanb
  • 9,806
  • 2
  • 23
  • 37