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;