CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS postgis_raster;
CREATE EXTENSION dblink;

CREATE ROLE rdsadmin
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

CREATE ROLE taskingmanager
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

--spatial ref for pop dataset
INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 954009, 'esri', 54009, '+proj=moll +lon_0=0 +x_0=0 +y_0=0 +ellps=WGS84 +datum=WGS84 +units=m +no_defs ', 'PROJCS["World_Mollweide",GEOGCS["GCS_WGS_1984",DATUM["WGS_1984",SPHEROID["WGS_1984",6378137,298.257223563]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]],PROJECTION["Mollweide"],PARAMETER["False_Easting",0],PARAMETER["False_Northing",0],PARAMETER["Central_Meridian",0],UNIT["Meter",1],AUTHORITY["EPSG","54009"]]');


-- schema and search path
CREATE SCHEMA IF NOT EXISTS data_preparation;
CREATE SCHEMA IF NOT EXISTS pop;


ALTER DATABASE mm_stats SET search_path TO public,pop,data_preparation;

-- data_preparation.ne_10m_admin_0_countries definition

-- Drop table

-- DROP TABLE data_preparation.ne_10m_admin_0_countries;

CREATE TABLE data_preparation.ne_10m_admin_0_countries (
	id serial4 NOT NULL,
	geom geometry(multipolygon, 4326) NULL,
	featurecla varchar(15) NULL,
	scalerank int4 NULL,
	labelrank int4 NULL,
	sovereignt varchar(32) NULL,
	sov_a3 varchar(3) NULL,
	adm0_dif int4 NULL,
	"level" int4 NULL,
	"type" varchar(17) NULL,
	"admin" varchar(36) NULL,
	adm0_a3 varchar(3) NULL,
	geou_dif int4 NULL,
	geounit varchar(36) NULL,
	gu_a3 varchar(3) NULL,
	su_dif int4 NULL,
	subunit varchar(36) NULL,
	su_a3 varchar(3) NULL,
	brk_diff int4 NULL,
	"name" varchar(25) NULL,
	name_long varchar(36) NULL,
	brk_a3 varchar(3) NULL,
	brk_name varchar(32) NULL,
	brk_group varchar(17) NULL,
	abbrev varchar(13) NULL,
	postal varchar(4) NULL,
	formal_en varchar(52) NULL,
	formal_fr varchar(35) NULL,
	name_ciawf varchar(45) NULL,
	note_adm0 varchar(22) NULL,
	note_brk varchar(63) NULL,
	name_sort varchar(36) NULL,
	name_alt varchar(19) NULL,
	mapcolor7 int4 NULL,
	mapcolor8 int4 NULL,
	mapcolor9 int4 NULL,
	mapcolor13 int4 NULL,
	pop_est int8 NULL,
	pop_rank int4 NULL,
	gdp_md_est float8 NULL,
	pop_year int4 NULL,
	lastcensus int4 NULL,
	gdp_year int4 NULL,
	economy varchar(26) NULL,
	income_grp varchar(23) NULL,
	wikipedia int4 NULL,
	fips_10_ varchar(3) NULL,
	iso_a2 varchar(3) NULL,
	iso_a3 varchar(3) NULL,
	iso_a3_eh varchar(3) NULL,
	iso_n3 varchar(3) NULL,
	un_a3 varchar(4) NULL,
	wb_a2 varchar(3) NULL,
	wb_a3 varchar(3) NULL,
	woe_id int4 NULL,
	woe_id_eh int4 NULL,
	woe_note varchar(167) NULL,
	adm0_a3_is varchar(3) NULL,
	adm0_a3_us varchar(3) NULL,
	adm0_a3_un int4 NULL,
	adm0_a3_wb int4 NULL,
	continent varchar(23) NULL,
	region_un varchar(23) NULL,
	subregion varchar(25) NULL,
	region_wb varchar(26) NULL,
	name_len int4 NULL,
	long_len int4 NULL,
	abbrev_len int4 NULL,
	tiny int4 NULL,
	homepart int4 NULL,
	min_zoom float8 NULL,
	min_label float8 NULL,
	max_label float8 NULL,
	ne_id int8 NULL,
	wikidataid varchar(8) NULL,
	name_ar varchar(72) NULL,
	name_bn varchar(148) NULL,
	name_de varchar(46) NULL,
	name_en varchar(44) NULL,
	name_es varchar(44) NULL,
	name_fr varchar(54) NULL,
	name_el varchar(88) NULL,
	name_hi varchar(126) NULL,
	name_hu varchar(52) NULL,
	name_id varchar(46) NULL,
	name_it varchar(48) NULL,
	name_ja varchar(63) NULL,
	name_ko varchar(47) NULL,
	name_nl varchar(49) NULL,
	name_pl varchar(47) NULL,
	name_pt varchar(43) NULL,
	name_ru varchar(86) NULL,
	name_sv varchar(57) NULL,
	name_tr varchar(42) NULL,
	name_vi varchar(56) NULL,
	name_zh varchar(36) NULL,
	geom54009 geometry(multipolygon, 54009) NULL,
	region_name varchar NULL,
	region_id int4 NULL,
	CONSTRAINT ne_10m_admin_0_countries_pkey PRIMARY KEY (id)
);



-- data_preparation.ne_110m_admin_0_countries definition

-- Drop table

-- DROP TABLE data_preparation.ne_110m_admin_0_countries;

CREATE TABLE data_preparation.ne_110m_admin_0_countries (
	id serial4 NOT NULL,
	geom geometry(multipolygon, 4326) NULL,
	featurecla varchar(15) NULL,
	scalerank int4 NULL,
	labelrank int4 NULL,
	sovereignt varchar(32) NULL,
	sov_a3 varchar(3) NULL,
	adm0_dif int4 NULL,
	"level" int4 NULL,
	"type" varchar(17) NULL,
	"admin" varchar(35) NULL,
	adm0_a3 varchar(3) NULL,
	geou_dif int4 NULL,
	geounit varchar(35) NULL,
	gu_a3 varchar(3) NULL,
	su_dif int4 NULL,
	subunit varchar(35) NULL,
	su_a3 varchar(3) NULL,
	brk_diff int4 NULL,
	"name" varchar(24) NULL,
	name_long varchar(35) NULL,
	brk_a3 varchar(3) NULL,
	brk_name varchar(32) NULL,
	brk_group varchar(80) NULL,
	abbrev varchar(10) NULL,
	postal varchar(4) NULL,
	formal_en varchar(52) NULL,
	formal_fr varchar(35) NULL,
	name_ciawf varchar(33) NULL,
	note_adm0 varchar(22) NULL,
	note_brk varchar(36) NULL,
	name_sort varchar(35) NULL,
	name_alt varchar(14) NULL,
	mapcolor7 int4 NULL,
	mapcolor8 int4 NULL,
	mapcolor9 int4 NULL,
	mapcolor13 int4 NULL,
	pop_est int8 NULL,
	pop_rank int4 NULL,
	gdp_md_est float8 NULL,
	pop_year int4 NULL,
	lastcensus int4 NULL,
	gdp_year int4 NULL,
	economy varchar(26) NULL,
	income_grp varchar(23) NULL,
	wikipedia int4 NULL,
	fips_10_ varchar(3) NULL,
	iso_a2 varchar(3) NULL,
	iso_a3 varchar(3) NULL,
	iso_a3_eh varchar(3) NULL,
	iso_n3 varchar(3) NULL,
	un_a3 varchar(4) NULL,
	wb_a2 varchar(3) NULL,
	wb_a3 varchar(3) NULL,
	woe_id int4 NULL,
	woe_id_eh int4 NULL,
	woe_note varchar(167) NULL,
	adm0_a3_is varchar(3) NULL,
	adm0_a3_us varchar(3) NULL,
	adm0_a3_un int4 NULL,
	adm0_a3_wb int4 NULL,
	continent varchar(23) NULL,
	region_un varchar(23) NULL,
	subregion varchar(25) NULL,
	region_wb varchar(26) NULL,
	name_len int4 NULL,
	long_len int4 NULL,
	abbrev_len int4 NULL,
	tiny int4 NULL,
	homepart int4 NULL,
	min_zoom float8 NULL,
	min_label float8 NULL,
	max_label float8 NULL,
	ne_id int8 NULL,
	wikidataid varchar(7) NULL,
	name_ar varchar(57) NULL,
	name_bn varchar(93) NULL,
	name_de varchar(40) NULL,
	name_en varchar(35) NULL,
	name_es varchar(41) NULL,
	name_fr varchar(44) NULL,
	name_el varchar(88) NULL,
	name_hi varchar(97) NULL,
	name_hu varchar(40) NULL,
	name_id varchar(39) NULL,
	name_it varchar(36) NULL,
	name_ja varchar(36) NULL,
	name_ko varchar(33) NULL,
	name_nl varchar(42) NULL,
	name_pl varchar(47) NULL,
	name_pt varchar(39) NULL,
	name_ru varchar(86) NULL,
	name_sv varchar(28) NULL,
	name_tr varchar(41) NULL,
	name_vi varchar(56) NULL,
	name_zh varchar(33) NULL,
	CONSTRAINT ne_110m_admin_0_countries_pkey PRIMARY KEY (id)
);
CREATE INDEX sidx_ne_110m_admin_0_countries_geom ON data_preparation.ne_110m_admin_0_countries USING gist (geom);


-- data_preparation.ne_50m_admin_0_countries definition

-- Drop table

-- DROP TABLE data_preparation.ne_50m_admin_0_countries;

CREATE TABLE data_preparation.ne_50m_admin_0_countries (
	id serial4 NOT NULL,
	geom geometry(multipolygon, 4326) NULL,
	featurecla varchar(15) NULL,
	scalerank int4 NULL,
	labelrank int4 NULL,
	sovereignt varchar(32) NULL,
	sov_a3 varchar(3) NULL,
	adm0_dif int4 NULL,
	"level" int4 NULL,
	"type" varchar(17) NULL,
	"admin" varchar(35) NULL,
	adm0_a3 varchar(3) NULL,
	geou_dif int4 NULL,
	geounit varchar(35) NULL,
	gu_a3 varchar(3) NULL,
	su_dif int4 NULL,
	subunit varchar(35) NULL,
	su_a3 varchar(3) NULL,
	brk_diff int4 NULL,
	"name" varchar(25) NULL,
	name_long varchar(35) NULL,
	brk_a3 varchar(3) NULL,
	brk_name varchar(32) NULL,
	brk_group varchar(17) NULL,
	abbrev varchar(13) NULL,
	postal varchar(4) NULL,
	formal_en varchar(52) NULL,
	formal_fr varchar(35) NULL,
	name_ciawf varchar(45) NULL,
	note_adm0 varchar(22) NULL,
	note_brk varchar(51) NULL,
	name_sort varchar(35) NULL,
	name_alt varchar(14) NULL,
	mapcolor7 int4 NULL,
	mapcolor8 int4 NULL,
	mapcolor9 int4 NULL,
	mapcolor13 int4 NULL,
	pop_est int8 NULL,
	pop_rank int4 NULL,
	gdp_md_est float8 NULL,
	pop_year int4 NULL,
	lastcensus int4 NULL,
	gdp_year int4 NULL,
	economy varchar(26) NULL,
	income_grp varchar(23) NULL,
	wikipedia int4 NULL,
	fips_10_ varchar(3) NULL,
	iso_a2 varchar(3) NULL,
	iso_a3 varchar(3) NULL,
	iso_a3_eh varchar(3) NULL,
	iso_n3 varchar(3) NULL,
	un_a3 varchar(4) NULL,
	wb_a2 varchar(3) NULL,
	wb_a3 varchar(3) NULL,
	woe_id int4 NULL,
	woe_id_eh int4 NULL,
	woe_note varchar(167) NULL,
	adm0_a3_is varchar(3) NULL,
	adm0_a3_us varchar(3) NULL,
	adm0_a3_un int4 NULL,
	adm0_a3_wb int4 NULL,
	continent varchar(23) NULL,
	region_un varchar(23) NULL,
	subregion varchar(25) NULL,
	region_wb varchar(26) NULL,
	name_len int4 NULL,
	long_len int4 NULL,
	abbrev_len int4 NULL,
	tiny int4 NULL,
	homepart int4 NULL,
	min_zoom float8 NULL,
	min_label float8 NULL,
	max_label float8 NULL,
	ne_id int8 NULL,
	wikidataid varchar(8) NULL,
	name_ar varchar(72) NULL,
	name_bn varchar(125) NULL,
	name_de varchar(46) NULL,
	name_en varchar(44) NULL,
	name_es varchar(44) NULL,
	name_fr varchar(54) NULL,
	name_el varchar(88) NULL,
	name_hi varchar(123) NULL,
	name_hu varchar(41) NULL,
	name_id varchar(46) NULL,
	name_it varchar(44) NULL,
	name_ja varchar(63) NULL,
	name_ko varchar(47) NULL,
	name_nl varchar(46) NULL,
	name_pl varchar(47) NULL,
	name_pt varchar(43) NULL,
	name_ru varchar(86) NULL,
	name_sv varchar(42) NULL,
	name_tr varchar(42) NULL,
	name_vi varchar(56) NULL,
	name_zh varchar(36) NULL,
	CONSTRAINT ne_50m_admin_0_countries_pkey PRIMARY KEY (id)
);
CREATE INDEX sidx_ne_50m_admin_0_countries_geom ON data_preparation.ne_50m_admin_0_countries USING gist (geom);