Clinical trials curation

From DISI
Revision as of 18:53, 17 August 2017 by Frodo (talk | contribs) (asdf)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

This is the curator page for clinical trials curation.

It would be a good idea to update drugbank and HMDB and their synonyms before updating CT.

curating subname, the basis for joining CT to substance

These are examples. look closely before executing.

update subname set name=regexp_replace(name, ' *.inn.*$',) where name like '%[inn%';
begin; delete from subname where length(name) < 10 and name like '% %';
begin; delete from subname where length(name) < 7 and name like '9%'; 1-9
  1. update after editing subname table
begin; update subname set terms=to_tsvector('english',name),query=plainto_tsquery(name);
  1. to solve the no lexemees problem
select * from subname where numnode(plainto_tsquery(name))=0 limit 10;

create the mapping table between them

Do this after updating subname

insert into clinical1.ct2subint (sub_id_fk, ct2int_fk)
select distinct sub_id_fk, ct2int_id from ctinttemp as ct
join clinical1.ct2 on ct.code=ct_code
join clinical1.ct2int as cti on ct2_id=cti.ct2_fk and cti.name=ct.name
join subname on ct.terms@@subname.query
where not exists(select 1 from clinical1.ct2subint as e where e.sub_id_fk=subname.sub_id_fk and e.ct2int_fk=cti.ct2int_id);

History

Some history about how the tables were originally created. Creating clinical name mappings. Note that this page should be obsoleted by the current page.