search

gbif / model-tests

Exploration of sample models
2 stars 0 forks source link

Wrong object IDs in Entity relationship table #8

Closed timrobertson100 closed 2 years ago

timrobertson100 commented 2 years ago 
arctos=# ALTER TABLE entity_relationship ADD FOREIGN KEY ("objectEntityID") REFERENCES entity_of_interest ("entityID");
ERROR:  foreign key constraint "entity_relationship_objectEntityID_fkey" cannot be implemented
DETAIL:  Key columns "objectEntityID" and "entityID" are of incompatible types: character varying and numeric.
timrobertson100 commented 2 years ago

This seems to be caused by using both local and external IDs in the objectEntityID in entity_relationship

arctos=# select * from entity_relationship;
 entityRelationshipID | dependsOnEntityRelationshipID | subjectEntityID | entityRelationshipType |                   objectEntityID                   
----------------------+-------------------------------+-----------------+------------------------+----------------------------------------------------
                      |                               |        21714980 | host of                | http://arctos.database.museum/guid/DMNS:Para:664
                      |                               |        21714980 | host of                | http://arctos.database.museum/guid/DMNS:Para:64
                      |                               |        21714980 | host of                | http://arctos.database.museum/guid/DMNS:Para:65
                      |                               |        29610849 | parasite of            | http://arctos.database.museum/guid/DMNS:Mamm:11098
                      |                               |        29610849 | collected with         | http://arctos.database.museum/guid/DMNS:Para:664
                      |                               |        29610849 | collected with         | http://arctos.database.museum/guid/DMNS:Para:65
                      |                               |        29610851 | parasite of            | http://arctos.database.museum/guid/DMNS:Mamm:11098
                      |                               |        29610851 | collected with         | http://arctos.database.museum/guid/DMNS:Para:664
                      |                               |        29610851 | collected with         | http://arctos.database.museum/guid/DMNS:Para:64
                      |                               |        10007944 | shows cataloged_item   | 10599395
                      |                               |        10007945 | shows cataloged_item   | 10599395
                      |                               |        10203450 | shows cataloged_item   | 10599395
                      |                               |        10212653 | shows cataloged_item   | 10599395
...
...
...
timrobertson100 commented 2 years ago

Looking at the EOI identifier table, I think some can be mapped:

current proposed
http://arctos.database.museum/guid/DMNS:Para:664 not found
http://arctos.database.museum/guid/DMNS:Para:64 29610849
http://arctos.database.museum/guid/DMNS:Para:65 29610851
http://arctos.database.museum/guid/DMNS:Mamm:11098 21714980 perhaps?
timrobertson100 commented 2 years ago

Closed as we'll discuss this on #9 instead

tucotuco commented 2 years ago

My first quick guess here without looking is that the numbers you are trying to match are occurrenceIDs, whereas the EOI Ids from Arctos really are organismIDs.

On Fri, Jun 10, 2022 at 6:56 AM Tim Robertson @.***> wrote:

Assigned #8 https://github.com/timrobertson100/model-tests/issues/8 to @tucotuco https://github.com/tucotuco.

— Reply to this email directly, view it on GitHub https://github.com/timrobertson100/model-tests/issues/8#event-6784651963, or unsubscribe https://github.com/notifications/unsubscribe-auth/AADQ7226W4IGIUXYLCWLSEDVOMGLFANCNFSM5YM3UOVA . You are receiving this because you were assigned.Message ID: @.***>