 -- 	SELECT	lg.uid AS uid,
 -- 		ST_IsValid(lg.geom) AS error,
 -- 		lg.geom AS geom
--- 	FROM	orig_geo_rli.osm_deu_polygon_urban_buffer100_unbuffer AS lg
+-- 	FROM	orig_geo_ego.osm_deu_polygon_urban_buffer100_unbuffer AS lg
 -- 	) AS test
 -- WHERE	test.error = FALSE;
 -- 

---------- ---------- ----------

-- "Calculate Sector Retail"   (OK!) -> 177.000ms =10.157
UPDATE 	orig_geo_rli.osm_deu_polygon_urban_buffer100_unbuffer AS t1
SET  	sector_area_retail = t2.sector_area,
	sector_count_retail = t2.sector_count,
	sector_share_retail = t2.sector_area / t2.area_ha

		la.area_ha < 1;

-- "Create Index GIST (geom_buffer)"   (OK!) -> 150ms =0
CREATE INDEX  	eosm_deu_polygon_urban_buffer100_unbuffer_check_res_combination_mview_geom_buffer_idx
    ON    	orig_geo_ego.osm_deu_polygon_urban_buffer100_unbuffer_check_res_combination_mview
    USING     	GIST (geom_buffer);

-- "Grant oeuser"   (OK!) -> 100ms =0
GRANT ALL ON TABLE 	orig_geo_ego.osm_deu_polygon_urban_buffer100_unbuffer_check_res_combination_mview TO oeuser WITH GRANT OPTION;
ALTER TABLE		orig_geo_ego.osm_deu_polygon_urban_buffer100_unbuffer_check_res_combination_mview OWNER TO oeuser;

-- "Remove Residential Combination"   (OK!) 2.500ms =28.647
DELETE FROM	orig_geo_ego.osm_deu_polygon_urban_buffer100_unbuffer AS la
	WHERE	(la.sector_area_retail IS NULL AND
		la.sector_area_industrial IS NULL AND
		la.sector_area_agricultural IS NULL) AND
		la.ioer_sum = 0 AND
		la.area_ha < 1;


---------- ---------- ----------
-- "Set Zeros"
---------- ---------- ----------

-- "Add NULL"   (OK!) -> 4.000ms =28.066
UPDATE 	orig_geo_ego.osm_deu_polygon_urban_buffer100_unbuffer
SET	zensus_sum 	= 0
WHERE	zensus_sum 	IS NULL;

-- "Add NULL"   (OK!) ->  10.000ms =28.066
UPDATE 	orig_geo_ego.osm_deu_polygon_urban_buffer100_unbuffer
SET	zensus_count 	= 0
WHERE	zensus_count 	IS NULL;

-- "Add NULL"   (OK!) ->  11.000ms =28.066
UPDATE 	orig_geo_ego.osm_deu_polygon_urban_buffer100_unbuffer
SET	zensus_density 	= 0
WHERE	zensus_density 	IS NULL;

---------- ---------- ----------

-- "Add NULL"   (OK!) ->  19.000ms =120.896
UPDATE 	orig_geo_ego.osm_deu_polygon_urban_buffer100_unbuffer
SET	ioer_sum 	= 0
WHERE	ioer_sum 	IS NULL;

-- "Add NULL"   (OK!) ->  21.000ms =120.896
UPDATE 	orig_geo_ego.osm_deu_polygon_urban_buffer100_unbuffer
SET	ioer_count 	= 0
WHERE	ioer_count 	IS NULL;

-- "Add NULL"   (OK!) ->  22.000ms =120.896
UPDATE 	orig_geo_ego.osm_deu_polygon_urban_buffer100_unbuffer
SET	ioer_density 	= 0
WHERE	ioer_density 	IS NULL;

---------- ---------- ----------

-- "Add NULL"   (OK!) ->  16.000ms =57.330
UPDATE 	orig_geo_ego.osm_deu_polygon_urban_buffer100_unbuffer
SET	sector_share_residential 	= 0
WHERE	sector_share_residential 	IS NULL;

-- "Add NULL"   (OK!) ->  39.000ms =158.513
UPDATE 	orig_geo_ego.osm_deu_polygon_urban_buffer100_unbuffer
SET	sector_share_retail 	= 0
WHERE	sector_share_retail 	IS NULL;

-- "Add NULL"   (OK!) ->  43.000ms =147.637
UPDATE 	orig_geo_ego.osm_deu_polygon_urban_buffer100_unbuffer
SET	sector_share_industrial 	= 0
WHERE	sector_share_industrial 	IS NULL;

-- "Add NULL"   (OK!) ->  54.000ms =168.670
UPDATE 	orig_geo_ego.osm_deu_polygon_urban_buffer100_unbuffer
SET	sector_share_agricultural 	= 0
WHERE	sector_share_agricultural 	IS NULL;


---------- ---------- ---------- ---------- ---------- ----------
-- "Write Results"
---------- ---------- ---------- ---------- ---------- ----------

-- "Create Table"   (OK!) 20.000ms =168.670
DROP TABLE IF EXISTS	orig_geo_ego.ego_deu_osm_loadarea CASCADE;
CREATE TABLE 		orig_geo_ego.ego_deu_osm_loadarea AS
	SELECT	la.*
	FROM	orig_geo_ego.osm_deu_polygon_urban_buffer100_unbuffer AS la
ORDER BY 	la.uid;

-- "Add ID (la_id)"   (OK!) 13.000ms =0
ALTER TABLE 	orig_geo_ego.ego_deu_osm_loadarea
	ADD COLUMN la_id SERIAL;

-- "Set PK (la_id)"   (OK!) 600ms =0
ALTER TABLE 	orig_geo_ego.ego_deu_osm_loadarea
	ADD PRIMARY KEY (la_id);

-- "Grant oeuser"   (OK!) -> 100ms =0
GRANT ALL ON TABLE 	orig_geo_ego.ego_deu_osm_loadarea TO oeuser WITH GRANT OPTION;
ALTER TABLE		orig_geo_ego.ego_deu_osm_loadarea OWNER TO oeuser;

-- "Create Index GIST (geom)"   (OK!) -> 2.000ms =0
CREATE INDEX  	ego_deu_osm_loadarea_geom_idx
    ON    	orig_geo_ego.ego_deu_osm_loadarea
    USING     	GIST (geom);

-- "Create Index GIST (geom_surfacepoint)"   (OK!) -> 2.000ms =0
CREATE INDEX  	ego_deu_osm_loadarea_geom_surfacepoint_idx
    ON    	orig_geo_ego.ego_deu_osm_loadarea
    USING     	GIST (geom_surfacepoint);

-- "Create Index GIST (geom_centroid)"   (OK!) -> 2.000ms =0
CREATE INDEX  	ego_deu_osm_loadarea_geom_centroid_idx
    ON    	orig_geo_ego.ego_deu_osm_loadarea
    USING     	GIST (geom_centroid);

-- "Create Index GIST (geom_buffer)"   (OK!) -> 2.000ms =0
CREATE INDEX  	ego_deu_osm_loadarea_geom_buffer_idx
    ON    	orig_geo_ego.ego_deu_osm_loadarea
    USING     	GIST (geom_buffer);


---------- ---------- ----------
-- "Create SPF"   2016-03-24 11:30
---------- ---------- ----------

-- "Create Table SPF"   (OK!) 1.000ms =719
DROP TABLE IF EXISTS  	orig_geo_ego.ego_deu_osm_loadarea_spf;
CREATE TABLE         	orig_geo_ego.ego_deu_osm_loadarea_spf AS
	SELECT	la.*
	FROM	orig_geo_ego.ego_deu_osm_loadarea AS la
	WHERE	nuts='DE27C' OR nuts='DE274';

-- "Ad PK"   (OK!) 150ms =0
ALTER TABLE	orig_geo_ego.ego_deu_osm_loadarea_spf
	ADD PRIMARY KEY (la_id);

-- "Create Index GIST (geom)"   (OK!) -> 100ms =0
CREATE INDEX  	ego_deu_osm_loadarea_spf_geom_idx
	ON	orig_geo_ego.ego_deu_osm_loadarea_spf
	USING	GIST (geom);

-- "Create Index GIST (geom_surfacepoint)"   (OK!) -> 150ms =0
CREATE INDEX  	ego_deu_osm_loadarea_spf_geom_surfacepoint_idx
    ON    	orig_geo_ego.ego_deu_osm_loadarea_spf
    USING     	GIST (geom_surfacepoint);

-- "Create Index GIST (geom_centroid)"   (OK!) -> 150ms =0
CREATE INDEX  	ego_deu_osm_loadarea_spf_geom_centroid_idx
    ON    	orig_geo_ego.ego_deu_osm_loadarea_spf
    USING     	GIST (geom_centroid);

-- "Create Index GIST (geom_buffer)"   (OK!) -> 150ms =0
CREATE INDEX  	ego_deu_osm_loadarea_spf_geom_buffer_idx
    ON    	orig_geo_ego.ego_deu_osm_loadarea_spf
    USING     	GIST (geom_buffer);

-- "Grant oeuser"   (OK!) -> 100ms =0
GRANT ALL ON TABLE 	orig_geo_ego.ego_deu_osm_loadarea_spf TO oeuser WITH GRANT OPTION;
ALTER TABLE		orig_geo_ego.ego_deu_osm_loadarea_spf OWNER TO oeuser;


---------- ---------- ---------- ---------- ---------- ----------
-- "Consumption (Ilka)"
---------- ---------- ---------- ---------- ---------- ----------

-- CREATE TABLE orig_geo_ego.znes_deu_consumption_spf AS
-- 	SELECT	la_id,
-- 		sector_consumption_residential,
-- 		sector_consumption_retail,
-- 		sector_consumption_industrial,
-- 		sector_consumption_agricultural
-- 	FROM	orig_geo_ego.ego_deu_osm_loadarea;
-- 
-- ALTER TABLE orig_geo_ego.rli_deu_consumption_spf
-- 	ADD PRIMARY KEY (la_id);
-- 
-- -- "Grant oeuser"   (OK!) -> 100ms =0
-- GRANT ALL ON TABLE 	orig_geo_ego.znes_deu_consumption_spf TO oeuser WITH GRANT OPTION;
-- ALTER TABLE		orig_geo_ego.znes_deu_consumption_spf OWNER TO oeuser;