/*
*****************************************
�bungsunterlage f�r Workshop FOSSGIS 2010
*****************************************
*/
PostGIS l�uft auf dem Port 5433 anstatt 5432
psql -U postgres postgres
/*
*****************************************
START DB anlegen und Daten laden
*****************************************
*/
--CREATE DATABASE fossgis_2010 encoding 'UTF8';
CREATE DATABASE fossgis_2010 encoding 'UTF8' template postgis_template;

--SELECT DropGeometryTable('adressen');
CREATE TABLE adressen(
	gid serial PRIMARY KEY,
	fk_stadtteil smallint REFERENCES bezirk_fossgis_2010(stadtteil),
	fk_strschl int, --REFERENCES strassenschluessel_xxx(strschl),
	hs_nr varchar(5),
	kwbez smallint,
	stimmbezirk varchar(4),
	herkunft varchar(15)
	);
SELECT AddGeometryColumn('', '','adressen','the_geom','31467','POLYGON',2);
ALTER TABLE adressen ADD CONSTRAINT strschl_hsnr_key unique(fk_strschl, hs_nr);


INSERT INTO adressen(gid, fk_stadtteil, fk_strschl, hs_nr, herkunft, the_geom)
	(SELECT gid, stadtteil, strschl, hs_nr, herkunft, the_geom FROM adressen_fossgis_2010);


CREATE OR REPLACE VIEW v_strschl_hs_stibez AS
--(SELECT c.fk_strschl, c.hs_nr, c.stbez, c.wbez FROM adressen,
	(SELECT a.fk_strschl, a.hs_nr, b.stbez, b.wbez FROM adressen a, wahlbezirke_fossgis_2010 b
		WHERE a.fk_strschl = b.straschl
			AND
			(
				(
					(round(to_number(a.hs_nr||'.0', '999D9'),0) >= round(to_number(b.von_u_hs||'.0', '999D9'),0) AND
					round(to_number(a.hs_nr||'.0', '999D9'),0) <= round(to_number(b.bis_u_hs||'.0', '999D9'),0) AND
					(round(to_number(a.hs_nr||'.0', '999D9') / 2,1)::text) LIKE '%.5')
				OR
					(round(to_number(a.hs_nr||'.0', '999D9'),0) >= round(to_number(b.von_g_hs||'.0', '999D9'),0) AND
					round(to_number(a.hs_nr||'.0', '999D9'),0) <= round(to_number(b.bis_g_hs||'.0', '999D9'),0) AND
					(round(to_number(a.hs_nr||'.0', '999D9') / 2,1)::text) LIKE '%.0')
				)
			)
	)-- AS c
--WHERE adressen.fk_strschl||'.'||adressen.hs_nr = c.fk_strschl||'.'||c.hs_nr)
;

UPDATE adressen b SET stimmbezirk = a.stbez, kwbez = a.wbez FROM v_strschl_hs_stibez a
	WHERE
		((a.hs_nr,a.fk_strschl) = (b.hs_nr,b.fk_strschl)) AND b.stimmbezirk IS NULL
;

/*
*****************************************
2.Schritt Geometrie erstellen / ST_Multi, ST_Union
*****************************************
*/
\timing
--SELECT DropGeometryTable('stimmbezirke');
CREATE TABLE stimmbezirke(gid serial PRIMARY KEY,
	kwbez smallint,
	stimmbezirk varchar(4));
SELECT AddGeometryColumn('', '','stimmbezirke','the_geom','31467','MULTIPOLYGON',2);

INSERT INTO stimmbezirke(kwbez, stimmbezirk, the_geom)
	(SELECT a.wbez, b.stimmbezirk, ST_Multi(ST_Union(b.the_geom)) FROM
			adressen b JOIN wahlbezirke_fossgis_2010 a ON ((b.stimmbezirk)::int = a.stbez)
		WHERE
			b.kwbez >= 101 AND b.kwbez <= 104
		GROUP BY b.stimmbezirk, a.wbez
	)
;

/*
************************************************************
3.Schritt Geometrien stutzen  /  ST_Multi, ST_Intersection, ST_Union
************************************************************
*/

UPDATE stimmbezirke c SET the_geom = foo.the_geom FROM 
	(SELECT a.gid, ST_Multi(ST_Intersection(a.the_geom ,ST_Union(b.the_geom))) AS the_geom
		FROM stimmbezirke a, bezirk_fossgis_2010 b
		GROUP BY a.the_geom, a.gid
	) foo
	WHERE c.gid = foo.gid;

SELECT gid, ST_NumGeometries(the_geom), ST_NumInteriorRings(the_geom) FROM stimmbezirke;
/*
*****************************************************************************
4.Schritt Geometrien f�r die Enklaven erzeugen  /  ST_Difference, ST_Union, ST_Dump
*****************************************************************************
*/
--SELECT DropGeometryTable('enklave');
CREATE TABLE enklave(gid serial PRIMARY KEY,
	stadtbezirk smallint);
SELECT AddGeometryColumn('', '','enklave','the_geom','31467','MULTIPOLYGON',2);

INSERT INTO enklave (stadtbezirk, the_geom)
	(SELECT b.stadtbezirk,
			(ST_Difference(ST_Union(b.the_geom), ST_Union(a.the_geom))) AS the_geom
		FROM stimmbezirke a, bezirk_fossgis_2010  b
	GROUP BY b.stadtbezirk
	)
;

--SELECT DropGeometryTable('enklave_n');
CREATE TABLE enklave_n(gid serial PRIMARY KEY,
	stadtbezirk smallint);
SELECT AddGeometryColumn('', '','enklave_n','the_geom','31467','POLYGON',2);

INSERT INTO enklave_n (gid, stadtbezirk, the_geom)
	(SELECT (ST_Dump(the_geom)).path[1] AS gid, stadtbezirk,
			(ST_Dump(the_geom)).geom AS the_geom
		FROM enklave
	)
;

/*
*****************************************************************************
5.Schritt Probleme untersuchen  /  ST_IsValidReason, ST_Area, ST_Buffer, ST_Dump
*****************************************************************************
*/


SELECT gid, ST_IsValid(the_geom), ST_Area(the_geom) FROM enklave_n;

--ST_Summary(the_geom)

SELECT gid, ST_IsValid(the_geom),
		ST_IsValid(ST_Buffer(the_geom, 0.0)),
		ST_Area(the_geom),
		ST_Area(ST_Buffer(the_geom, 0.0))
	FROM enklave_n;


SELECT gid, 
	(substring(ST_IsValidReason(the_geom) FROM position('[' in ST_IsValidReason(the_geom)) + 1 FOR 12))::numeric AS x,
	(substring(ST_IsValidReason(the_geom) FROM position(' ' in ST_IsValidReason(the_geom)) + 1 FOR 12))::numeric AS y
	FROM enklave_n WHERE ST_IsValidReason(the_geom) LIKE 'Self-intersection%';


SELECT path, ST_AsText(geom) FROM
(SELECT (ST_Dump(the_geom)).* FROM 
	(SELECT the_geom FROM enklave_n WHERE gid = 1) the_geom) foo;

/*
*****************************************************************************
6.Schritt Enklaven zuordnen  /  ST_Difference, ST_Union, ST_Dump
*****************************************************************************
*/

ALTER TABLE enklave_n ADD COLUMN stimmbezirk varchar(4);


UPDATE enklave_n SET stimmbezirk = NULL;
--Variante 1a: L�sung �ber die L�nge der gemeinsamen Grenze
UPDATE enklave_n c SET stimmbezirk = foo3.stimmbezirk FROM
		(SELECT foo.gid, foo.stimmbezirk FROM
			(SELECT distinct a.stimmbezirk, b.gid,
					(LENGTH(ST_Intersection(b.the_geom, a.the_geom)))
				FROM stimmbezirke a, enklave_n b
			GROUP BY b.gid, b.the_geom, a.the_geom, a.stimmbezirk
			HAVING ST_Overlaps(a.the_geom, ST_Buffer(b.the_geom, 0.01)) AND ST_IsValid(b.the_geom) = 't'
			) AS foo
		WHERE foo.gid = foo.gid AND foo.length in
			(SELECT max(foo2.length) FROM
				(SELECT b.gid,
						(LENGTH(ST_Intersection(b.the_geom, a.the_geom)))
					FROM stimmbezirke a, enklave_n b
				GROUP BY b.gid, b.the_geom, a.the_geom
				HAVING ST_IsValid(b.the_geom) = 't'
				) AS foo2
			GROUP BY foo2.gid)
		) AS foo3
	WHERE c.gid = foo3.gid
;

--Variante 1b: L�sung �ber die L�nge der gemeinsamen Grenze
UPDATE enklave_n c SET stimmbezirk = foo3.stimmbezirk FROM
		(SELECT foo.gid, foo.stimmbezirk FROM
			(SELECT a.stimmbezirk, b.gid,
					(LENGTH(ST_Intersection(ST_Buffer(b.the_geom, 0.0), a.the_geom)))
				FROM stimmbezirke a, enklave_n b
			GROUP BY b.gid, b.the_geom, a.the_geom, a.stimmbezirk
			HAVING ST_Overlaps(a.the_geom, ST_Buffer(b.the_geom, 0.01)) AND ST_IsValid(ST_Buffer(b.the_geom, 0.0)) = 't'
			) AS foo
		WHERE foo.gid = foo.gid AND foo.length in
			(SELECT max(foo2.length) FROM
				(SELECT b.gid,
						(LENGTH(ST_Intersection(ST_Buffer(b.the_geom, 0.0), a.the_geom)))
					FROM stimmbezirke a, enklave_n b
				GROUP BY b.gid, b.the_geom, a.the_geom
				HAVING ST_IsValid(ST_Buffer(b.the_geom, 0.0)) = 't'
				) AS foo2
			GROUP BY foo2.gid)
		) AS foo3
	WHERE c.gid = foo3.gid
;

--Variante 2: L�sung �ber die gr��te �berlappung
UPDATE enklave_n c SET stimmbezirk = foo.stimmbezirk FROM
	(SELECT b.gid, a.stimmbezirk, ST_Intersects(a.the_geom, ST_Buffer(b.the_geom, 0.0000001)),
			ST_Area(ST_Intersection(ST_Buffer(b.the_geom, 0.1), a.the_geom))
		FROM stimmbezirke a, enklave_n b
		GROUP BY b.gid, a.stimmbezirk, b.the_geom, a.the_geom
		HAVING ST_Intersects(a.the_geom, ST_Buffer(b.the_geom, 0.0000001)) = 't'
			AND ST_Area(ST_Intersection(ST_Buffer(b.the_geom, 0.1), a.the_geom))
				in(SELECT max(max_area_pro_gid.ST_Area)--, max_area_pro_gid.gid
					FROM
					(SELECT b.gid, ST_Intersects(a.the_geom, ST_Buffer(b.the_geom, 0.0000001)),
							(ST_Area(ST_Intersection(ST_Buffer(b.the_geom, 0.1), a.the_geom)))
						FROM stimmbezirke a, enklave_n b
						GROUP BY b.gid, b.the_geom, a.the_geom
					HAVING ST_Intersects(a.the_geom, ST_Buffer(b.the_geom, 0.0000001)) = 't') max_area_pro_gid
					GROUP BY max_area_pro_gid.gid)
	) foo
	WHERE c.gid = foo.gid AND c.stimmbezirk IS NULL
;



UPDATE stimmbezirke c SET the_geom = foo.the_geom FROM
	(SELECT a.stimmbezirk,
			--ST_GeometryType
				(ST_Multi(ST_Union(a.the_geom, ST_Union(b.the_geom)))) AS the_geom
		FROM stimmbezirke a, enklave_n b
		WHERE a.stimmbezirk = b.stimmbezirk
		AND ST_IsValid(b.the_geom) = 't'
		GROUP BY a.stimmbezirk, a.the_geom
		HAVING ST_GeometryType(ST_Multi(ST_Union(a.the_geom, ST_Union(b.the_geom)))) = 'ST_MultiPolygon'
	) foo
	WHERE c.stimmbezirk = foo.stimmbezirk
	--AND ST_IsValid(foo.the_geom) = 't'
;




SELECT gid, ST_Summary(ST_Buffer(b.the_geom, 0.0)) FROM enklave_n b GROUP BY gid, b.the_geom;

SELECT distinct b.gid, ST_GeometryType(ST_Multi(ST_Union(a.the_geom, ST_Buffer(b.the_geom, 0.0)))) FROM stimmbezirke a, enklave_n b WHERE gid = 13;





