POSTGRESQL Foreign Keys

[fariasfrancisco]

The POSTGRESQL script creates all the tables and populates them all right, but no foreign keys are created. Is this intended? how can I fix this.

Another issue I have is that this script does not work with other release than english international version. At least the spanish one is not working without modifying the script.

[siamchen]

I cannot see the need for foreign key requirement for imported SNOMED CT as it is in read only mode. Using indexing should be more appropriate. I have no idea about the difference between international release and other country local release. Can you give me some hits to make the script more robotic?

[fariasfrancisco]

Yeah, I guess you are right about the FKs, I just use them to know that, for example a moduleId points to a conpcetId but never mind.

Regarding the spanish release, which is the one I have access to, the file names change. For example, this is the Concept file name for the international release: sct2_Concept_Full_INT-20170731.txt. And the spanish name for that file is sct2_Concept_SpanishExtensionFull_INT-20170731.txt. So the TYPE changes from Full in this example to SpanishExtensionFull. Another example is sct2_Description_Full-en_INT-20170731.txt which in spanish is sct2_Description_SpanishExtensionFull-en_INT-20170731.txt where not only the type change but the locale (from en to es)

[fariasfrancisco]

@siamchen do you happen to have a script with all the recommended indexes?

[siamchen]

Hi @fariasfrancisco , this is a sample script to create indexing for the tables after data import. You need to adjudge the indexing based on your implementation.

CREATE INDEX description_conceptid_idx ON snomedct.description_f USING btree (conceptid COLLATE pg_catalog."default"); CREATE INDEX textdefinition_conceptid_idx ON snomedct.textdefinition_f USING btree (conceptid); CREATE INDEX relationship_f_idx ON snomedct.relationship_f USING btree (sourceid, destinationid); CREATE INDEX stated_relationship_f_idx ON snomedct.stated_relationship_f USING btree (sourceid, destinationid); CREATE INDEX langrefset_referencedcomponentid_idx ON snomedct.langrefset_f USING btree (referencedcomponentid); CREATE INDEX associationrefset_f_idx ON snomedct.associationrefset_f USING btree (referencedcomponentid, targetcomponentid); CREATE INDEX attributevaluerefset_f_idx ON snomedct.attributevaluerefset_f USING btree (referencedcomponentid, valueid); CREATE INDEX simplerefset_referencedcomponentid_idx ON snomedct.simplerefset_f USING btree (referencedcomponentid); CREATE INDEX complexmaprefset_referencedcomponentid_idx ON snomedct.complexmaprefset_f USING btree (referencedcomponentid); CREATE INDEX extendedmaprefset_referencedcomponentid_idx ON snomedct.extendedmaprefset_f USING btree (referencedcomponentid);

[fariasfrancisco]

Thank you so much!

[fariasfrancisco]

One last question if you don't mind. How would I query the database if I want to, for example, get al clinical findings which description match a certain string? I know that clinical finding has a conceptId which is 404684003. But there is no way to relate concepts whose description match the given string to that conceptId (unless they are direct descendants of it) without making a recursive query to find all of its children.

I know I am doing something wrong here, since the query takes a really long time sometimes. Do you have any sugestions?

Thanks in advance

[siamchen]

Hi @fariasfrancisco , Yes, there are something wrong here. For querying a concept as a child of the others in non-graph database, you do need transitive closure. SNOMED International has a Perl script for generate that.

[fariasfrancisco]

awesome, thanks!