Clinical trials curation

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.