Creating clinical name mappings

This is part of the curation of clinical trial data.

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 ;