
create table building_costs (
	years int,
    occupancy char(30),
    structures char(15),
    costs double precision,
    primary key ("structures", "occupancy")
);

create table building_csvr (
    occupancy char(30),
    csvr double precision,
    primary key ("occupancy")
);

create table building_structure_damage_rate (
    occupancy char(30),
    structures char(15),
    "0m" double precision,
    "0-0.3m" double precision,
    "0.3-0.6m" double precision,
    "0.6-0.9m" double precision,
    "0.9-1.2m" double precision,
    "1.2-1.5m" double precision,
    "1.5-1.8m" double precision,
    "1.8-2.1m" double precision,
    "2.1-2.4m" double precision,
    "2.4-2.7m" double precision,
    "2.7-3.0m" double precision,
    "3.0m-" double precision,
    primary key ("occupancy", "structures")
);

create table building_inner_damage_rate (
    occupancy char(30),
    "0m" double precision,
    "0-0.3m" double precision,
    "0.3-0.6m" double precision,
    "0.6-0.9m" double precision,
    "0.9-1.2m" double precision,
    "1.2-1.5m" double precision,
    "1.5-1.8m" double precision,
    "1.8-2.1m" double precision,
    "2.1-2.4m" double precision,
    "2.4-2.7m" double precision,
    "2.7-3.0m" double precision,
    "3.0m-" double precision,
    primary key ("occupancy")
);

create table building_entrance (
    occupancy char(30),
    height double precision,
    primary key ("occupancy")
);

create table building_depreciation(
    occupancy char(30),
    structures char(15),
    persisting_period double precision,
    primary key ("structures", "occupancy")
);



create table population_occurrence_probability (
	damage_type char(11),
	hierarchy char(22),
	"0-0.9m" double precision,
	"0.9m-" double precision,
	primary key ("hierarchy")
);

create table population_unit_cost (
	year int,
	life_loss int,
	victim_loss int,
	primary key ("year")
);



CREATE TABLE agriculture_month_price_flow (
	years integer NOT NULL,
	type character(11) NOT NULL,
	farmland character(5) NOT NULL,
	crops character(9) not null,
	may double precision,
	jun double precision, 
	jul double precision,
	aug double precision
);

CREATE TABLE agriculture_price_flow (
	years integer NOT NULL,
	farmland character(5) NOT NULL,
	crops character(9) not null,
	stndr_prd_cst double precision,
	input_prd_cst double precision, 
	stndr_income double precision,
	expct_netpr double precision
);

CREATE TABLE agriculture_crop_dmg_func (
	farmland character(5) NOT NULL,
	crops character(9) not null,
	flood_period character(4) not null,
	"0m" double precision,
	"0-0.3m" double precision,
	"0.3-0.6m" double precision,
	"0.6-0.9m" double precision,
	"0.9-1.2m" double precision,
	"1.2-1.5m" double precision,
	"1.5-1.8m" double precision,
	"1.8-2.1m" double precision,
	"2.1-2.4m" double precision,
	"2.4-2.7m" double precision,
	"2.7-3.0m" double precision,
	"3.0m-" double precision,
	PRIMARY KEY (farmland, crops, flood_period)
);

CREATE TABLE agriculture_frm_dmg_func (
	farmland character(5) NOT NULL,
	"0m" double precision,
	"0-0.3m" double precision,
	"0.3-0.6m" double precision,
	"0.6-0.9m" double precision,
	"0.9-1.2m" double precision,
	"1.2-1.5m" double precision,
	"1.5-1.8m" double precision,
	"1.8-2.1m" double precision,
	"2.1-2.4m" double precision,
	"2.4-2.7m" double precision,
	"2.7-3.0m" double precision,
	"3.0m-" double precision,
	PRIMARY KEY (farmland)
);

CREATE TABLE agriculture_dmage_unit (
	unit double percision
)

/*
 * COPY building_costs_2018(structures, occupancy, occupancy_code, costs) 
 * FROM 'C:/Users/Administrator/Desktop/새 폴더/통합 문서1.CSV' ENCODING 'EUC_KR' DELIMITER ',' CSV HEADER;
 */
ALTER TABLE inv_building_2018 ALTER COLUMN geometry TYPE geometry(MultiPoint,5179);
ALTER TABLE inv_car_2018
	ALTER COLUMN geometry TYPE geometry(MULTIPOLYGON, 변경 Srid) USING ST_Transform(ST_SetSRID(geometry, 기존 Srid), 변경 Srid);
SELECT updategeometrysrid('inv_farmland_2018', 'geometry', 5179);
	
CREATE INDEX idx_inv_building_2018_sig_cd ON inv_building_2018(sig_cd text_pattern_ops);
CREATE INDEX idx_inv_agriculture_2018_pnu ON inv_farmland_2018(pnu text_pattern_ops);
CREATE INDEX idx_inv_population_2018_tot_reg_cd ON inv_population_2018(tot_reg_cd text_pattern_ops);
CREATE INDEX idx_inv_car_2018_tot_reg_cd ON inv_car_2018(tot_reg_cd text_pattern_ops);

CREATE INDEX idx_inv_building_2018_geometry ON inv_building_2018 USING GIST (geometry);
CREATE INDEX idx_inv_agriculture_2018_geometry ON inv_farmland_2018 USING GIST (geometry);
CREATE INDEX idx_inv_population_2018_geometry ON inv_population_2018 USING GIST (geometry);
CREATE INDEX idx_inv_car_2018_geometry ON inv_car_2018 USING GIST (geometry);

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_inv_building_2018 ON inv_building_2018 USING gin (l_admin gin_trgm_ops, sig_cd gin_trgm_ops);
CREATE INDEX idx_inv_agriculture_2018 ON inv_farmland_2018 USING gin (l_admin gin_trgm_ops, pnu10 gin_trgm_ops);
CREATE INDEX idx_inv_population_2018 ON inv_population_2018 USING gin (l_admin gin_trgm_ops, tot_reg_cd gin_trgm_ops);
CREATE INDEX idx_inv_car_2018 ON inv_car_2018 USING gin (l_admin gin_trgm_ops, tot_reg_cd gin_trgm_ops);