Loading the Vocabulary - dbmi-pitt/np-terminology-imports GitHub Wiki

DDL to create and load vocabulary tables in the OMOP standard vocabulary

All vocabulary tables with natural products vocabulary can be found here - Vocabulary Tables. Use the following DDL code to create the tables in your vocabulary (if they do not exist).

-- staging_vocabulary.concept definition
-- Drop table
-- DROP TABLE staging_vocabulary.concept;
CREATE TABLE staging_vocabulary.concept (
    concept_id int4 NOT NULL,
    concept_name varchar(255) NOT NULL,
    domain_id varchar(20) NOT NULL,
    vocabulary_id varchar(20) NOT NULL,
    concept_class_id varchar(100) NOT NULL,
    standard_concept varchar(1) NULL,
    concept_code varchar(50) NOT NULL,
    valid_start_date date NOT NULL,
    valid_end_date date NOT NULL,
    invalid_reason varchar(1) NULL
);
CREATE INDEX idx_stg_concept_class_id ON staging_vocabulary.concept USING btree (concept_class_id);
CREATE INDEX idx_stg_concept_code ON staging_vocabulary.concept USING btree (concept_code);
CREATE UNIQUE INDEX idx_stg_concept_concept_id ON staging_vocabulary.concept USING btree (concept_id);
CREATE INDEX idx_stg_concept_domain_id ON staging_vocabulary.concept USING btree (domain_id);
CREATE INDEX idx_stg_concept_varchar_concept_id ON staging_vocabulary.concept USING btree (((concept_id)::character varying));
CREATE INDEX idx_stg_concept_vocabluary_id ON staging_vocabulary.concept USING btree (vocabulary_id);
CREATE INDEX vocab_concept_name_ix ON staging_vocabulary.concept USING btree (vocabulary_id, standard_concept, upper((concept_name)::text), concept_id);

-- staging_vocabulary.concept_relationship definition
-- Drop table
-- DROP TABLE staging_vocabulary.concept_relationship;
CREATE TABLE staging_vocabulary.concept_relationship (
    concept_id_1 int4 NOT NULL,
    concept_id_2 int4 NOT NULL,
    relationship_id varchar(20) NOT NULL,
    valid_start_date date NOT NULL,
    valid_end_date date NOT NULL,
    invalid_reason varchar(1) NULL
);
CREATE INDEX idx_stg_concept_relationship_id_1 ON staging_vocabulary.concept_relationship USING btree (concept_id_1);
CREATE INDEX idx_stg_concept_relationship_id_2 ON staging_vocabulary.concept_relationship USING btree (concept_id_2);
CREATE INDEX idx_stg_concept_relationship_id_3 ON staging_vocabulary.concept_relationship USING btree (relationship_id);
CREATE INDEX idx_stg_concept_relationship_id_4 ON staging_vocabulary.concept_relationship USING btree (concept_id_1, concept_id_2, relationship_id);

-- staging_vocabulary.relationship definition
-- Drop table
-- DROP TABLE staging_vocabulary.relationship;
CREATE TABLE staging_vocabulary.relationship (
    relationship_id varchar(20) NOT NULL,
    relationship_name varchar(255) NOT NULL,
    is_hierarchical varchar(1) NOT NULL,
    defines_ancestry varchar(1) NOT NULL,
    reverse_relationship_id varchar(20) NOT NULL,
    relationship_concept_id int4 NOT NULL
);
CREATE UNIQUE INDEX idx_stg_relationship_rel_id ON staging_vocabulary.relationship USING btree (relationship_id);