title: SQLite: How to return a list of nodes in which traveling distance from one node to the next is the shortest (visiting only each node once)?
body: <p>Problem statement: Given an initial bar position, return a list of bars in which the traveling distance from one bar to another is the shortest, without visiting the same bar twice.</p>
  
  <p>The table BAR_POS stores the following info of bars around my area:</p>
  
  <ul>
  <li>unique_id (integer)</li>
  <li>bar_name (text)</li>
  <li>latitude (real)</li>
  <li>longitude (real)</li>
  </ul>
  
  <p>I have created a function DIST_SCORE() that can approximately evaluate the distance given a pair of latitude and longitude values.</p>
  
  <p>I even computed a temp view that stores all distances between all pairs of bars (there were only ~100 bars in the BAR_POS table):</p>
  
  <pre><code>CREATE TEMP VIEW 
  dist_matrix(idA, idB, dist) AS 
      SELECT A.id, B.id, dist_score(A.lat, A.lon, B.lat,B.lon) AS dist
      FROM bar_pos AS A, bar_pos AS B 
      WHERE A.id &gt; B.id
      ORDER BY dist;
  </code></pre>
  
  <p>I thought of using a recursive query, but it does not allow the use of MIN(), an aggregate function, in the recursive select statement, as mentioned in the documentation. So the following attempt does not work! But i hope it gives you an idea of what I'm trying to accomplish.</p>
  
  <p>Assuming the initial bar has a unique_id = 14989798357:</p>
  
  <pre><code>WITH RECURSIVE result_tb(id, dist, counter) AS (
         SELECT D.idA+D.idB-14989798357, MIN(D.dist), 0
         FROM dist_matrix AS D
         WHERE D.idA = '14989798357' OR D.idB = '14989798357'
  
             UNION ALL
  
         SELECT D.idA+D.idB-result_tb.id, MIN(D.dist), result_tb.counter+1
         FROM dist_matrix AS D, result_tb
         WHERE D.idA = result_tb.id OR D.idB = result_tb.id        
         )
  
  SELECT R.id, B.name
  FROM result_tb AS R, bar_pos AS B
  WHERE R.id = B.id
  </code></pre>
  
  <p>So is there a pure SQLite way of solving this problem? I know that I can solve this problem easily with other programming languages, but I am learning SQLite right now and would like to learn as much as possible from SQLite.</p>
  
  <p>Here is the first few lines of the dump of the BAR_POS table:</p>
  
  <pre><code>sqlite&gt; .dump bar_pos
  PRAGMA foreign_keys=OFF;
  BEGIN TRANSACTION;
  CREATE TABLE bar_pos(id INT,name TEXT,lat REAL,lon REAL);
  INSERT INTO "bar_pos" VALUES(1127248835,'The Lobby Bar',47.6142099,-122.3198397);
  INSERT INTO "bar_pos" VALUES(1194793097,'Comet Tavern',47.6142655,-122.3197336);
  INSERT INTO "bar_pos" VALUES(1250696405,'Poco Wine Room',47.6154362,-122.3135746);
  INSERT INTO "bar_pos" VALUES(1332014994,'The Saint',47.6176017,-122.3265279);
  INSERT INTO "bar_pos" VALUES(1332014999,'Speckled &amp; Drake',47.6166484,-122.3275833);
  INSERT INTO "bar_pos" VALUES(1332015004,'Knee High Stocking Co.',47.616968,-122.327413);
  INSERT INTO "bar_pos" VALUES(1387947219,'Unicorn Bar',47.6142292,-122.3171081);
  INSERT INTO "bar_pos" VALUES(1539274991,'Liberty',47.6235665,-122.3128271);
  INSERT INTO "bar_pos" VALUES(1607121865,'Bathtub',47.6130973,-122.3449673);
  INSERT INTO "bar_pos" VALUES(1735895574,'Artusi',47.615173,-122.3143967);
  INSERT INTO "bar_pos" VALUES(1836754564,'Pioneer Square Saloon',47.6016146,-122.3354445);
  INSERT INTO "bar_pos" VALUES(1999272109,'Nitelite',47.611501,-122.3410283);
  INSERT INTO "bar_pos" VALUES(2053744069,'Contour',47.6034597,-122.3353422);
  INSERT INTO "bar_pos" VALUES(2077248890,'Linda''s',47.6150977,-122.3230019);
  INSERT INTO "bar_pos" VALUES(2131716660,'Julia''s',47.621253,-122.3206536);
  </code></pre>