CREATE TABLE poly_state AS
  SELECT distinct substring(module from 1 for 5) as state_module from poly_county;
 ALTER TABLE poly_state ADD COLUMN gen_full geometry;
 
 
 UPDATE poly_state SET gen_full =
      (
          SELECT buffer( collect(gen_full), 0)  FROM poly_county WHERE poly_county.module like (poly_state.state_module || '%')
      )
 ;

--should all return 0
 select count(*) from poly_state where gen_full isnull;
select count(*) from poly_state where not(isvalid(gen_full));

alter table poly_state add column state_abrev text;
alter table poly_state add column state_name text;


update poly_state set state_abrev =
  ( select abrev from state_translation where 
        state_translation.module = substring (poly_state.state_module from 4 for 2));

update poly_state set state_name =
  ( select state_translation.name from state_translation where 
        state_translation.module = substring (poly_state.state_module from 4 for 2));