Difference between revisions of "iRefIndex/PBR Notes"

From irefindex
(Added interactors.)
(→‎Queries: Restrict to 9606 interactors.)
Line 50: Line 50:
 
   on i5_mitab_sources.rigid = mitab_sources.rigid
 
   on i5_mitab_sources.rigid = mitab_sources.rigid
 
   and i5_mitab_sources.name = mitab_sources.name
 
   and i5_mitab_sources.name = mitab_sources.name
  where mitab_sources.name = 'bind';
+
inner join i5_mitab_interactions
 +
  on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
 +
  and i5_mitab_sources.uidB = i5_mitab_interactions.uidB
 +
  and i5_mitab_sources.rigid = i5_mitab_interactions.rigid
 +
  where mitab_sources.name = 'bind'
 +
  and taxA = 9606
 +
  and taxB = 9606;
  
 
Interactions only in iRefIndex 5.1:
 
Interactions only in iRefIndex 5.1:
  
  select count(distinct rigid)
+
  select count(distinct i5_mitab_sources.rigid)
 
  from i5_mitab_sources
 
  from i5_mitab_sources
 +
inner join i5_mitab_interactions
 +
  on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
 +
  and i5_mitab_sources.uidB = i5_mitab_interactions.uidB
 +
  and i5_mitab_sources.rigid = i5_mitab_interactions.rigid
 
  where i5_mitab_sources.name = 'bind'
 
  where i5_mitab_sources.name = 'bind'
   and rigid not in (select rigid from mitab_sources where mitab_sources.name = 'bind');
+
   and taxA = 9606
 +
  and taxB = 9606
 +
  and i5_mitab_sources.rigid not in (select rigid from mitab_sources where mitab_sources.name = 'bind');
  
 
Interactions only in BIND PSI-XML:
 
Interactions only in BIND PSI-XML:
Line 64: Line 76:
 
  from mitab_sources
 
  from mitab_sources
 
  where mitab_sources.name = 'bind'
 
  where mitab_sources.name = 'bind'
   and rigid not in (select rigid from i5_mitab_sources where i5_mitab_sources.name = 'bind');
+
   and rigid not in (
 +
  select count(distinct i5_mitab_sources.rigid)
 +
  from i5_mitab_sources
 +
  inner join i5_mitab_interactions
 +
    on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
 +
    and i5_mitab_sources.uidB = i5_mitab_interactions.uidB
 +
    and i5_mitab_sources.rigid = i5_mitab_interactions.rigid
 +
  where i5_mitab_sources.name = 'bind'
 +
    and taxA = 9606
 +
    and taxB = 9606
 +
  );
  
 
Common interactors:
 
Common interactors:
  
 
  select count(distinct X.uid)
 
  select count(distinct X.uid)
  from (select uidA as uid from i5_mitab_sources where name = 'bind' union select uidB as uid from i5_mitab_sources where name = 'bind') as X
+
  from (
  inner join (select uidA as uid from mitab_sources where name = 'bind' union select uidB as uid from mitab_sources where name = 'bind') as Y
+
  select i5_mitab_sources.uidA as uid
 +
  from i5_mitab_sources
 +
  inner join i5_mitab_interactions
 +
    on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
 +
  where name = 'bind' and taxA = 9606
 +
  union
 +
  select i5_mitab_sources.uidB as uid
 +
  from i5_mitab_sources
 +
  inner join i5_mitab_interactions
 +
    on i5_mitab_sources.uidB = i5_mitab_interactions.uidB
 +
  where name = 'bind' and taxB = 9606
 +
  ) as X
 +
  inner join (
 +
  select uidA as uid
 +
  from mitab_sources
 +
  where name = 'bind'
 +
  union
 +
  select uidB as uid
 +
  from mitab_sources
 +
  where name = 'bind'
 +
  ) as Y
 
   on X.uid = Y.uid;
 
   on X.uid = Y.uid;
  
Line 76: Line 118:
  
 
  select count(distinct X.uid)
 
  select count(distinct X.uid)
  from (select uidA as uid from i5_mitab_sources where name = 'bind' union select uidB as uid from i5_mitab_sources where name = 'bind') as X
+
  from (
 +
  select i5_mitab_sources.uidA as uid
 +
  from i5_mitab_sources
 +
  inner join i5_mitab_interactions
 +
    on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
 +
  where name = 'bind' and taxA = 9606
 +
  union
 +
  select i5_mitab_sources.uidB as uid
 +
  from i5_mitab_sources
 +
  inner join i5_mitab_interactions
 +
    on i5_mitab_sources.uidB = i5_mitab_interactions.uidB
 +
  where name = 'bind' and taxB = 9606
 +
  ) as X
 
  where X.uid not in (select uidA as uid from mitab_sources where name = 'bind' union select uidB as uid from mitab_sources where name = 'bind');
 
  where X.uid not in (select uidA as uid from mitab_sources where name = 'bind' union select uidB as uid from mitab_sources where name = 'bind');
  
Line 83: Line 137:
 
  select count(distinct X.uid)
 
  select count(distinct X.uid)
 
  from (select uidA as uid from mitab_sources where name = 'bind' union select uidB as uid from mitab_sources where name = 'bind') as X
 
  from (select uidA as uid from mitab_sources where name = 'bind' union select uidB as uid from mitab_sources where name = 'bind') as X
  where X.uid not in (select uidA as uid from i5_mitab_sources where name = 'bind' union select uidB as uid from i5_mitab_sources where name = 'bind');
+
  where X.uid not in (
 +
  select i5_mitab_sources.uidA as uid
 +
  from i5_mitab_sources
 +
  inner join i5_mitab_interactions
 +
    on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
 +
  where name = 'bind' and taxA = 9606
 +
  union
 +
  select i5_mitab_sources.uidB as uid
 +
  from i5_mitab_sources
 +
  inner join i5_mitab_interactions
 +
    on i5_mitab_sources.uidB = i5_mitab_interactions.uidB
 +
  where name = 'bind' and taxB = 9606
 +
  );
  
 
== Other Stuff ==
 
== Other Stuff ==

Revision as of 13:41, 21 September 2009

Preliminary BIND PSI-XML parsing results.

Results

Importing MITAB output from the iRefIndex 5.1 build and a build done using the BIND PSI-XML data, then comparing the interaction details for BIND, the following distributions are observed:

Interactions

iRefIndex 5.1
(Total) (9811)
3818
PSI-XML (8916) 2923 5993

To summarise:

  • Common interactions: 5993
  • Interactions only in iRefIndex 5.1: 3818
  • Interactions only in BIND PSI-XML: 2923

Interactors

iRefIndex 5.1
(Total) (6896)
2511
PSI-XML (5324) 939 4385

To summarise:

  • Common interactors: 4385
  • Interactors only in iRefIndex 5.1: 2511
  • Interactors only in BIND PSI-XML: 939

Queries

Common interactions:

select count(distinct mitab_sources.rigid)
from i5_mitab_sources
inner join mitab_sources
  on i5_mitab_sources.rigid = mitab_sources.rigid
  and i5_mitab_sources.name = mitab_sources.name
inner join i5_mitab_interactions
  on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
  and i5_mitab_sources.uidB = i5_mitab_interactions.uidB
  and i5_mitab_sources.rigid = i5_mitab_interactions.rigid
where mitab_sources.name = 'bind'
  and taxA = 9606
  and taxB = 9606;

Interactions only in iRefIndex 5.1:

select count(distinct i5_mitab_sources.rigid)
from i5_mitab_sources
inner join i5_mitab_interactions
  on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
  and i5_mitab_sources.uidB = i5_mitab_interactions.uidB
  and i5_mitab_sources.rigid = i5_mitab_interactions.rigid
where i5_mitab_sources.name = 'bind'
  and taxA = 9606
  and taxB = 9606
  and i5_mitab_sources.rigid not in (select rigid from mitab_sources where mitab_sources.name = 'bind');

Interactions only in BIND PSI-XML:

select count(distinct rigid)
from mitab_sources
where mitab_sources.name = 'bind'
  and rigid not in (
  select count(distinct i5_mitab_sources.rigid)
  from i5_mitab_sources
  inner join i5_mitab_interactions
    on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
    and i5_mitab_sources.uidB = i5_mitab_interactions.uidB
    and i5_mitab_sources.rigid = i5_mitab_interactions.rigid
  where i5_mitab_sources.name = 'bind'
    and taxA = 9606
    and taxB = 9606
  );

Common interactors:

select count(distinct X.uid)
from (
  select i5_mitab_sources.uidA as uid
  from i5_mitab_sources
  inner join i5_mitab_interactions
    on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
  where name = 'bind' and taxA = 9606
  union
  select i5_mitab_sources.uidB as uid
  from i5_mitab_sources
  inner join i5_mitab_interactions
    on i5_mitab_sources.uidB = i5_mitab_interactions.uidB
  where name = 'bind' and taxB = 9606
  ) as X
inner join (
  select uidA as uid
  from mitab_sources
  where name = 'bind'
  union
  select uidB as uid
  from mitab_sources
  where name = 'bind'
  ) as Y
  on X.uid = Y.uid;

Interactors only in iRefIndex 5.1:

select count(distinct X.uid)
from (
  select i5_mitab_sources.uidA as uid
  from i5_mitab_sources
  inner join i5_mitab_interactions
    on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
  where name = 'bind' and taxA = 9606
  union
  select i5_mitab_sources.uidB as uid
  from i5_mitab_sources
  inner join i5_mitab_interactions
    on i5_mitab_sources.uidB = i5_mitab_interactions.uidB
  where name = 'bind' and taxB = 9606
  ) as X
where X.uid not in (select uidA as uid from mitab_sources where name = 'bind' union select uidB as uid from mitab_sources where name = 'bind');

Interactors only in BIND PSI-XML:

select count(distinct X.uid)
from (select uidA as uid from mitab_sources where name = 'bind' union select uidB as uid from mitab_sources where name = 'bind') as X
where X.uid not in (
  select i5_mitab_sources.uidA as uid
  from i5_mitab_sources
  inner join i5_mitab_interactions
    on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
  where name = 'bind' and taxA = 9606
  union
  select i5_mitab_sources.uidB as uid
  from i5_mitab_sources
  inner join i5_mitab_interactions
    on i5_mitab_sources.uidB = i5_mitab_interactions.uidB
  where name = 'bind' and taxB = 9606
  );

Other Stuff

This is what I did for the unnecessary "new BIND vs. other sources" experiment...

Downloaded the following:

  • BioGrid
  • CORUM
  • DIP
  • HPRD
  • Intact
  • MINT
  • MIPS (MPPI)
  • OPHID

Modified the following:

  • BioGrid - removed all but human data

Parsed all sources mentioned above.

Removed all non-9606 records.

Did ROG assignment and so on.

Made the reports.