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`state_fips` fips` assigned nationally
`county_name`
`county`county_fips` 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` `created` the date and time the row was created in YYYY-MM-DD HH:MM:SS format
`modified` `modified` the date and time the row was last edited in YYYY-MM-DD HH:MM:SS format
Fieldsfield type attributes notes example entity_geographies.entity_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT Provided by Pellucid. FOREIGN KEY: links to entities table 00000000001 entity_geographies.unique_id VARCHAR(255) DEFAULT NULL Non-IPRO identifier hrr_code entity_geographies.name VARCHAR(255) DEFAULT NULL Determined by IPRO based on measures Nassau County entity_geographies.aka VARCHAR(255) DEFAULT NULL Alternate name for geographical entity Nassau entity_geographies.state ENUM DEFAULT NULL Abbreviation for the state the geographical entity resides in, 2 Character NY entity_geographies.state_fips VARCHAR(10) DEFAULT NULL Nationally defined 36 entity_geographies.county_name VARCHAR(30) DEFAULT NULL Physical Location Nassau entity_geographies.county_fips VARCHAR(10) DEFAULT NULL Determined by state 123 entity_geographies.entity_type VARCHAR(50) NOT NULL DEFAULT 'state' Determined by entity and measure county entity_geographies.geo_lat DOUBLE DEFAULT NULL Location 24.26 entity_geographies.geo_long DOUBLE DEFAULT NULL Location 86.45 entity_geographies.closed_date date DEFAULT NULL YYYY-MM-DD 2009-12-31 entity_geographies.created timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' Time the row is created, YYYY-MM-DD HH:MM:SS 2008-12-19 12:19:24 entity_geographies.modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' Time the row was last modified, YYYY-MM-DD HH:MM:SS 2008-12-19 12:19:24
PRIMARY KEY (`entity_id`)
CONSTRAINT `entity_geographies_ibfk_1` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`entity_id`) ON DELETE CASCADE -- Pellucid only constraint
Notes2014-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
field | type | attributes | notes | example |
---|---|---|---|---|
entity_geographies.entity_id | BIGINT(20) | UNSIGNED NOT NULL AUTO_INCREMENT | Provided by Pellucid. FOREIGN KEY: links to entities table | 00000000001 |
entity_geographies.unique_id | VARCHAR(255) | DEFAULT NULL | Non-IPRO identifier | hrr_code |
entity_geographies.name | VARCHAR(255) | DEFAULT NULL | Determined by IPRO based on measures | Nassau County |
entity_geographies.aka | VARCHAR(255) | DEFAULT NULL | Alternate name for geographical entity | Nassau |
entity_geographies.state | ENUM | DEFAULT NULL | Abbreviation for the state the geographical entity resides in, 2 Character | NY |
entity_geographies.state_fips | VARCHAR(10) | DEFAULT NULL | Nationally defined | 36 |
entity_geographies.county_name | VARCHAR(30) | DEFAULT NULL | Physical Location | Nassau |
entity_geographies.county_fips | VARCHAR(10) | DEFAULT NULL | Determined by state | 123 |
entity_geographies.entity_type | VARCHAR(50) | NOT NULL DEFAULT 'state' | Determined by entity and measure | county |
entity_geographies.geo_lat | DOUBLE | DEFAULT NULL | Location | 24.26 |
entity_geographies.geo_long | DOUBLE | DEFAULT NULL | Location | 86.45 |
entity_geographies.closed_date | date | DEFAULT NULL | YYYY-MM-DD | 2009-12-31 |
entity_geographies.created | timestamp | NOT NULL DEFAULT '0000-00-00 00:00:00' | Time the row is created, YYYY-MM-DD HH:MM:SS | 2008-12-19 12:19:24 |
entity_geographies.modified | timestamp | NOT NULL DEFAULT '0000-00-00 00:00:00' | Time the row was last modified, YYYY-MM-DD HH:MM:SS | 2008-12-19 12:19:24 |
PRIMARY KEY (`entity_id`)
CONSTRAINT `entity_geographies_ibfk_1` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`entity_id`) ON DELETE CASCADE -- Pellucid only constraint
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