Is there a map of migration pathways?

Question

While in chat lamenting a really bad question that was migrated to a site because 3 people voted for it elsewhere, we got to wondering:

Is it possible to see a map of all the migration pathways in the vote to close lists?

I know moderators can move things anywhere, but what can the normal close-voters do? What clusters of sites are disconnected from others? Could a question on one site eventually find it's way to any other site?

Would it be possible to use the data.SE to pull migration options or would it require going through all the sites and building the list manually (for us regular, non-employee/developer folks)?

Answer 1

I'm not a data visualization expert so a map is beyond my reach. I have created a SEDE Query that produces an json data set that can be copied into the code for a Google Chart of the type Sankey (as I have done in the Stack Snippet below it).

SEDE Query

The query is a beast that iterates over all databases and executes a query in each database while storing its results in a temporary table. The temporary table is then post-processed to eliminate source-destination cross-duplicates and as final step there is a one column result rendered as a valid json array for easy copy-pasting.

-- create url from dbname
IF OBJECT_ID ( '#siteurl', 'P' ) IS NOT NULL 
    DROP PROCEDURE #siteurl;
GO

-- change StackExchange.StackOverflow.Meta  to Meta.StackOverflow.StackExchange
create procedure #siteurl
   @dbname nvarchar(250),
   @res nvarchar(250) OUT
   as
   begin
      declare @pos integer
      declare @rec nvarchar(250)
      set @res = @dbname
      set @pos = CHARINDEX('.', @dbname)
      if (@pos > 0 ) 
      begin
         set @rec = substring(@dbname, @pos+1, len(@dbname))
         exec #siteurl @rec,  @rec output  -- recursive call
         set @res = @rec 
                  + '.' 
                  + substring(@dbname, 0, @pos) 
      end
   end;
go

-- all databases
declare  db_c cursor for select [name] 
                         from sys.databases 
                         where database_id > 5 -- skip master, temp, model, msdb, Data.SE

declare @db_c_name sysname   -- holds name of db after fetch
declare @sql nvarchar(max) -- holds build up sql string

-- result table
create table #all_migrations ( [source] nvarchar(200)
                            , dest nvarchar(200)
                            , value int);
                            
open db_c
fetch next from db_c into @db_c_name
while(@@FETCH_STATUS = 0)
begin
    -- get the migrations for each db
    set @sql = N'use '+ QUOTENAME(@db_c_name) +';    
declare @http nvarchar  = N''http://''
declare @slash nvarchar = N''/''
declare @url nvarchar(250)
exec #siteurl ''' + @db_c_name  + ''', @url output
-- comment contains ''to http://host/question'' or ''from http://host/question''
-- the substring and charindex is used to only get the host part of the url
insert into #all_migrations 
select @url as [source]
     , substring(
          comment
          , charindex(@http, comment) +7
          , charindex(@slash ,comment, 
               charindex(@http, comment) +7)-
                  (charindex(@http, comment) +7)
           ) as dest
       , count(*) as [tot]     
from posthistory 
where posthistorytypeid in (35,36)
group by substring(comment
       , charindex(@http, comment) +7
       , charindex(@slash, comment, 
            charindex(@http, comment) +7)-
               (charindex(@http, comment) +7))
    '
     exec (@sql)
   fetch next from db_c into @db_c_name
end;
close db_c;
deallocate db_c;

-- process the results so src and dest strings are equal
-- get rid of .stackexchange and toplevel domain parts
declare  mig_c cursor for 
      select  replace(lower(source), '.stackexchange', '') as src
      , replace(
          replace(
              replace(dest, '.stackexchange.com' ,'')
              , '.net', '')
              , '.com', '') as dst
      ,  value
      from #all_migrations

-- result table
create table #final_migrations ( [source] nvarchar(250)
                                , dest nvarchar(250)
                                , value int);
declare @found int
declare @mig_c_src nvarchar(250)
declare @mig_c_dst nvarchar(250)
declare @mig_c_val int

-- remove mirrored rows
-- the chart component can't handle rows if a source/dest combination also 
-- exist in a dest/source row
-- so this is illegal in the result:
--  ['meta','superuser', 10] 
--  ['superuser', 'meta', 4]
-- I couldn't come up with a better solution (aka a select with a clever join)
-- above example would end up in the #final_migrations as
-- ['meta','superuser', 14] 
open mig_c
fetch next from mig_c into @mig_c_src, @mig_c_dst, @mig_c_val
while(@@FETCH_STATUS = 0)
begin
   
   -- check if we have a mirrored source/dest already
   select @found = count(*) 
   from #final_migrations
   where source = @mig_c_dst
   and dest = @mig_c_src;
   
   print @found   
   print @mig_c_src
   print @mig_c_dst;
    
   if @found > 0
   begin
      -- in theory migration from and to values should be equal
      -- we update the existing record
      update #final_migrations set value = value + @mig_c_val
      where source = @mig_c_dst
      and dest = @mig_c_src;
   end
   else
   begin
      -- no source/dest exist yet, insert it
      insert into #final_migrations values (@mig_c_src, @mig_c_dst, @mig_c_val)
   end
   
   fetch next from mig_c into @mig_c_src, @mig_c_dst, @mig_c_val
end
close mig_c
deallocate mig_c

-- generate javascript array ['meta','superuser', 14],
 select '['''
     + source
     + ''','''
     + dest
     + ''','
     + cast(value as nvarchar)
     + '],'
from #final_migrations
-- reduce the number of rows otherwise the chart component fails
where source not like 'meta.%'
and source not like 'meta'
and dest not like 'meta.%'
and dest not like 'meta'
and value > 50 

drop table #all_migrations
drop table #final_migrations
DROP PROCEDURE #siteurl;

Notice that the final query reduces the number of records returned. The chart component refused to render a larger set, claiming the data was not correct but I couldn't find the root-cause for that. A smaller set did work.

The per database query is basically build around the posthistory table that holds the migrations:

select comment
      , case when posthistorytypeid = 35 then 'away'
             when posthistorytypeid = 36 then 'here'
        end as here_away
from posthistory 
where posthistorytypeid in (35,36)

would give the following result:

comment                                        | here_away
----------------------------------------------------------
from http://superuser.com/questions/15044/ ... | here
to http://programmers.stackexchange.com/qu ... | away

Stack Snippet (javascript/google chart/html)

Here is the Stack Snippet that produces the Sankey diagram with a set that works. I indicated in the source code where the result of the query should be copied.

google.charts.load('current', {'packages':['sankey']});
google.setOnLoadCallback(drawChart);

function drawChart() {
    var data = new google.visualization.DataTable();
    data.addColumn('string', 'From');
    data.addColumn('string', 'To');
    data.addColumn('number', 'Weight');
    data.addRows([
/* this is copied from the SEDE Query result */
['ubuntu','serverfault',185],
['ubuntu','stackoverflow',691],
['ubuntu','superuser',51],
['ubuntu','unix',132],
['stats','economics',52],
['stats','machinelearning',200],
['stats','math',463],
['stats','mathoverflow',57],
['stats','stackoverflow',3550],
['webapps','apple',54],
['webapps','stackoverflow',420],
['webapps','superuser',3912],
['webapps','webmasters',477],
['webapps','wordpress',82],
['serverfault','security',374],
['serverfault','webmasters',680],
['serverfault','wordpress',52],
['serverfault','apple',228],
['serverfault','networkengineering',54],
['serverfault','superuser',11753],
['serverfault','dba',658],
['serverfault','sharepoint',64],
['serverfault','stackoverflow',14389],
['serverfault','programmers',60],
['serverfault','unix',1777],
['superuser','tex',55],
['superuser','security',877],
['superuser','gaming',167],
['superuser','programmers',180],
['superuser','unix',220],
['superuser','webmasters',426],
['superuser','askubuntu',113],
['superuser','dba',61],
['superuser','graphicdesign',139],
['superuser','stackoverflow',30444],
['superuser','android',801],
['superuser','apple',811],
['superuser','electronics',368],
['superuser','networkengineering',247],
['stackoverflow','gis',323],
['stackoverflow','magento',178],
['stackoverflow','programmers',12091],
['stackoverflow','unix',2363],
['stackoverflow','codereview',3069],
['stackoverflow','cs',401],
['stackoverflow','joomla',71],
['stackoverflow','math',434],
['stackoverflow','security',794],
['stackoverflow','crypto',278],
['stackoverflow','cstheory',56],
['stackoverflow','expressionengine',83],
['stackoverflow','tex',2751],
['stackoverflow','gamedev',666],
['stackoverflow','webmasters',4371],
['stackoverflow','windowsphone',82],
['stackoverflow','wordpress',1176],
['stackoverflow','askubuntu',62],
['stackoverflow','dba',7117],
['stackoverflow','drupal',933],
['stackoverflow','graphicdesign',133],
['stackoverflow','sharepoint',829],
['stackoverflow','android',669],
['stackoverflow','apple',602],
['stackoverflow','arduino',68],
['stackoverflow','codegolf',189],
['stackoverflow','electronics',206],
['stackoverflow','blender',75],
['stackoverflow','dsp',356],
['stackoverflow','mathematica',352],
['stackoverflow','ux',215],
['ell','english',1164],
['chemistry','physics',103],
['workplace','programmers',266],
['cs','cstheory',631],
['cs','math',83],
['cs','programmers',69],
['academia','math',55],
['sports','fitness',70],
['movies','scifi',128],
['scicomp','math',73],
['scicomp','physics',93],
['hermeneutics','christianity',247],
['linguistics','english',115],
['crypto','security',500],
['gardening','diy',173],
['pm','programmers',92],
['codereview','programmers',407],
['graphicdesign','photo',150],
['graphicdesign','ux',196],
['audio','photo',146],
['audio','sound',64],
['writers','english',383],
['money','economics',74],
['mathoverflow','math',1107],
['mathematica','math',430],
['skeptics','physics',61],
['scifi','literature',169],
['ux','programmers',169],
['diy','electronics',156],
['dba','programmers',97],
['security','healthcareit',77],
['security','programmers',155],
['security','unix',76],
['physics','electronics',185],
['physics','math',841],
['wordpress','webmasters',229],
['electronics','arduino',296],
['unix','askubuntu',244],
['unix','programmers',67],
['math','cstheory',61],
['programmers','webmasters',72],
['astronomy','space',90],
['sound','avp',4086],
['sound','video',652],
/* end copy of SEDE Query*/
    ]);
    
    // Set chart options
    var options = {
      width: 1280,
    };

    // Instantiate and draw our chart, passing in some options.
    var chart = new google.visualization.Sankey(document.getElementById('sankey_basic'));
    chart.draw(data, options);
   }

<script type="text/javascript"
  src="https://www.gstatic.com/charts/loader.js">
</script>

<div id="sankey_basic" style="width: 980px; height: 720px;"></div>

Update 2020

google.charts.load('current', {'packages':['sankey']});
google.setOnLoadCallback(drawChart);

function drawChart() {
    var data = new google.visualization.DataTable();
    data.addColumn('string', 'From');
    data.addColumn('string', 'To');
    data.addColumn('number', 'Weight');
    data.addRows([
/* this is copied from the SEDE Query result */
['dba','programmers',107],                       
['dba','serverfault',848],                       
['dba','superuser',111],                         
['dba','stackoverflow',8994],                    
['wordpress','webapps',79],                      
['wordpress','serverfault',63],                  
['wordpress','superuser',108],                   
['wordpress','webmasters',280],                  
['wordpress','stackoverflow',1396],              
['gis','opendata',114],                          
['gis','stackoverflow',465],                     
['opendata','datascience',182],                  
['apple','unix',55],                             
['apple','webapps',76],                          
['apple','stackoverflow',797],                   
['apple','serverfault',266],                     
['apple','superuser',1022],                      
['music','musicfans',216],                       
['music','sound',97],                            
['programmers','english',57],                    
['programmers','ux',178],                        
['programmers','webmasters',82],                 
['programmers','pm',104],                        
['programmers','unix',67],                       
['programmers','serverfault',67],                
['programmers','superuser',185],                 
['programmers','workplace',283],                 
['programmers','codereview',492],                
['programmers','cs',97],                         
['programmers','security',162],                  
['programmers','stackoverflow',11001],           
['bitcoin','crypto',51],                         
['physics','hsm',150],                           
['physics','scicomp',140],                       
['physics','space',66],                          
['physics','astronomy',61],                      
['physics','skeptics',76],                       
['physics','biology',68],                        
['physics','chemistry',193],                     
['physics','math',2101],                         
['physics','academia',55],                       
['physics','electronics',462],                   
['joomla','stackoverflow',87],                   
['literature','english',55],                     
['linguistics','english',231],                   
['law','money',86],                              
['law','opensource',97],                         
['law','politics',152],                          
['law','security',64],                           
['expressionengine','stackoverflow',98],         
['ux','graphicdesign',341],                      
['scicomp','math',85],                           
['scicomp','stackoverflow',78],                  
['cstheory','cs',698],                           
['cstheory','math',65],                          
['cstheory','stackoverflow',59],                 
['chemistry','biology',100],                     
['politics','history',57],                       
['gamedev','gaming',56],                         
['gamedev','stackoverflow',683],                 
['diy','gardening',263],                         
['diy','woodworking',75],                        
['diy','electronics',896],                       
['expatriates','travel',844],                    
['ai','datascience',94],                         
['ai','stackoverflow',73],                       
['webmasters','security',53],                    
['webmasters','webapps',654],                    
['webmasters','serverfault',789],                
['webmasters','superuser',512],                  
['webmasters','stackoverflow',4580],             
['ux','stackoverflow',258],                      
['networkengineering','serverfault',104],        
['cs','superuser',65],                           
['cs','math',216],                               
['cs','stackoverflow',823],                      
['networkengineering','superuser',330],          
['quant','stats',63],                            
['sharepoint','serverfault',63],                 
['sharepoint','stackoverflow',839],              
['stats','datascience',76],                      
['stats','mathoverflow',118],                    
['stats','economics',85],                        
['stats','machinelearning',200],                 
['stats','math',876],                            
['stats','academia',53],                         
['stats','stackoverflow',7854],                  
['math','dsp',70],                               
['math','matheducators',62],                     
['math','hsm',52],                               
['math','crypto',76],                            
['math','stackoverflow',563],                    
['math','academia',78],                          
['math','math.meta',81],                         
['math','mathoverflow',2296],                    
['math','mathematica',937],                      
['philosophy','english',57],                     
['security','tor',76],                           
['security','unix',135],                         
['security','healthcareit',77],                  
['security','serverfault',874],                  
['security','superuser',2416],                   
['security','crypto',1217],                      
['security','stackoverflow',1423],               
['health','biology',114],                        
['crypto','stackoverflow',582],                  
['crypto','superuser',160],                      
['opensource','softwareengineering',64],         
['blender','stackoverflow',178],                 
['sports','fitness',101],                        
['travel','aviation',72],                        
['garage','diy',55],                             
['garage','electronics',267],                    
['softwarerecs','android',57],                   
['softwarerecs','superuser',116],                
['softwarerecs','webapps',69],                   
['biology','gardening',54],                      
['writers','english',604],                       
['writers','techcomm',162],                      
['space','astronomy',328],                       
['dsp','electronics',61],                        
['dsp','stackoverflow',411],                     
['audio','graphicdesign',85],                    
['audio','photo',218],                           
['audio','sound',64],                            
['gaming','superuser',189],                      
['mathematica','stackoverflow',370],             
['academia','openscience',70],                   
['webapps','stackoverflow',491],                 
['webapps','android',110],                       
['webapps','superuser',5442],                    
['travel','outdoors',53],                        
['codereview','stackoverflow',4388],             
['hermeneutics','christianity',642],             
['english','ell',7821],                          
['english','writing',255],                       
['codegolf','stackoverflow',128],                
['movies','scifi',221],                          
['raspberrypi','electronics',178],               
['raspberrypi','stackoverflow',187],             
['raspberrypi','superuser',105],                 
['raspberrypi','unix',119],                      
['superuser','unix',376],                        
['superuser','windowsphone',70],                 
['superuser','meta',67],                         
['superuser','tex',58],                          
['superuser','stackoverflow',31855],             
['superuser','graphicdesign',268],               
['superuser','askubuntu',200],                   
['superuser','serverfault',13695],               
['superuser','android',1572],                    
['superuser','electronics',987],                 
['stackoverflow','meta',5082],                   
['stackoverflow','ru.stackoverflow',81],         
['stackoverflow','tex',3031],                    
['stackoverflow','askubuntu',75],                
['stackoverflow','datascience',338],             
['stackoverflow','arduino',196],                 
['stackoverflow','electronics',343],             
['stackoverflow','drupal',935],                  
['stackoverflow','graphicdesign',144],           
['stackoverflow','windowsphone',92],             
['stackoverflow','magento',238],                 
['stackoverflow','softwareengineering',168],     
['stackoverflow','unix',4040],                   
['stackoverflow','android',681],                 
['stackoverflow','serverfault',14511],           
['stackoverflow','tridion',52],                  
['tex','tex.meta',107],                          
['graphicdesign','photo',154],                   
['unix','askubuntu',386],                        
['unix','serverfault',2898],                     
['sound','video',661],                           
['sound','avp',4041],                            
['stackapps','meta',99],                         
['economics','money',127],                       
['arduino','electronics',1632],                  
['engineering','electronics',121],               
['engineering','embedded',57],                   
['ubuntu','unix',176],                           
['ubuntu','serverfault',234],                    
['ubuntu','superuser',58],                       
['ubuntu','stackoverflow',820],                  
['ru.stackoverflow','ru.meta.stackoverflow',63], 
/* end copy of SEDE Query*/
    ]);
    
    // Set chart options
    var options = {
      width: 1280,
    };

    // Instantiate and draw our chart, passing in some options.
    var chart = new google.visualization.Sankey(document.getElementById('sankey_basic'));
    chart.draw(data, options);
   }

<script type="text/javascript"
  src="https://www.gstatic.com/charts/loader.js">
</script>

<div id="sankey_basic" style="width: 980px; height: 720px;"></div>