psql -f polbnda_fips.sql

psql <<EOF

DROP TABLE temp_fips10;

CREATE TABLE temp_fips10 (gid serial PRIMARY KEY,
                          fips2         varchar(2),
                          fips4         varchar(4),
                          na0           varchar(64),
                          na1           varchar(64),
                          na2           varchar(2),
                          na3           varchar(1));

SELECT AddGeometryColumn ('','temp_fips10','geom','4326','POLYGON',2);

INSERT INTO temp_fips10 (geom, na1, na2, na3)
SELECT geom, nam, na2, na3 FROM polbnda4;


--- split the original na1 into na0, na1

UPDATE temp_fips10
SET na0 = split_part (na1, '#', 1),
    na1 = split_part (na1, '#', 2)
WHERE position ('#' in na1) != 0;

--- get fips4 values from our polbnda_fips correspondance table

UPDATE temp_fips10
SET fips4 = polbnda_fips.fips4, 
    fips2 = substring (polbnda_fips.fips4 from 1 for 2)
FROM polbnda_fips
WHERE polbnda_fips.na2 = temp_fips10.na2
  AND polbnda_fips.na1 = temp_fips10.na1;

--- fix a few zones

-- Saint Martin is no longer part of Guadeloupe
UPDATE temp_fips10
SET fips2 = 'RN', fips4 = 'RN'
WHERE na0 = 'SAINT MARTIN' AND na2 = 'GP';

-- Saint Barthelemy is no longer part of Guadeloupe
UPDATE temp_fips10
SET fips2 = 'TB', fips4 = 'TB'
WHERE na0 = 'SAINT-BARTHELEMY' AND na2 = 'GP';

-- islands at the western end of Marshall Islands are
-- incorrectly labelled MICRONESIA
UPDATE temp_fips10
SET fips2 = 'RM', fips4 = 'RM'
WHERE fips2 = 'FM'
  AND ST_within (geom, polygonfromtext ('POLYGON((160 8, 160 13, 164 13, 164 8, 160 8))', 4326));

-- Chatham Islands are incorrectly labelled WELLINGTON
UPDATE temp_fips10
SET fips4 = 'NZ10'
WHERE fips2 = 'NZ'
  AND ST_within (geom, polygonfromtext ('POLYGON((-179 -42, -173 -42, -173 -47, -179 -47, -179 -42))', 4326));

-- Kermadec islands are incorrectly labelled AUCKLAND. They are not part of 
-- any region
UPDATE temp_fips10
SET fips4 = 'NZ'
WHERE fips2 = 'NZ'
  AND ST_within (geom, polygonfromtext ('POLYGON((-179 -28, -173 -28, -173 -32, -179 -32, -179 -28))', 4326));

-- Divisions of Federated states of Micronesia
UPDATE temp_fips10
SET fips4 = 'FM04'
WHERE fips2 = 'FM'
  AND ST_within (geom, polygonfromtext ('POLYGON((136 2, 148.5 2, 148.5 15, 136 15, 136 2))', 4326));

UPDATE temp_fips10
SET fips4 = 'FM03'
WHERE fips2 = 'FM'
  AND ST_within (geom, polygonfromtext ('POLYGON((148.5 2, 155 2, 155.5 15, 148.5 15, 148.5 2))', 4326));

UPDATE temp_fips10
SET fips4 = 'FM02'
WHERE fips2 = 'FM'
  AND ST_within (geom, polygonfromtext ('POLYGON((155 2, 162 2, 162 15, 155 15, 155 2))', 4326));

UPDATE temp_fips10
SET fips4 = 'FM01'
WHERE fips2 = 'FM'
  AND ST_within (geom, polygonfromtext ('POLYGON((162 2, 165 2, 165 15, 162 15, 162 2))', 4326));


-- Divisions of Kiribati
UPDATE temp_fips10
SET fips4 = 'KR01'
WHERE fips2 = 'KR'
  AND ST_within (geom, polygonfromtext ('POLYGON((160 4, 160 -3, 177 -3, 177 4, 160 4))', 4326));

UPDATE temp_fips10
SET fips4 = 'KR02'
WHERE fips2 = 'KR'
  AND ST_within (geom, polygonfromtext ('POLYGON((-165 5, -165 -12, -150 -12, -150 5, -165 5))', 4326));

UPDATE temp_fips10
SET fips4 = 'KR03'
WHERE fips2 = 'KR'
  AND ST_within (geom, polygonfromtext ('POLYGON((-175 -2, -175 -5, -170 -5, -170 -2, -175 -2))', 4326));



-- UK sovereign area in Cyprus, resolve to AX Akroti Sovereign Base Area
UPDATE temp_fips10
SET fips2 = 'AX', fips4 = 'AX'
WHERE ST_within (pointfromtext ('POINT(33.0 34.6)', 4326), geom);

-- UK sovereign area in Cyprus, resolve to DX Dhekelia Sovereign Base Area
UPDATE temp_fips10
SET fips2 = 'DX', fips4 = 'DX'
WHERE ST_within (pointfromtext ('POINT(33.75 34.99)', 4326), geom);

-- area under dispute, resolve to WE West Bank
UPDATE temp_fips10
SET fips2 = 'WE', fips4 = 'WE' 
WHERE ST_within (pointfromtext ('POINT(35.4 31.7)', 4326), geom)
   OR ST_within (pointfromtext ('POINT(34.98 31.85)', 4326), geom)
   OR ST_within (pointfromtext ('POINT(35.23 31.75)', 4326), geom);

-- area under dispute, resolve to SY03 Golan Heights
UPDATE temp_fips10
SET fips2 = 'SY', fips4 = 'SY03'
WHERE ST_within (pointfromtext ('POINT(35.7 33.0)', 4326), geom);

-- areas under dispute, resolve to CH13 or CH14
UPDATE temp_fips10
SET fips2 = 'CH', fips4 = 'CH14'
WHERE ST_within (pointfromtext ('POINT(79.1 31.24)', 4326), geom)
   OR ST_within (pointfromtext ('POINT(79.4 32.8)', 4326), geom)
   OR ST_within (pointfromtext ('POINT(80.145 30.732)', 4326), geom)
   OR ST_within (pointfromtext ('POINT(78.65 32.08)', 4326), geom);

UPDATE temp_fips10
SET fips2 = 'CH', fips4 = 'CH13'
WHERE ST_within (pointfromtext ('POINT(78.6 35.6)', 4326), geom);


-- area under dispute, resolve to SA17
UPDATE temp_fips10
SET fips2 = 'SA', fips4 = 'SA17'
WHERE ST_within (pointfromtext ('POINT(43.22 17.25)', 4326), geom);

-- litigated areas, resolve to BR06
UPDATE temp_fips10
SET fips2 = 'BR', fips4 = 'BR06'
WHERE ST_within (pointfromtext ('POINT(-41.1 -4.7)', 4326), geom)
   OR ST_within (pointfromtext ('POINT(-41.2 -3.9)', 4326), geom)
   OR ST_within (pointfromtext ('POINT(-41.4 -3.45)', 4326), geom);

-- litigated area, resolve to BR04
UPDATE temp_fips10
SET fips4 = 'BR04'
WHERE ST_within (pointfromtext ('POINT(-56.57 -2.33)', 4326), geom);

--- dissolve on fips2/fips4
DROP TABLE fips10s;

CREATE TABLE fips10s (gid serial PRIMARY KEY,
                      fips2      varchar(2),
                      fips4      varchar(4));

SELECT AddGeometryColumn ('','fips10s','geom','4326','POLYGON',2);

INSERT INTO fips10s (fips2, fips4, geom) 
  SELECT fips2, fips4, h as geom FROM (
    SELECT fips2, fips4, (ST_Dump (g)).geom as h FROM (
      SELECT fips2, fips4, ST_Union (geom) as g
      FROM temp_fips10
      GROUP BY fips2, fips4)
    as x)
  as y;

--- dissolve on fips2
DROP TABLE fips10c;

CREATE TABLE fips10c (gid serial PRIMARY KEY,
                      fips2      varchar(2));

SELECT AddGeometryColumn ('','fips10c','geom','4326','POLYGON',2);

INSERT INTO fips10c (fips2, geom) 
  SELECT fips2, h as geom FROM (
    SELECT fips2, (ST_Dump (g)).geom as h FROM (
      SELECT fips2, ST_Union (geom) as g
      FROM fips10s
      GROUP BY fips2)
    as x)
  as y;

---
DROP TABLE temp_fips10;


EOF

pgsql2shp -g geom -f fips10c ${PGDATABASE} fips10c

pgsql2shp -g geom -f fips10s ${PGDATABASE} fips10s