BEGIN;

-- create indexes
CREATE INDEX "gadm2_iso_idx" ON gadm2 (iso);
CREATE INDEX "gadm2_geom_gist" ON gadm2 USING GIST (geom);
CREATE INDEX "gadm2_name_0_idx" ON gadm2 (name_0);
CREATE INDEX "gadm2_name_1_idx" ON gadm2 (name_1);

-- physically reorder tuples on disk in country name index order
-- nb: i _think_ this helped, but am not 100% sure
CLUSTER gadm2 USING gadm2_name_0_idx;

-- create simplified country polygons
SELECT AddGeometryColumn('public', 'gadm2', 'simple_geom', 4326, 'MULTIPOLYGON', 2 );
UPDATE gadm2 SET simple_geom = (SELECT st_simplify(geom, 0.001));
CREATE INDEX "gadm2_simple_geom_gist" ON gadm2 USING gist (simple_geom);
-- ... and create geography versions ...
-- note workaround to exclude Antarctica because it has an invalid
-- western extent of -180.000015258789
ALTER TABLE gadm2 ADD COLUMN simple_geog geography;
UPDATE gadm2 SET simple_geog = simple_geom::geography WHERE name_0<>'Antarctica';
CREATE INDEX "gadm2_simple_geog_gist" ON gadm2 USING gist (simple_geog);

COMMIT;