Schema changes to incorporate new addressing standards

[laurahaley]

Some schema changes for locations, addresses, street names, and subunit to comply with fdgc national address and national emergency numbering association standards. If I'm not using the exact standard name, I'll put the standard name in (parentheses)

Zip Codes

• rename city field to community (PlaceName/PostalCommunityName)
  create table zip_codes (
  zip integer not null primary key,
  community varchar(20) not null,
  state char(2) not null default 'IN'
  );

Street Names

• add pre_modifier field (StreetNamePreModifier)
• add pre_type field (StreetNamePretype)
  -add separator field (Separator Element/(StreetNamePretypeSeparator)
• re-add missing STREET_TYPE_SUFFIX_CODE, name the field post_type_id (StreetNamePostType)
• add post_modifier (StreetNamePostModifier)
• re-add missing foreign key post_type_id aka STREET_TYPE_SUFFIX_CODE. Suggest new name for look up table street_post_types or post_types
  create table street_names (
  id serial primary key,
  pre_modifier varchar(16),
  direction_id integer,
  pre_type varchar(16),
  separator varchar(20),
  name varchar(64),
  post_type_id integer,
  post_direction_id integer,
  post_modifier varchar(16),
  notes varchar(240),
  foreign key (direction_id ) references directions (id),
  foreign key (post_direction_id) references directions (id),
  foreign key (post_type_id) references street_post_types (id)

);

Street Post Types (Type Suffixes) - I didn't see a a table for this, it's possible that ENG.MAST_STREET_NAME_TYPE_MASTER and ENG.MAST_STREET_TYPE_SUFFIX_MASTER
got combined into one new table street_types. I think using the standard name street post types (or just post_types) for street type suffix will help avoid confusion.
create table street_post_types (
id serial primary key,
code varchar(8) not null unique,
name varchar(16) not null
);

Addresses

• reorder some fields
• add county field
• add community_place_id field, foreign key to relate it to new geo_place_names table. We'll want to restrict it to Unincorporated or Neighborhood place types
• add elevation field
• re-add notes field
  create table addresses (
  id serial primary key,
  address_type varchar(16) not null,
  street_number_prefix varchar(8),
  street_number integer not null,
  street_number_suffix varchar(8),
  adddress2 varchar(64),
  street_id integer not null,
  county varchar(16), not null default 'Monroe',
  jurisdiction_id integer not null,
  township_id integer,
  section varchar(16),
  quarter_section char(2),
  subdivision_id integer,
  plat_id integer,
  plat_lot_number varchar(16),
  community__place_id integer,
  city varchar(32),
  state char(2) not null default 'IN',
  zip integer,
  zipplus4 smallint,
  state_plane_x integer,
  state_plane_y integer,
  latitude decimal(10, 8),
  longitude decimal(10, 8),
  usng varchar(20),
  elevation decimal(5,1),
  notes varchar(240),
  geom public.geometry(Point, 2966),
  foreign key (street_id ) references streets (id),
  foreign key (jurisdiction_id) references jurisdictions(id),
  foreign key (township_id ) references townships (id),
  foreign key (subdivision_id ) references subdivisions (id),
  foreign key (plat_id ) references plats (id),
  foreign key (quarter_section) references quarter_sections(code),
  foreign key (zip ) references zip_codes(zip),
  foreign key (community_place_id) references geo_place_names(id)
  );

Geographic Place Names - create new look up table to use with addresses to meet some NENA standards (Unincorporated Comunity and Neighborhood Community) and add geometry so we can also use with map labeling).
create table geo_place_names (
id serial primary key,
place_type varchar(16) not null unique, (Municipality, Unincorporated Community, Neighborhood, Natural Place)
name varchar(40) not null unique,
geom public.geometry(Point, 2966),

);

Subunits (Subaddresses)

• reorder position of notes field
• add building field
• add floor field. Floor is a descriptor (ie Floor 2, Mezzanine, Main Floor) not numeric building level
• add room field
  [ Note: the nena standard has building, floor, and room separated from units used for typical street addresses such as apt, suite, lot... We have included building and floor as a unit type which is more in line with the fdgc standard. Emergency response would find separate fields more useful, but it will make it trickier to create a composite full address value used for many things. ]
• add other_location field (Additional Location Information - a subaddress that is not a unit building floor room or seat - examples loading dock ,storage area, ,barn,)
• change name of type_id to unit_type_id (Subaddress Type) - just type_id makes it seem like it is a description of the type of record and not the unit designation where unit type + identifier is full unit.
• add elevation field
  create table subunits (
  id serial primary key,
  address_id integer not null,
  building varchar(25),
  floor varchar(16),
  unit_type_id integer,
  identifier varchar(16),
  room varchar(15),
  sub_location varchar(30),
  state_plane_x integer,
  state_plane_y integer,
  latitude decimal(10, 8),
  longitude decimal(10, 8),
  usng varchar(20),
  elevation decimal(5,1),
  notes varchar(240),
  geom public.geometry(Point, 2966),
  foreign key (address_id) references addresses (id),
  foreign key (unit_type_id ) references subunit_types(id)
  );

Locations -
-reorder some fields

• add use_id field foreign key to relate it to new location_uses lookup table (No standard for fgdc but nena calls is place type) - used to describe more detailed description of the use as opposed to location type which is more of a general classification)
• add feature_type field (Feature Type/Placement Method)
• add description field (Location Description)
  create table locations (
  location_id serial,
  active boolean,
  address_id integer,
  subunit_id integer,
  type_id integer not null,
  use_id integer,
  feature_type varchar(20), (Building, Site, Property, Driveway, Entrance)
  description varchar(240),
  mailable boolean,
  occupiable boolean,
  unique (location_id, address_id, subunit_id),
  foreign key (address_id) references addresses (id),
  foreign key (subunit_id) references subunits (id),
  foreign key (type_id ) references location_types(id)
  foreign key (use_id ) references location_uses(id)
  );

Location Uses - create new lookup table
create table location_uses (
id serial primary key,
name varchar(30) not null
);