******************************************
******************************************
Workshop
	Geoprozessieren mit PostgreSQL/PostGIS
FOSSGIS 2011

Refernten:
Stephan Holl
Harald Schwenk

******************************************
******************************************

Datenquelle:
http://epp.eurostat.ec.europa.eu/portal/page/portal/gisco/popups/references/administrative_units_statistical_units_1
bzw.
http://epp.eurostat.ec.europa.eu/cache/GISCO/geodatafiles/NUTS_03M_2006_SH.zip

*****************************************
Datenbankmonitor starten
*****************************************

psql -U user postgres

*****************************************
DB anlegen und Daten laden
*****************************************

CREATE DATABASE fossgis_2011 encoding 'UTF8' template template_postgis;

SELECT postgis_full_version();
\encoding
--SET client_encoding TO 'LATIN1';

--alternativ
CREATE DATABASE fossgis_2010 encoding 'UTF8';
SELECT * FROM pg_language;

CREATE LANGUAGE plpgsql;

\i /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
\i /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql
\i /usr/share/postgresql/8.4/contrib/postgis_comments.sql


\i /home/user/Desktop/europa.sql

\d

SELECT * FROM geometry_columns;
SELECT DISTINCT ST_SRID(the_geom) FROM europa;
SELECT DISTINCT ST_GeometryType(the_geom) FROM europa;

*************************************
Tests zur Datenqualität
Enklaven/Exklaven, Löcher
*************************************
--Relationen zählen
SELECT COUNT(*) FROM europa
		WHERE nuts_id LIKE 'DEA%' AND stat_levl_ = 3;	--wahlweise NRW(nuts_id LIKE 'DEA%') oder Bayern (nuts_id LIKE 'DE2%')

--Funktion St_Dump
SELECT objectid, (ST_Dump(the_geom)).* FROM europa
			WHERE nuts_id LIKE 'DEA%' AND stat_levl_ = 3
			OFFSET 17
			LIMIT 1;
SELECT objectid, (ST_Dump(the_geom)).geom FROM europa
			WHERE nuts_id LIKE 'DEA%' AND stat_levl_ = 3
			OFFSET 17
			LIMIT 1;
SELECT objectid, (ST_Dump(the_geom)).path AS path FROM europa
			WHERE nuts_id LIKE 'DEA%' AND stat_levl_ = 3
			ORDER BY objectid, path;
SELECT objectid, (ST_Dump(the_geom)).path[1] FROM europa
			WHERE nuts_id LIKE 'DEA%' AND stat_levl_ = 3
			ORDER BY objectid, path;


--Geometien zählen
--SELECT COUNT(*) FROM (SELECT objectid, (ST_Dump(the_geom)).geom AS the_geom FROM europa WHERE nuts_id LIKE 'DEA%' AND stat_levl_ = 3) AS foo;
SELECT COUNT(*) FROM
		(SELECT objectid, (ST_Dump(the_geom)).geom AS the_geom FROM europa
			WHERE nuts_id LIKE 'DEA%' AND stat_levl_ = 3
		) AS foo;

SELECT ST_NumGeometries(the_geom) FROM europa WHERE nuts_id LIKE 'DEA%' AND stat_levl_ = 3;

--differenz identifizieren
SELECT objectid, COUNT(objectid) FROM
		(SELECT objectid, (ST_Dump(the_geom)).* FROM europa
			WHERE nuts_id LIKE 'DEA%' AND stat_levl_ = 3
		) AS foo
	GROUP BY objectid
	HAVING COUNT(objectid) > 1;

--differenz zählen
SELECT(
	(SELECT COUNT(*) FROM
		(SELECT objectid, (ST_Dump(the_geom)).geom AS the_geom FROM europa
			WHERE nuts_id LIKE 'DEA%' AND stat_levl_ = 3
		) AS foo
) - (
	SELECT COUNT(*) FROM europa
		WHERE nuts_id LIKE 'DEA%' AND stat_levl_ = 3
)) AS differenz;


--wie zuvor für Daten Bayern
SELECT COUNT(*) FROM europa
		WHERE nuts_id LIKE 'DE2%' AND stat_levl_ = 3;
SELECT COUNT(*) FROM
		(SELECT objectid, (ST_Dump(the_geom)).geom AS the_geom FROM europa
			WHERE nuts_id LIKE 'DE2%' AND stat_levl_ = 3
		) AS foo;
SELECT objectid, COUNT(objectid) FROM
		(SELECT objectid, (ST_Dump(the_geom)).* FROM europa
			WHERE nuts_id LIKE 'DE2%' AND stat_levl_ = 3
		) AS foo
	GROUP BY objectid
	HAVING COUNT(objectid) > 1;
SELECT objectid, ST_NumGeometries(the_geom) FROM europa WHERE nuts_id LIKE 'DE2%' AND stat_levl_ = 3;




--Funktion ST_DumpRings
SELECT objectid, (ST_DumpRings(the_geom)).* FROM europa
			WHERE nuts_id LIKE 'DE2%' AND stat_levl_ = 3 --AND ST_GeometryType(the_geom) NOT LIKE '%Multi%'
			;
SELECT DISTINCT ST_GeometryType(the_geom) FROM europa;
SELECT DISTINCT ST_GeometryType((ST_Dump(the_geom)).geom) FROM europa;
SELECT objectid, (ST_DumpRings((ST_Dump(the_geom)).geom)).* FROM europa
			WHERE nuts_id LIKE 'DE2%' AND stat_levl_ = 3
			OFFSET 6
			LIMIT 1;


--Löcher zählen
SELECT COUNT(*) FROM
		(SELECT (ST_DumpRings(polygon.the_geom)).path FROM
			(SELECT (ST_Dump(the_geom)).geom AS the_geom FROM	--ST_GeometryType()
				(SELECT ST_Union(the_geom) AS the_geom FROM europa
					WHERE nuts_id LIKE 'DE2%' AND stat_levl_ = 3
				) AS multipolygon
			) AS polygon 
		) AS foo WHERE foo.path[1] > 0;

SELECT objectid, ST_NumInteriorRings(the_geom) FROM europa WHERE nuts_id LIKE 'DE2%' AND stat_levl_ = 3;

SELECT ST_NumInteriorRings(the_geom) FROM
		(SELECT (ST_DumpRings(polygon.the_geom)).* AS the_geom FROM
			(SELECT (ST_Dump(the_geom)).geom AS the_geom FROM	--ST_GeometryType()
				(SELECT ST_Union(the_geom) AS the_geom FROM europa
					WHERE nuts_id LIKE 'DE2%' AND stat_levl_ = 3
				) AS multipolygon
			) AS polygon 
		) AS foo WHERE foo.path[1] >= 0;



--Löcher identifizieren
SELECT ST_GeometryType(ST_Boundary(geom)) FROM
		(SELECT (ST_DumpRings(polygon.the_geom)).* FROM
			(SELECT (ST_Dump(the_geom)).geom AS the_geom FROM
				(SELECT ST_Union(the_geom) AS the_geom FROM europa
					WHERE nuts_id LIKE 'DE2%' AND stat_levl_ = 3
				) AS multipolygon
			) AS polygon 
		) AS foo WHERE foo.path[1] > 0;


--diverse
CREATE SCHEMA ws;
\d
SELECT * FROM pg_namespace;



--CREATE TABLE ws.union(gid serial);
--SELECT AddGeometryColumn('ws', 'union', 'the_geom', '4258', 'MULTIPOLYGON', 2);

--Geometrie für die Löcher erstellen
CREATE TABLE ws.boundary(gid serial);
SELECT AddGeometryColumn('ws', 'boundary', 'the_geom', '4258', 'LINESTRING', 2);
INSERT INTO ws.boundary(the_geom) (
	SELECT ST_Boundary(geom) FROM
		(SELECT (ST_DumpRings(polygon.the_geom)).* FROM
			(SELECT (ST_Dump(the_geom)).geom AS the_geom FROM
				--(SELECT ST_Union(the_geom) AS the_geom FROM
									europa
					WHERE nuts_id LIKE 'DE2%' AND stat_levl_ = 3
				--) AS multipolygon
			) AS polygon 
		) AS foo WHERE foo.path[1] > 0
	);

SELECT DropGeometryTable('ws', 'boundary');
DELETE FROM ws.boundary;

*************************************
Tests zur Datenqualität
Überlappung
*************************************

SELECT a.objectid, ST_Overlaps(a.the_geom, b.the_geom), b.objectid FROM
			europa a, europa b
			WHERE a.nuts_id LIKE 'DE2%' AND a.stat_levl_ = 3
				AND b.nuts_id LIKE 'DE2%' AND b.stat_levl_ = 3
				AND ST_Overlaps(a.the_geom, b.the_geom) IS TRUE
				AND a.objectid != b.objectid
		LIMIT 10;

CREATE TABLE ws.intersection(gid serial);
SELECT AddGeometryColumn('ws', 'intersection', 'the_geom', '4258', 'GEOMETRY', 2);
INSERT INTO ws.intersection(the_geom) (
	SELECT (ST_Dump(ST_Intersection(a.the_geom, b.the_geom))).geom FROM
			europa a, europa b
			WHERE a.nuts_id LIKE 'DE2%' AND a.stat_levl_ = 3
				AND b.nuts_id LIKE 'DE2%' AND b.stat_levl_ = 3
				AND ST_Overlaps(a.the_geom, b.the_geom) IS TRUE
				AND a.objectid != b.objectid
	);

SELECT DropGeometryTable('ws', 'intersection');
DELETE FROM ws.intersection;

*************************************
Import/Export
*************************************


DROP TABLE europa_txt;
CREATE TABLE europa_txt(
	objectid serial PRIMARY KEY,
	nuts_id varchar(5),
	cntr_code varchar(2),
	nuts_name varchar(70)
	);

DELETE FROM europa_txt;
COPY europa_txt FROM '/home/user/Desktop/europa_txt.txt' DELIMITERS '|';

COPY (SELECT objectid, nuts_id, cntr_code, nuts_name FROM europa_txt)
	TO
	'/tmp/europa_txt2.txt' DELIMITER '|' CSV HEADER;

