Re-nest geometries for Ohio

renest.sql

-- Gather the county geometries from the blocks
select b.id, b.name, st_union(a.geom) as geom into temp new_county_geoms from redistricting_geounit as a join redistricting_geounit as b on a.geom && b.geom where b.geolevel_id = 3 and a.geolevel_id = 1 and position(b.portable_id in a.portable_id) = 1 group by b.id, b.name;

-- Index the id column for a faster join when updating
create index new_county_id on new_county_geoms (id);

-- Update your counties
update redistricting_geounit as a set geom = multi(b.geom) from new_county_geoms as b where a.id = b.id;

-- Gather the place geometries from the blocks
select b.id, b.name, st_union(a.geom) as geom into temp new_place_geoms from redistricting_geounit as a join redistricting_geounit as b on a.geom && b.geom where b.geolevel_id = 2 and a.geolevel_id = 1 and position(b.tree_code in a.tree_code) = 1 group by b.id, b.name;

-- Index the id column for a faster join when updating
create index new_place_id on new_place_geoms (id);

-- Update your places
update redistricting_geounit as a set geom = multi(b.geom) from new_place_geoms as b where a.id = b.id;