Location Table Updates

[clairblacketer]

Location Table Updates

• Requester: @alondhe, @vojtechhuser, Gowtham Rao
• Discussion: link to forum post.

---

Proposal

Relevant table: Location

• Add a country field
• Add a way to capture region
• Add latitude and longitude
• The GIS workgroup will also have some changes they are interested in as well

Field	Required	Type	Description
location_id	Yes	integer	A unique identifier for each geographic location.
country	No	varchar(3)	The ISO country code
address_1	No	varchar(50)	The address field 1, typically used for the street address, as it appears in the source data.
address_2	No	varchar(50)	The address field 2, typically used for additional detail such as buildings, suites, floors, as it appears in the source data.
city	No	varchar(50)	The city field as it appears in the source data.
state	No	varchar(2)	The state field as it appears in the source data.
zip	No	varchar(9)	The zip or postal code.
county	No	varchar(20)	The county.
region	No	varchar(50)	The region field as it appears in the source data.
location_source_value	No	varchar(50)	The verbatim information that is used to uniquely identify the location as it appears in the source data.
latitude	No	float	The geocoded latitude
longitude	No	float	The geocoded longitude

Conventions

• The Country field will be based on the ISO's 3166-1 vocabulary, which officially designates 2- and 3-character codes for 249 countries. The alpha-3 set, which uses 3-characters, is the suggested version.
• For standardized geospatial visualization and analysis, addresses need to be, at the minimum be geocoded into latitude and longitude. This allows it to put as a point on a map. This proposal is to add two fields, latitude and longitude to the location table.
• We should allow regions to propose region-specific extensions to the CDM/vocabulary (needs elaboration)

[JaehyeongCho]

I think inputting the location information in the observation table would be an alternative for 'Add a way to capture region'

[JaehyeongCho]

I would like to know when the extended location model presented in this issue will be reflected in the OMOP-CDM version.

Will it be reflected in the next OMOP-CDM update?

[gowthamrao]

I second that. Let's get the least controversial fields latitude and longitude in to the location table, at least

[cgreich]

@gowthamrao: What's controversial about the others? I wouldn't call the field "zip" but postalcode (zip is an utterly American thing), and I might challenge whether we can merge the region or county fields, but that's not very controversial. If you think it's ready bring it on. @clairblacketer holds the meeting agenda strings.

[gowthamrao]

Exactly. You just mentioned all the controversies. Region, zip, etc. Lat and long are non controversial. We need to propose that ASAP and try to incorporate into CDM.

[cgreich]

Bring it on.

[clairblacketer]

@gowthamrao @cgreich I'll add this to the agenda for next Tuesday and I will invite Robert Miller as well from the GIS workgroup.

[cgreich]

Well, all of those guys say they just want country (which I don't understand why, since the value will always be the same in a database, with rarest exceptions). Only Robert has GIS, and the Koreans. Want to invite those?

[clairblacketer]

Sure - I invited Robert Miller already and I will extend the invite to the Koreans as well.

[rtmill]

We didn't have time during the CDM call today to discuss this proposal so I figured I'd put my two cents down while the idea is still fresh. To preface, this ignores the other discussion of how to enable the location table to be internationally friendly (in addition to adding a field to represent altitude as Melanie Philofsky has suggested) as I assume it's best to distinguish the topics.

In short, my revised proposal is nearly the exact format that @gowthamrao put forward in his proposal (happy 1 year anniversary Gowtham's post!). The only difference would be to increase the size limit of the country field to 100 characters or so, just in case. TODO: Data mine Gowtham's forum post history, pass off ideas as my own

Is there anyone who disagrees with adding latitude and longitude columns? It appears to be low hanging fruit that we could push forward if we get hung up on the other modifications to the location table.

Differences from current proposal

1 ) Use free text instead of ISO codes to represent countries

• Do we have use cases where institutions have data from enough distinct countries that they need to put countries into universal coding/concepts? For the small percentage of users that do have data from multiple countries, as long as they are consistent with how they represent the country as a string it should be sufficient for querying (what sort of analysis are we using country for?)
• Fits pattern currently used in location table for street address fields (string as appears in source)
• If you are using your location data to do spatial analysis, you will most likely have your data geocoded (latitude/longitude fields populated) and could thus infer the country rather than rely on the field

2 ) Do not maintain a reference to a region within the location table

• Locations are not equivalent to regions (discussed here)
• We should avoid forcing many-to-many relationships into one-to-one representations
  • Why continue this pattern of design that keeps biting us later on? For instance, currently there's no way to represent location history (though a proposal is ready for review) due to the CDM assumption that only one location is relevant throughout a person's life
    • e.g. Person to Location, Person to Care site, Person to Provider, Provider to Care Site, Care Site to Location, Provider to Location, etc.
    • We can avoid these issues by:
      • Restrict data that is temporally qualified to the tables that have temporal qualifiers (date fields)
      • Refrain from storing dynamic data in static tables
    • In other words, restrict the type of data we keep in entity tables (person, location, provider, care site) to one-to-one, static information (e.g. date of birth and race in PERSON, street address and lat/long in LOCATION)
  • The relationship between location and region is many-to-many
  • Which region type would be the standard? How do we reconcile institutions' varying level of granularity? International?
  • Regions do not always roll up to one another (e.g. census block to hospital service area) thus a single location-to-region reference would not be sufficient

Field	Required	Type	Description
location_id	Yes	integer	A unique identifier for each geographic location.
address_1	No	varchar(50)	The address field 1, typically used for the street address, as it appears in the source data.
address_2	No	varchar(50)	The address field 2, typically used for additional detail such as buildings, suites, floors, as it appears in the source data.
city	No	varchar(50)	The city field as it appears in the source data.
state	No	varchar(2)	The state field as it appears in the source data.
zip	No	varchar(9)	The zip or postal code.
county	No	varchar(20)	The county.
country	No	varchar(100)	The country as it appears in the source data
location_source_value	No	varchar(50)	The verbatim information that is used to uniquely identify the location as it appears in the source data.
latitude	No	float	The geocoded latitude
longitude	No	float	The geocoded longitude

[gowthamrao]

@rtmill good post.

Altitude - can't it be derived at analytics time? Are there any use case to have altitude only on location table without lat and long? If lat and long are prerequisites, then can't altitude be derived from it thru a lookup?

[MelaniePhilofsky]

We, University of Colorado Denver, are interested in altitude data as a criteria to include patients in a cohort.