 	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;

WHERE	area_ha > '500'; */

/* 
-- Schnittlängen (Umrisse)
SELECT	'Raw LA' AS name,
	count(la.geom) AS number,
	ST_Perimeter(ST_Collect(la.geom))/1000000 AS perimeter_in_tkm 

		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;
