Creating clinical name mappings - Revision history

Teague Sterling: Updating

2015-08-13T22:03:42Z

Updating

← Older revision		Revision as of 22:03, 13 August 2015
Line 35:		Line 35:
	(Assuming ctstatus is already loaded)		(Assuming ctstatus is already loaded)

	create temporary table cttemp (nct varchar, ~~status~~ varchar, start_date varchar, ~~title~~ varchar);		create temporary table cttemp (nct varchar, title varchar, start_date varchar, phase varchar, status varchar);

	\copy cttemp from /nfs/work/teague/Projects/trials/extracted/trials.txt		\copy cttemp from /nfs/work/teague/Projects/trials/extracted/trials.txt

	update cttemp set ~~"date"~~=~~NULL~~ where ~~"date"~~='';		update cttemp set start_date = null where start_date='';

	insert into ~~clinical1.~~ct2 (ct_code, description, ctstatus_fk~~, start_date~~) select nct ~~as code~~, ~~status as description, ctstatus_id as ctstatus_fk~~ , ~~cast(~~start_date as date~~) as start_date~~ from cttemp join ~~clinical1~~.ctstatus on ~~title~~=name;		insert into ct2 (ct_code, description, start_date, ctphase_fk, ctstatus_fk) select nct, title, start_date::date, ctphase_id, ctstatus_id from cttemp join ctphase on phase=ctphase.name join ctstatus on status=ctstatus.name;

	create temporary table ~~ctinttemp~~ (code varchar, kind varchar, name varchar, description varchar);		create temporary table ct2inttemp (code varchar, kind varchar, name varchar, description varchar);

	\copy ctinttemp from /nfs/work/teague/Projects/trials/extracted/trial_drugs.txt		\copy ctinttemp from /nfs/work/teague/Projects/trials/extracted/trial_drugs.txt
Line 53:		Line 53:

	alter table ctinttemp add column terms tsvector;		alter table ctinttemp add column terms tsvector;
	update ctinttemp set terms=to_tsvector('english', ~~name), query=plainto_tsquery(~~name);		update ctinttemp set terms=to_tsvector('english', name);

	create index ~~ix_ctinttemp_terms~~ on ctinttemp using ~~gist~~(terms);		create index ix_ct2inttemp_terms on ctinttemp using gin (terms);

	vacuum analyze verbose ~~ctinttemp~~;		vacuum analyze verbose ct2inttemp;

	~~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 on ct2_id~~=ct2_fk and clinical1.ct2int~~.name=ct.name ~~join subname on~~ ct.~~terms@@subname~~.~~query~~;		select sub_id_fk, ct.ct2int_id, ctemp.name as intname, subname.name as subname into temp table ctsubinttemp from subname join ct2inttemp as ctemp on ctemp.terms @@ subname.query join (select ct_code as code, ct2int.name as name, ct2int_id from ct2 join ct2int on ct2_fk=ct2_id) as ct on (ct.name=ctemp.name and ct.code=ctemp.code) where ctemp.kind in ('Drug', 'Nutritional Suppliement');

	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);		-- 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 on ct2_id=ct2_fk and clinical1.ct2int.name=ct.name join subname on ct.terms@@subname.query;

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

	</pre>		</pre>

Frodo: asdf

2015-08-13T21:02:06Z

asdf

@@ Line 62: / Line 62: @@
 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);
 </pre>
 [[Category:Curator]]

Frodo: asdf

2015-08-12T21:09:13Z

asdf

@@ Line 1: / Line 1: @@
 This is part of the curation of clinical trial data.
 <pre>
 create temp table subname as select sub_id_fk as sub_id_fk, who_name as name from catalog_item join catalog on (cat_id_fk=cat_id and short_name='chembl20') join chembl20.molecule_dictionary as md on supplier_code=md.chembl_id join chembl20.molecule_atc_classification as mac on md.molregno=mac.molregno join chembl20.atc_classification as ac on mac.level5=ac.level5;
@@ Line 24: / Line 25: @@
 select * from ctinter join subname on terms@@q where sub_id_fk=53;
 select * from subname join ctinter on q @@ terms where to_tsvector('english', 'biotin') @@ q ;
 </pre>
 [[Category:Curator]]

Frodo: asdf

2015-08-11T21:42:24Z

asdf

New page

This is part of the curation of clinical trial data.

<pre>
create temp table subname as select sub_id_fk as sub_id_fk, who_name as name from catalog_item join catalog on (cat_id_fk=cat_id and short_name='chembl20') join chembl20.molecule_dictionary as md on supplier_code=md.chembl_id join chembl20.molecule_atc_classification as mac on md.molregno=mac.molregno join chembl20.atc_classification as ac on mac.level5=ac.level5;

insert into subname select cs.sub_id_fk, sy.synonym from catalog_substance as cs join synonym as s on cs.cat_content_fk = s.cat_content_fk where not exists (select 1 from subname as sn where sn.sub_id_fk = cs.sub_id_fk and sn.name = sy.synonym);

alter table subname add column q tsquery;

update table subname as s set q=plainto_tsquery(t.who_name) from subname as t where s.sub_id_fk=t.sub_id_fk and s.who_name=t.who_name;

\copy ctinter from druginfo.txt

alter table ctinter add column terms tsvector;

update ctinter as x set terms = to_tsvector('english', y.name) from ctinter as y where x.id=y.id;

create index subanme_idx on subname using gist(q);
create index ctinter_idx on ctinter using gin(terms);

vacuum analyze verbose ctinter;
vacuum analyze verbose subname;

select * from ctinter join subname on terms@@q where sub_id_fk=53;
select * from subname join ctinter on q @@ terms where to_tsvector('english', 'biotin') @@ q ;
</pre>

[[Category:Curator]]