sqlpostgresqlpgrouting

Postgresql remove duplicate reversed pairs


I have this table:

 origin     destination

new york      seattle
new york      chicago
new york      portland
seattle       new york
seattle       chicago
chicago       new york

I have to build a graph so I need to remove all the duplicated reversed pairs to have:

origin      destination oneway

new york      seattle     0
new york      chicago     0
new york      portland    1
seattle       chicago     1

I already read this post: SQL -- Remove duplicate pairs but it's not useful for me because I have string filed.

Thanks


Solution

  • One method uses aggregation:

    select origin, destination,
           (case when exists (select 1
                              from t t2
                              where t2.origin = t.destination and t2.destination = t.origin
                             )
                 then 0 else 1
            end) as one_way
    from t
    where origin < destination
    union all
    select origin, destination, 1
    from t
    where origin > destination;
    

    An alternative method uses window functions:

    select origin, destination, (cnt = 1)::int as one_way
    from (select t.*,
                 count(*) over (partition by least(origin, destination), greatest(origin, destination)) as cnt
          from t
         ) t
    where origin < destination or
          (origin > destination and cnt = 1);