 		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;

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

-- Calculate statistics for BKG-vg250 

	ST_PointOnSurface(geom) AS geom
FROM	political_boundary.bkg_vg250_6_gem;

-- Gemeinden
UPDATE 	model_draft.ego_grid_mv_griddistrict AS t1
SET  	gem = t2.gem
FROM	(SELECT	gd.subst_id,
		COUNT(ST_PointOnSurface(gem.geom))::integer AS gem
	FROM	political_boundary.bkg_vg250_6_gem AS gem,
		model_draft.ego_grid_mv_griddistrict AS gd
	WHERE	gd.geom && ST_TRANSFORM(gem.geom,3035) AND
		ST_CONTAINS(gd.geom,ST_PointOnSurface(ST_TRANSFORM(gem.geom,3035)))
	GROUP BY gd.subst_id
	)AS t2
WHERE  	t1.subst_id = t2.subst_id;

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