Select all countries that can be found by recursively adding all neighbouring countries (PostgreSQL), PostgreSQL

Select all countries that can be found by recursively adding all neighbouring countries (PostgreSQL)

-- SO question http://stackoverflow.com/questions/43136590/finding-all-entries-that-can-be-found-by-recursive-selection
create table borders(
    Country1 varchar(100),
    Country2 varchar(100)
);
insert into borders(Country1,Country2)
values
('Sweden','Finland'),
('Norway','Sweden'),
('Norway','Finland'),
('Norway','Russia'),
('Russia','Ukraine'),
('Russia','Finland'),
('Russia','China'),
('Canada','United States')
;

WITH RECURSIVE neighbours AS (
    -- start with 'Sweden' if any neighbour of it exists
    SELECT DISTINCT 'Sweden' as Name, ARRAY[cast('Sweden' as varchar)] as path
    FROM borders b 
    WHERE 'Sweden' IN (b.Country1, b.Country2)
    --
    UNION ALL
    SELECT 
      CASE WHEN nb.Name = b.Country1 THEN b.Country2 ELSE b.Country1 END, 
      path || CASE WHEN nb.Name = b.Country1 THEN b.Country2 ELSE b.Country1 END
    FROM borders b
    JOIN neighbours nb ON nb.Name IN(b.Country1, b.Country2) 
          AND NOT(b.Country1 = ANY(path) AND b.Country2 = ANY(path))
)
SELECT DISTINCT Name
FROM neighbours;


run \| edit \| history \| help	0

Please log in to post a comment.