1

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.

The table BAR_POS stores the following info of bars around my area:

  • unique_id (integer)
  • bar_name (text)
  • latitude (real)
  • longitude (real)

I have created a function DIST_SCORE() that can approximately evaluate the distance given a pair of latitude and longitude values.

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):

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 > B.id
    ORDER BY dist;

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.

Assuming the initial bar has a unique_id = 14989798357:

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

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.

Here is the first few lines of the dump of the BAR_POS table:

sqlite> .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 & 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);
testing109385
  • 11
  • 2
  • Can you show a `.dump` of a suitably tailored toy database? – Yunnosch May 29 '17 at 18:36
  • Would you be satisfied by (I read your question this way) a list where the distance between one node and the next is always the shortest of all remaining nodes? Or do you want the more complex (and not always the same) list which has the shortest total distance, i.e. the traveling salesman answer? – Yunnosch May 29 '17 at 18:45
  • I am NOT interested in the traveling salesman answer. I would like the distance between one node and the next be always the shortest of all remaining nodes. Thank you so much for asking such good clarification question!!!! – testing109385 May 29 '17 at 20:30
  • Thanks for confirming, sorry for being unsure (four exclamation marks seem annoyed). How about a `.dump` now? – Yunnosch May 29 '17 at 20:35
  • And nope, my four exclamation marks was just my way of expressing gratitude, i.e. I was not annoyed. This my first time asking a question on stack overflow and I should learn the proper internet culture/etiquette here. Thank you for pointing that out as well. – testing109385 May 29 '17 at 22:15
  • Ok. There is always the danger of misunderstanding emotions, when you do not see the others face; and it is hard to predict who might misunderstand what. In my opinion you are doing well, keeping up communication and simply clarifying what seems to have been misunderstood. By the way, I played with your question and have to admit it is not as easy as I thought. I might come back to it. – Yunnosch May 30 '17 at 05:37

0 Answers0