my-eyes.sql

drop table if exists combined_geometries;

 with boston_area_geometries as
    ( select name,
             msa_code,
             geom
     from divisions
     inner join
         ( select distinct on (msa_code) msa_code
          from area_definitions ) ad on ad.msa_code = nctadvfp ),
      township_geometries as
    ( select name,
             msa_code,
             geom
     from townships
     inner join
         ( select distinct on (msa_code) msa_code
          from area_definitions ) ad on ad.msa_code = geoid
     where nectafp != '71650' -- Because Boston is special
 ),
      other_geometries as
    ( select msa_name,
             msa_code,
             st_union(geom)
     from counties
     inner join
         ( select distinct on (msa_code,
                               county_code) msa_name,
                                            msa_code,
                                            county_code,
                                            fips
          from area_definitions
          where township_name is null ) ad on ad.county_code = counties.countyfp
     and counties.statefp = fips
     group by msa_name,
              msa_code ),
      special_county_geometries as
    ( select msa_name,
             msa_code,
             st_union(geom)
     from counties
     inner join area_definitions ad on ad.county_code = counties.countyfp
     and counties.statefp = fips
     where msa_code in('5000001', '5000002', '3300002')
     group by msa_name,
              msa_code ),
      special_township_geometries as
    ( select name,
             msa_code,
             geom
     from townships
     inner join
         ( select distinct on (fips,
                               township_code) fips,
                                              msa_code
          from area_definitions ) ad on ad.msa_code = nectafp
     where msa_code in('3300002') )
select * into combined_geometries
from
    ( select *
     from boston_area_geometries
     union select *
     from township_geometries
     union select *
     from other_geometries
     union select *
     from special_county_geometries
     union select *
     from special_township_geometries ) og;