Creates a table containing city connections for dyads

create_city_connections.sql

select ST_MAKELINE(ARRAY[St_Setsrid(st_centroid(dc1.geom),4326), st_setsrid(st_centroid(dc2.geom),4326)]), dc1.name_2 as source,dc2.name_2 as target, (sum(dd.is_friend_ratio)/(dc1.rf_population+dc2.rf_population)::float) as weight, count(*) as cou
into presentation_city_connections
from derived_dyads dd
inner join user_residence ur1 on dd.user_a=ur1.user_id
inner join dk_cities dc1 on dc1.gid=ur1.city_id
inner join user_residence ur2 on dd.user_b=ur2.user_id
inner join dk_cities dc2 on dc2.gid=ur2.city_id
where dc1.name_2!=dc2.name_2
group by dc1.name_2,dc2.name_2,dc1.rf_population, dc2.rf_population,dc1.geom, dc2.geom;