 		NOW() AT TIME ZONE 'Europe/Berlin' AS timestamp
 	FROM	model_draft.ego_grid_mv_griddistrict AS gd;
  */
+-- Type1
+UPDATE 	model_draft.ego_grid_mv_griddistrict AS t1
+SET  	type1 = t2.type1
+FROM	(SELECT	gd.subst_id,
+		COUNT(ST_PointOnSurface(typ.geom))::integer AS type1
+	FROM	model_draft.ego_grid_mv_griddistrict_type1 AS typ,
+		model_draft.ego_grid_mv_griddistrict AS gd
+	WHERE	gd.geom && typ.geom AND
+		ST_CONTAINS(gd.geom,ST_PointOnSurface(typ.geom))
+	GROUP BY gd.subst_id
+	)AS t2
+WHERE  	t1.subst_id = t2.subst_id;
+
+UPDATE 	model_draft.ego_grid_mv_griddistrict AS t1
+SET  	type1_cnt = t2.type_cnt
+FROM	(SELECT	gd.subst_id,
+		COUNT(ST_PointOnSurface(typ.geom))::integer AS type_cnt
+	FROM	model_draft.ego_political_boundary_hvmv_subst_per_gem_1_mview AS typ,
+		model_draft.ego_grid_mv_griddistrict AS gd
+	WHERE	gd.geom && typ.geom AND
+		ST_CONTAINS(gd.geom,ST_PointOnSurface(typ.geom))
+	GROUP BY gd.subst_id
+	)AS t2
+WHERE  	t1.subst_id = t2.subst_id;
+
+-- Type2
+UPDATE 	model_draft.ego_grid_mv_griddistrict AS t1
+SET  	type2 = t2.type2
+FROM	(SELECT	gd.subst_id,
+		COUNT(ST_PointOnSurface(typ.geom))::integer AS type2
+	FROM	model_draft.ego_grid_mv_griddistrict_type2 AS typ,
+		model_draft.ego_grid_mv_griddistrict AS gd
+	WHERE	gd.geom && typ.geom AND
+		ST_CONTAINS(gd.geom,ST_PointOnSurface(typ.geom))
+	GROUP BY gd.subst_id
+	)AS t2
+WHERE  	t1.subst_id = t2.subst_id;
+
+UPDATE 	model_draft.ego_grid_mv_griddistrict AS t1
+SET  	type2_cnt = t2.type_cnt
+FROM	(SELECT	gd.subst_id,
+		COUNT(ST_PointOnSurface(typ.geom))::integer AS type_cnt
+	FROM	model_draft.ego_grid_hvmv_substation_voronoi_cut AS typ,
+		model_draft.ego_grid_mv_griddistrict AS gd
+	WHERE	gd.geom && typ.geom AND
+		ST_CONTAINS(gd.geom,ST_PointOnSurface(typ.geom))
+	GROUP BY gd.subst_id
+	)AS t2
+WHERE  	t1.subst_id = t2.subst_id;
+
+
+-- Type3
+UPDATE 	model_draft.ego_grid_mv_griddistrict AS t1
+SET  	type3 = t2.type3
+FROM	(SELECT	gd.subst_id,
+		COUNT(ST_PointOnSurface(typ.geom))::integer AS type3
+	FROM	model_draft.ego_grid_mv_griddistrict_type3 AS typ,
+		model_draft.ego_grid_mv_griddistrict AS gd
+	WHERE	gd.geom && typ.geom AND
+		ST_CONTAINS(gd.geom,ST_PointOnSurface(typ.geom))
+	GROUP BY gd.subst_id
+	)AS t2
+WHERE  	t1.subst_id = t2.subst_id;
+
+UPDATE 	model_draft.ego_grid_mv_griddistrict AS t1
+SET  	type3_cnt = t2.type_cnt
+FROM	(SELECT	gd.subst_id,
+		COUNT(ST_PointOnSurface(typ.geom))::integer AS type_cnt
+	FROM	model_draft.ego_political_boundary_hvmv_subst_per_gem_3_mview AS typ,
+		model_draft.ego_grid_mv_griddistrict AS gd
+	WHERE	gd.geom && typ.geom AND
+		ST_CONTAINS(gd.geom,ST_PointOnSurface(typ.geom))
+	GROUP BY gd.subst_id
+	)AS t2
+WHERE  	t1.subst_id = t2.subst_id;
+
+
+-- Group
+UPDATE 	model_draft.ego_grid_mv_griddistrict
+SET  	"group" = (SELECT	
+		CASE
+			WHEN	type1 = '1' AND type2 = '0' AND type3 = '1' THEN 'A' -- lndlich
+			WHEN	type1 = '0' AND type2 = '1' AND type3 = '1' THEN 'B'
+			WHEN	type1 = '1' AND type2 = '0' AND type3 = '0' THEN 'C'
+			WHEN	type1 = '0' AND type2 = '1' AND type3 = '0' THEN 'D' -- stdtisch
+		END);
 
 
 DROP MATERIALIZED VIEW IF EXISTS political_boundary.bkg_vg250_6_gem_pts;

	ST_PointOnSurface(geom) AS geom
FROM	political_boundary.bkg_vg250_6_gem;


/* -- bkg_vg250_6_gem_clean_pts
DROP MATERIALIZED VIEW IF EXISTS model_draft.ego_political_boundary_bkg_vg250_6_gem_clean_pts;

	ags_0,
	ST_PointOnSurface(geom) AS geom
FROM	model_draft.ego_political_boundary_bkg_vg250_6_gem_clean; */

-- Gemeinde Parts
UPDATE 	model_draft.ego_grid_mv_griddistrict AS t1
SET  	gem_clean = t2.gem_clean
FROM	(SELECT	gd.subst_id,
		COUNT(ST_PointOnSurface(gem.geom))::integer AS gem_clean
	FROM	model_draft.ego_political_boundary_bkg_vg250_6_gem_clean AS gem,
		model_draft.ego_grid_mv_griddistrict AS gd
	WHERE	gd.geom && gem.geom AND
		ST_CONTAINS(gd.geom,ST_PointOnSurface(gem.geom))
	GROUP BY gd.subst_id
	)AS t2
WHERE  	t1.subst_id = t2.subst_id;

-- GD Area 3610
UPDATE 	model_draft.ego_grid_mv_griddistrict AS t1
SET  	area_ha = t2.area_ha
FROM	(SELECT	gd.subst_id,
		ST_AREA(gd.geom)/10000 AS area_ha
	FROM	model_draft.ego_grid_mv_griddistrict AS gd
	)AS t2
WHERE  	t1.subst_id = t2.subst_id;

-- LA Count
UPDATE 	model_draft.ego_grid_mv_griddistrict AS t1
SET  	la_count = t2.la_count
FROM	(SELECT	gd.subst_id,
		COUNT(ST_PointOnSurface(la.geom))::integer AS la_count
	FROM	model_draft.ego_demand_loadarea AS la,
		model_draft.ego_grid_mv_griddistrict AS gd
	WHERE	gd.geom && la.geom AND
		ST_CONTAINS(gd.geom,ST_PointOnSurface(la.geom))
	GROUP BY gd.subst_id
	)AS t2
WHERE  	t1.subst_id = t2.subst_id;

-- LA Area 3606
UPDATE 	model_draft.ego_grid_mv_griddistrict AS t1
SET  	la_area = t2.la_area
FROM	(SELECT	gd.subst_id,
		SUM(ST_AREA(la.geom)/10000) ::decimal(10,3) AS la_area
	FROM	model_draft.ego_demand_loadarea AS la,
		model_draft.ego_grid_mv_griddistrict AS gd
	WHERE	gd.geom && la.geom AND
		ST_CONTAINS(gd.geom,ST_PointOnSurface(la.geom))
	GROUP BY gd.subst_id
	)AS t2
WHERE  	t1.subst_id = t2.subst_id;

-- not LA Area (free_area)
UPDATE 	model_draft.ego_grid_mv_griddistrict AS t1
SET  	free_area = t2.free_area
FROM	(SELECT	gd.subst_id,
		SUM(gd.area_ha)-SUM(gd.la_area) AS free_area
	FROM	model_draft.ego_grid_mv_griddistrict as gd
	GROUP BY gd.subst_id
	)AS t2
WHERE  	t1.subst_id = t2.subst_id;

-- not LA Area (free_area)
UPDATE 	model_draft.ego_grid_mv_griddistrict AS t1
SET  	area_share = t2.area_share
FROM	(SELECT	gd.subst_id,
		SUM(gd.la_area)/SUM(gd.area_ha)*100 AS area_share
	FROM	model_draft.ego_grid_mv_griddistrict as gd
	GROUP BY gd.subst_id
	)AS t2
WHERE  	t1.subst_id = t2.subst_id;

SELECT	MAX(area_share) AS max,
	MIN(area_share) AS min
FROM	model_draft.ego_grid_mv_griddistrict ;


-- Consumption
UPDATE 	model_draft.ego_grid_mv_griddistrict AS t1
	SET  	consumption = t2.consumption
	FROM	(SELECT	gd.subst_id,
			SUM(la.sector_consumption_sum)::numeric AS consumption
		FROM	model_draft.ego_demand_loadarea AS la,
			model_draft.ego_grid_mv_griddistrict AS gd
		WHERE	gd.subst_id = la.subst_id
		GROUP BY gd.subst_id
		)AS t2
	WHERE  	t1.subst_id = t2.subst_id;
	
UPDATE 	model_draft.ego_grid_mv_griddistrict AS t1
	SET  	consumption_per_area = consumption *1000000 / area_ha;

-- test
SELECT	SUM(mvgd.consumption)
	FROM	model_draft.ego_grid_mv_griddistrict AS mvgd
UNION ALL
SELECT	SUM(la.sector_consumption_sum)
	FROM	model_draft.ego_demand_loadarea AS la;
	
UPDATE 	model_draft.ego_grid_mv_griddistrict AS t1
	SET  	consumption_per_area = t2.consumption_per_area
	FROM	(SELECT	gd.subst_id,
			SUM(la.sector_consumption_sum)::integer AS consumption
		FROM	model_draft.ego_grid_mv_griddistrict AS mvgd
		WHERE	gd.subst_id = la.subst_id
		GROUP BY gd.subst_id
		)AS t2
	WHERE  	t1.subst_id = t2.subst_id;

-- ego scenario log (version,io,schema_name,table_name,script_name,comment)
SELECT ego_scenario_log('v0.2.5','output','model_draft','ego_grid_mv_griddistrict','ego_paper_result.sql',' ');

	

-- Calculate statistics for BKG-vg250 
