Description

LOCATION

Table Description

The LOCATION table represents a generic way to capture physical location or address information of Persons and Care Sites.

User Guide

The current iteration of the LOCATION table is US centric. Until a major release to correct this, certain fields can be used to represent different international values.

- STATE can also be used for province or district
- ZIP is also the postal code or postcode
- COUNTY can also be used to represent region

ETL Conventions

Each address or Location is unique and is present only once in the table. Locations do not contain names, such as the name of a hospital. In order to construct a full address that can be used in the postal service, the address information from the Location needs to be combined with information from the Care Site.

CDM Field	User Guide	ETL Conventions	Datatype	Required	Primary Key	Foreign Key	FK Table
location_id	The unique key given to a unique Location.	Each instance of a Location in the source data should be assigned this unique key.	integer	Yes	Yes	No
address_1	This is the first line of the address.		varchar(50)	No	No	No
address_2	This is the second line of the address		varchar(50)	No	No	No
city			varchar(50)	No	No	No
state			varchar(2)	No	No	No
zip		Zip codes are handled as strings of up to 9 characters length. For US addresses, these represent either a 3-digit abbreviated Zip code as provided by many sources for patient protection reasons, the full 5-digit Zip or the 9-digit (ZIP + 4) codes. Unless for specific reasons analytical methods should expect and utilize only the first 3 digits. For international addresses, different rules apply.	varchar(9)	No	No	No
county			varchar(20)	No	No	No
location_source_value		Put the verbatim value for the location here, as it shows up in the source.	varchar(50)	No	No	No
country_concept_id	The Concept Id representing the country. Values should conform to the Geography domain.		integer	No	No	Yes	CONCEPT
country_source_value	The name of the country.		varchar(80)	No	No	No
latitude		Must be between -90 and 90.	float	No	No	No
longitude		Must be between -180 and 180.	float	No	No	No

Primary Key Constraint Name

xpk_location

Unlogged

false



Name	Begin	End

fpk_person_location_id : Relationship	location : Entity	person : Entity
: Relationship	location : Entity	care_site : Entity
fpk_location_country_concept_id : Relationship	concept : Entity	location : Entity



	Name		Value

	Name		location_id
	Type		int4
	Nullable		false
	Unique		false
	Primary Key		true
	Index		false
	Length		0
	Scale		0
	Id Generator		native
	Foreign Key		false
	Generated		Unspecified
	Sync To Attribute		Yes



	Name		Value

	Name		country_concept_id
	Type		int4
	Nullable		false
	Unique		false
	Primary Key		true
	Index		false
	Length		0
	Scale		0
	Id Generator		native
	Foreign Key		true
	Generated		Unspecified
	Sync To Attribute		Yes



	Name		Value

	Name		latitude
	Type		numeric
	Nullable		true
	Unique		false
	Primary Key		false
	Index		false
	Type Name		numeric
	Length		19
	Scale		0
	Id Generator		native
	Foreign Key		false
	Generated		Unspecified
	Sync To Attribute		Yes



	Name		Value

	Name		longitude
	Type		numeric
	Nullable		true
	Unique		false
	Primary Key		false
	Index		false
	Type Name		numeric
	Length		19
	Scale		0
	Id Generator		native
	Foreign Key		false
	Generated		Unspecified
	Sync To Attribute		Yes



Column Name	Data Type	PK / FK	Nullable	Description

location_id	int4	PK	No
address_1	varchar(50)		Yes
address_2	varchar(50)		Yes
city	varchar(50)		Yes
state	varchar(2)		Yes
zip	varchar(9)		Yes
county	varchar(20)		Yes
location_source_value	varchar(50)		Yes
country_concept_id	int4	PK / FK	No
country_source_value	varchar(80)		Yes
latitude	numeric(19)		Yes
longitude	numeric(19)		Yes



	Diagram

	Standardized_Tables : Entity Relationship Diagram
	Vocabulary_With_Standard : Entity Relationship Diagram