   OWNER TO oeuser;
 
 -----
--- Add information on corresponding bus (subst_id and otg_id) to load areas
+-- Add information on corresponding bus (subst_id and otg_id) and un_id to load tables
 -----
 
-ALTER TABLE calc_ego_loads.ego_deu_load_area
+
+-- Identify corresponding bus for small scale consumer (ssc) with the help of grid districts and add un_id
+ALTER TABLE calc_ego_loads.ego_deu_consumption
 	ADD COLUMN otg_id bigint;
 
--- Identify corresponding bus with the help of grid districts (large scale consumer are not considered seperately yet) 
 
-UPDATE calc_ego_loads.ego_deu_load_area a
+UPDATE calc_ego_loads.ego_deu_consumption a
 	SET otg_id = b.otg_id
 	FROM calc_ego_substation.ego_deu_substations b
 	WHERE a.subst_id = b.id; 
 
--- Add data to pf_load_single 
+UPDATE calc_ego_loads.ego_deu_consumption a
+	SET un_id = b.un_id 
+	FROM orig_geo_powerplants.loads_total b
+	WHERE a.id = b.ssc_id; 
+
+-- Identify corresponding bus for large scale consumer (lsc) with the help of ehv-voronoi and add un_id
+
+ALTER TABLE calc_ego_loads.large_scale_consumer
+	ADD COLUMN subst_id bigint,
+	ADD COLUMN otg_id bigint
+	ADD COLUMN un_id;
+
+UPDATE calc_ego_loads.large_scale_consumer a
+	SET subst_id = b.subst_id
+	FROM calc_ego_substation.ego_deu_voronoi_ehv b
+	WHERE ST_Intersects (ST_Transform(a.geom,4326), b.geom) =TRUE;
+
+UPDATE calc_ego_loads.large_scale_consumer a
+	SET otg_id = b.otg_id
+	FROM calc_ego_substation.ego_deu_substations b
+	WHERE a.subst_id = b.id; 
+
+UPDATE calc_ego_loads.large_scale_consumer a
+	SET un_id = b.un_id 
+	FROM orig_geo_powerplants.loads_total b
+	WHERE a.id = b.lsc_id; 
+
+-------------
+-- Insert load data into powerflow schema, that contains all generators seperately 
+-------------
+
+-- Add data for small scale consumer to pf_load_single 
 
 INSERT INTO calc_ego_loads.pf_load_single (load_id, bus, e_annual)
-	SELECT id, otg_id, sector_consumption_sum
-	FROM calc_ego_loads.ego_deu_load_area; 
+	SELECT un_id, otg_id, (sector_consumption_residential+sector_consumption_retail+sector_consumption_industrial+sector_consumption_agricultural)
+	FROM calc_ego_loads.ego_deu_consumption; 
 
+-- Add data for large scale consumer to pf_load_single 
 
+INSERT INTO calc_ego_loads.pf_load_single (load_id, bus, e_annual)
+	SELECT un_id, otg_id, consumption
+	FROM calc_ego_loads.large_scale_consumer; 
+
+-------------
 -- Aggregate load per bus and insert into hv_powerflow schema 
-
+-------------
 
 INSERT INTO calc_ego_hv_powerflow.load (e_annual, load_id, bus)
 SELECT sum(e_annual), bus, bus 

	FROM 
		(SELECT id, otg_id FROM calc_ego_substation.ego_deu_substations) 
		AS result, calc_ego_loads.ego_demand_per_transition_point b
	WHERE b.id = result.id
/* DROP TABLE IF EXISTS orig_geo_powerplants.loads_total;

CREATE TABLE orig_geo_powerplants.loads_total
(
  un_id serial NOT NULL, 
  ssc_id integer, 
  lsc_id integer,
  geom geometry(Point,4326),
  CONSTRAINT generators_total_pkey PRIMARY KEY (un_id)
);

ALTER TABLE orig_geo_powerplants.loads_total
	OWNER TO oeuser; */
	
DELETE FROM orig_geo_powerplants.loads_total; 

INSERT INTO orig_geo_powerplants.loads_total (ssc_id, geom) 
	SELECT id, geom
	FROM calc_ego_loads.ego_deu_consumption; 

INSERT INTO orig_geo_powerplants.loads_total (lsc_id, geom) 
	SELECT id, geom
	FROM calc_ego_loads.large_scale_consumer; 

CREATE INDEX loads_total_idx
  ON orig_geo_powerplants.loads_total
  USING gist
  (geom);



-----
-- Create table to assign single load areas to a bus
-----
