16

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

  • 1
    A cool way to visualize it would be to have the size of the sites on the map be relative to their activity (questions per day for example) and show the migration connections weighted by their relative use between sites... But that's a secondary request! – tpg2114 Jan 29 '15 at 21:14
  • Would that include all of the meta sites too? Or just this meta site? – Jim Jan 29 '15 at 21:16
  • 1
    I would say only this one, although if some weighting is indicated it might be useful to show how many questions on a site end up being intended for the meta. I have a few ideas on how to visualize that too without cluttering the map much – tpg2114 Jan 29 '15 at 21:18
16

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, what I have done in the StackSnippet below, but first the ...

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

StackSnippet (javascript/google chart/html)

Here is the StackSnippet 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.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.google.com/jsapi?autoload={'modules':[{'name':'visualization','version':'1.1','packages':['sankey']}]}">
</script>

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

  • Okay, this is awesome and now I have something to play with! So the way the query works is based on migrations that actually happened, right? And not migrations that could happen but haven't been used yet? I'm not sure if there are pathways available but never used, but who knows. Also -- does it include all migrations (including those from moderators, which can go to any site) or just the migrations possible from the vote-to-close screen? – tpg2114 Jan 31 '15 at 19:55
  • That is right, these are migrations that did happen. SEDE doesn't have info on non-complete closes questions. – rene Jan 31 '15 at 20:05
  • OKay, cool. I'll spend a few days (when not working) trying to visualize/cluster the data. Fortunately the output format is perfect for Python too as a list-of-lists format. I'll see what I can come up with! – tpg2114 Jan 31 '15 at 20:17
  • Oh my god! So much data in such a small area. So hard to see the big picture from this but so detailed. Well done! – Jim Feb 2 '15 at 14:46

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .