 
 
 -- Add population
-CREATE TABLE IF NOT EXISTS calc_ego_grid_district.ego_deu_LV_grid_district
+CREATE TABLE IF NOT EXISTS model_draft."ego_grid_lv_grid_district"
 (
   id serial NOT NULL,
   geom geometry(Polygon,3035),

  area_ha integer,
  pop_density integer,
  structure_type text,
  CONSTRAINT ego_deu_lv_grid_district_pkey PRIMARY KEY (id)
);

TRUNCATE TABLE 	calc_ego_grid_district.ego_deu_LV_grid_district;

WITH pop AS (
	SELECT SUM(pts.population) AS population, grid_district.id AS grid_district_id
	FROM 	orig_destatis.zensus_population_per_ha AS pts, 
		calc_ego_grid_district.ego_deu_lv_grid_district_withoutpop AS grid_district
	WHERE ST_CONTAINS(grid_district.geom,pts.geom)
		AND pts.population > 0
	GROUP BY grid_district.id
) 
INSERT INTO		calc_ego_grid_district.ego_deu_LV_grid_district (geom,id,load_area_id,population)

SELECT t1.*,pop.population 
FROM calc_ego_grid_district.ego_deu_lv_grid_district_withoutpop AS t1 LEFT OUTER JOIN pop
	ON t1.id = pop.grid_district_id;

UPDATE calc_ego_grid_district.ego_deu_LV_grid_district
SET population = 0
WHERE population IS NULL;




-- Lösche sehr kleine Gebiete; Diese sind meistens Bugs in den Grenzverläufen
DELETE FROM model_draft."ego_grid_lv_cut" WHERE ST_AREA(geom) < 0.001;
	
-- Count substations per grid district



UPDATE 	model_draft."ego_grid_lv_cut" AS t1
SET ont_count = 0;

UPDATE 	model_draft."ego_grid_lv_cut" AS t1
SET  	ont_count = t2.count
FROM (SELECT COUNT (onts.geom) AS count,dist.id AS id
	FROM model_draft."ego_grid_mvlv_substation" AS onts, model_draft."ego_grid_lv_cut" AS dist
	WHERE ST_CONTAINS (dist.geom,onts.geom)
	GROUP BY dist.id
      ) AS t2
WHERE t1.id = t2.id;


UPDATE 	model_draft."ego_grid_lv_cut" AS t1
SET  	ont_id = t2.id
FROM model_draft."ego_grid_mvlv_substation" AS t2
WHERE ST_CONTAINS(t1.geom,t2.geom);

-- Add merge info


UPDATE 	model_draft."ego_grid_lv_cut" AS t1
SET merge_id = t2.merge_id
FROM (
	WITH mins AS (
		SELECT regions.id AS regions_id, MIN(ST_DISTANCE(ST_CENTROID(regions.geom),onts.geom)) AS distance
		FROM
			model_draft."ego_grid_mvlv_substation" AS onts 
			INNER JOIN 
			model_draft."ego_grid_lv_cut" AS regions ON onts.load_area_id = regions.load_area_id
			INNER JOIN 
			model_draft."ego_grid_lv_cut" AS regions2 ON ST_INTERSECTS(regions.geom,regions2.geom)
		WHERE ST_CONTAINS (regions2.geom,onts.geom)
		GROUP BY regions_id

