Clinical trials curation: Difference between revisions
Jump to navigation
Jump to search
(asdf) |
(asdf) |
||
| Line 1: | Line 1: | ||
This is the curator page for 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%'; | 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) < 10 and name like '% %'; | ||
begin; delete from subname where length(name) < 7 and name like '9%'; 1-9 | begin; delete from subname where length(name) < 7 and name like '9%'; 1-9 | ||
# update after editing subname table | |||
begin; update subname set terms=to_tsvector('english',name),query=plainto_tsquery(name); | begin; update subname set terms=to_tsvector('english',name),query=plainto_tsquery(name); | ||
# to solve the no lexemees problem | |||
# to solve the no lexemees | |||
select * from subname where numnode(plainto_tsquery(name))=0 limit 10; | 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) | insert into clinical1.ct2subint (sub_id_fk, ct2int_fk) | ||
select distinct sub_id_fk, ct2int_id from ctinttemp as ct | select distinct sub_id_fk, ct2int_id from ctinttemp as ct | ||
| Line 19: | Line 21: | ||
join subname on ct.terms@@subname.query | 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); | 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. | |||
[[Category:Curator]] | [[Category:Curator]] | ||
Latest revision as of 18:53, 17 August 2017
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
- update after editing subname table
begin; update subname set terms=to_tsvector('english',name),query=plainto_tsquery(name);
- 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.