OHDSI Home | Forums | Wiki | Github

Person location over time?

CDM Builders
#21

We had discussed having the location history table be able to contain entities other than just person but weren’t sure if it was necessary. A basic example would be a provider changing locations which would be relevant for distance to care calculations.

The general structure was along the lines of:
(@Andrew if you remember more clearly please correct me)

I didn’t include time in this table as I don’t think it’s relevant for the use cases we’ve been considering. @Gowtham_Rao could you elaborate on how you think time would be useful in terms of locations?

#22

@rtmill - the entity_domain and entity_id makes sense.

I think date is definitely needed to evaluate a relationship at certain point in history. Time is just more granularity, and maybe optional - like Andrew said

#23

@rtmill:

I see. So, what prevents us from introducing the tables PERSON_LOCATION_HISTORY, PROVIDER_LOCATION_HISTORY, CARESITE_LOCATION_HISTORY (and, if somebody can explain to me why,) DEVICE_LOCATION_HISTORY?

#24

too many tables? :scream:

over achieving

but this is a great example for an E-A-V model.

#25

Ah, if we go with all those granular tables, we’ll end up right back to a unified cost table (this time called the unified location table).

#26

Not a fan of Entity-Attribute-View, elegant and expandable, but can get out
of control. From above example, add entity_domain_2 and entity_id_2. Now
you can handle a time span relationship between any two entities. But this
is close to re-creating the fact_relationship table, so we could add start
and end times to the fact_relationship table and eliminate the location
history table. But now this new table looks almost like the concept
relationship table so maybe we eliminate the concept relationship and just
use the new fact relationship table with time constraints. And so on

#27

I see. So, what prevents us from introducing the tables PERSON_LOCATION_HISTORY, PROVIDER_LOCATION_HISTORY, CARESITE_LOCATION_HISTORY (and, if somebody can explain to me why,) DEVICE_LOCATION_HISTORY?

To me its a debate between efficiency and organization. Would the efficiency gain from only needing to search one table at a time as needed (e.g. looking for a provider’s history in the provider history table) outweigh the ease of use of a single location history table?

@DTorok :

Not a fan of Entity-Attribute-View, elegant and expandable, but can get out of control.

Would you rather distinct location history tables for each domain as Christian suggests? Would you still have concerns if we enforce certain restrictions, such as:

  • The second entity in the relationship must always be a location
  • There is a specified list of acceptable domains allowed for entity_domain. I can’t remember off the top of my head but I believe the CDM restricts the options for race or ethnicity in a similar manner
#28

@DTorok:

Perfectly put. What do we do? It’s rare that we have location_history data, and it’s rare that people want to use it. How do we create a solution which is simple, does the trick and is easy to understand and query?

#29

You are right about the elegant and expandable part. It is easy for new comer to follow, and good for adoption. Performance may also be a concern compared to a normalized table - although this is progressively less. Besides, it cant get more out of control than what the vocabulary allows.

I can’t debate this further - as this is end of my understanding, but from a conceptual stand point – E-A-V is reported to be very good when there is great metadata. The beauty of the OMOP CDM is the vocabulary i.e. metadata. Logical schemas may be deployed that are normalized over a physical E-A-V when there is good metadata. Again, not a technologist - but E-A-Vs are so easy to follow

t