-- packages/ref-us-counties/sql/oracle/ref-us-counties-create.sql
--
-- @author jon@jongriffin.com
-- @creation-date 2001-08-27
-- @cvs-id $Id: ref-us-counties-create.sql,v 1.1 2001/08/27 23:08:35 jong Exp $

-- This is the combination of state and county FIPS codes.
-- It is possible to get the state by parsing the first 2 chars

create table us_counties (
    fips_state_code  char(2)  
	             constraint us_counties_fips_fk
                     references us_states(fips_state_code),
    fips_county_code char(6)  
                     constraint us_counties_county_code_nn not null,
    name             varchar2(100)
                     constraint us_counties_name_nn not null,
    state_abbrev     char(2)
                     constraint us_counties_state_abrrev_fk
                     references us_states(abbrev),
    population       number,
    housing_units    number,
    land_area        number,
    water_area       number,
    latitude         number,
    longitude        number,
    --
    -- Primary key for this table is generated by combining the state
    -- and county FIPS codes.  County code are only unique within a given state.
    -- 
    constraint us_counties_unique_fips_pk primary key (fips_county_code, fips_state_code)
);

comment on table us_counties is '
    This is the United States county code table from the US Census Bureau
';

comment on column us_counties.fips_state_code is '
State FIPS code.
';

comment on column us_counties.fips_county_code is '
County FIPS code.
';

comment on column us_counties.name is '
Name. Includes name of county. Counties don't nave a type, but do have
the word "County" as part of the name.
';

comment on column us_counties.state_abbrev is '
State abbreviations.
';

comment on column us_counties.population is '
Total population (1990).
';

comment on column us_counties.housing_units is '
Housing units (1990).
';

comment on column us_counties.land_area is '
Land are included (in thousandths of a square kilometer).
';

comment on column us_counties.water_area is '
Water area included (in thousandths of a square kilometer).
';

comment on column us_counties.latitude is '
Longitude (millionths of a degree, + or - denoting N or S,
respectively.
';

comment on column us_counties.longitude is '
Latitude (millionths of a degree, + or - denoting N or S,
respectively.
';

-- add this table into the ACS reference repository

declare
v_id integer;

begin
    v_id := acs_reference.new(
        table_name     => 'US_COUNTIES',
        source         => 'US Census Bureau',
        source_url     => 'http://ftp.census.gov/geo/www/gazetteer/places.html',
        last_update    => to_date('1990-12-31','YYYY-MM-DD'),
        effective_date => sysdate
    );
commit;
end;
/

@ '../common/ref-us-counties-data.sql'