entity_geographies

Summary

This table is used as a way to store information on geographic entities, such as regions or states. For example, New York State and Nassau County are both treated geographic entities that are stored differently than individual hospitals. entity_id is a foreign key on the entities table and is unique across all referenced tables, and attributes for geographical entities are stored as key value pairs in entity_geography_attributes and entity_geography_overrides.

This table includes an entity for each ZIP5 code in the US, each FIPS county, every state and territory, as well as the Dartmouth HRRs and HSAs. Additionally, the table stores user-defined geographic entities such as regions of Illinois defined by IDPH, and regions of New York defined by UHF.

Table Creation

DROP TABLE IF EXISTS `entity_geographies`;

CREATE TABLE entity_geographies ( 
`entity_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 
`unique_id` varchar(255) DEFAULT NULL, 
`name` varchar(255) DEFAULT NULL, 
`aka` varchar(255) DEFAULT NULL, 
`state` enum ('AK','AL','AR','AZ','CA','CO','CT','DC','DE','FL','GA','GU','HI','IA','ID',
'IL','IN','KS','KY','LA','MA','MD','ME','MI','MN','MO','MP','MS','MT','NC','ND','NE','NH', 
'NJ','NM','NV','NY','OH','OK','OR','PA','PR','RI','SC','SD','TN','TX','UT','VA','VI','VT', 
'WA','WI','WV','WY','US','AS','AE','AA','AP','PW','FM','MH') DEFAULT NULL, 
`state_fips` varchar(10) DEFAULT NULL, 
`county_name` varchar(30) DEFAULT NULL, 
`county_fips` varchar(10) DEFAULT NULL, 
`entity_type` varchar(50) NOT NULL DEFAULT 'state', 
`geo_lat` double DEFAULT NULL, 
`geo_long` double DEFAULT NULL, 
`closed_date` date DEFAULT NULL, 
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP, 
PRIMARY KEY (`entity_id`), 
CONSTRAINT `entity_geographies_ibfk_1` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`entity_id`) ON DELETE CASCADE -- Pellucid only constraint 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Field Information

`entity_id` the primary key for the table. This is unique to each geographic entity and should link to the entities table 
`unique_id` stores a unique, non-IPRO ID, such as hrr_code 
`name` description of the geographic entity 
`aka` any aliases the area may have 
`state` 
`state_fips` assigned nationally 
`county_name` 
`county_fips` national identifying number for the county 
`entity_type` the scope/size/description of the entity being measured 
`geo_lat` latitude location 
`geo_long` longitude location 
`closed_date` the date the entity stopped being measured 
`created` the date and time the row was created in YYYY-MM-DD HH:MM:SS format 
`modified` the date and time the row was last edited in YYYY-MM-DD HH:MM:SS format

Fields

PRIMARY KEY (`entity_id`) 
CONSTRAINT `entity_geographies_ibfk_1` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`entity_id`) ON DELETE CASCADE -- Pellucid only constraint

Notes

2014-07-07

updated entity_type to varchar(50)

AL

2010-05-26

• added 'AS','AE','AA','AP','PW','FM','MH' to the list of possible entries for the `state` field. This was based on information uploaded from the database of the nation's zip codes (located in the `zipcodes` table of the `pellucid2` database)
• After consulting with jmking added 'zip' and 'idph_region' to the list of potential entries for the `entity_type` field.
• added the `state_fips`, `county_name`, and `county_fips` fields to the 'Table Creation', 'Field Information', and 'Fields' sections of this page. This was based on the recommendations of Jmking regarding this table.

~JG

2010-06-23

• added 'uhf_region' to entity_types

~JG