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

Referent:
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

Urheberrechtshinweis beachten!

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

psql -U user postgres

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

CREATE DATABASE agit_2011 template template_postgis;
\c agit_2011
SELECT version();
SELECT postgis_full_version();
\encoding

--alternativ
CREATE DATABASE agit_2011;
SELECT * FROM pg_language;
SELECT version();
SELECT postgis_full_version();
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

SELECT * FROM geometry_columns;

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

\d

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


CREATE OR REPLACE VIEW at_3 AS
        SElECT * FROM europa WHERE nuts_id LIKE 'AT%' AND stat_levl_ = 3
;


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

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

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

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


--differenz identifizieren
SELECT objectid, COUNT(objectid) FROM
        (SELECT objectid, (ST_Dump(the_geom)).* FROM europa
            WHERE nuts_id LIKE 'AT%' 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 'AT%' AND stat_levl_ = 3
        ) AS foo
) - (
    SELECT COUNT(*) FROM europa
        WHERE nuts_id LIKE 'AT%' AND stat_levl_ = 3
)) AS differenz;


--diverse
CREATE SCHEMA ws;
SELECT * FROM pg_namespace;
\dt
\dt ws.*
SELECT * FROM pg_namespace;


--Funktion ST_Union
--SELECT DropGeometryTable('ws', 'st_union');
CREATE TABLE ws.st_union(gid serial PRIMARY KEY, methode varchar(19));
SELECT AddGeometryColumn('ws', 'st_union', 'the_geom', '4258', 'MULTIPOLYGON', 2);
INSERT INTO ws.st_union(methode, the_geom) (
                SELECT 'union'::text, ST_Union(the_geom) AS the_geom FROM
                                    europa
                    WHERE nuts_id LIKE 'AT%' AND stat_levl_ = 3
            );

SELECT COUNT(*) FROM ws.st_union;
SELECT COUNT(*) FROM (
    SELECT (St_Dump(the_geom)).geom FROM ws.st_union
    ) AS foo;


--Funktion ST_Collect
--SELECT DropGeometryTable('ws', 'st_collect');
CREATE TABLE ws.st_collect(gid serial PRIMARY KEY, methode varchar(19));
SELECT AddGeometryColumn('ws', 'st_collect', 'the_geom', '4258', 'GEOMETRY', 2);
INSERT INTO ws.st_collect(methode, the_geom) (
                SELECT 'collect'::text, ST_Collect(the_geom) AS the_geom FROM
                                    at_3 --europa
                    --WHERE nuts_id LIKE 'AT%' AND stat_levl_ = 3
            );

SELECT COUNT(*) FROM ws.st_collect;
SELECT COUNT(*) FROM (
    SELECT (St_Dump(the_geom)).geom FROM ws.st_collect
    ) AS foo;
SELECT ST_GeometryType(the_geom) FROM ws.st_collect;
SELECT ST_GeometryType(ST_Collect(the_geom)) FROM (
    SELECT (St_Dump(the_geom)).geom AS the_geom FROM ws.st_collect
    ) AS foo;

!!!!!!!DELETE FROM ws.st_collect;
INSERT INTO ws.st_collect(methode, the_geom) (
    SELECT 'collect'::text, ST_Collect(the_geom) FROM (
        SELECT (St_Dump(the_geom)).geom AS the_geom FROM ws.st_collect
        ) AS foo
    );


--Funktion ST_DumpRings
SELECT objectid, (ST_DumpRings(the_geom)).* FROM at_3 --europa
            --WHERE nuts_id LIKE 'AT%' AND stat_levl_ = 3
            ;
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 at_3 --europa
            --WHERE nuts_id LIKE 'AT%' 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 at_3 --europa
                    --WHERE nuts_id LIKE 'AT%' AND stat_levl_ = 3
                ) AS multipolygon
            ) AS polygon 
        ) AS foo WHERE foo.path[1] >= 0;

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

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


--Geometrie für die Löcher erstellen
CREATE TABLE ws.boundary(gid serial PRIMARY KEY, methode varchar(19));
SELECT AddGeometryColumn('ws', 'boundary', 'the_geom', '4258', 'LINESTRING', 2);
INSERT INTO ws.boundary(methode, the_geom) (
    SELECT 'boundary'::text, 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
                                    at_3 --europa
                    --WHERE nuts_id LIKE 'AT%' 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 'AT%' AND a.stat_levl_ = 3
                AND b.nuts_id LIKE 'AT%' 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 PRIMARY KEY, methode varchar(19));
SELECT AddGeometryColumn('ws', 'intersection', 'the_geom', '4258', 'GEOMETRY', 2);
INSERT INTO ws.intersection(methode, the_geom) (
    SELECT 'intersection'::text, (ST_Dump(ST_Intersection(a.the_geom, b.the_geom))).geom FROM
            europa a, europa b
            WHERE a.nuts_id LIKE 'AT%' AND a.stat_levl_ = 3
                AND b.nuts_id LIKE 'AT%' 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;