****************************************** ****************************************** Workshop Geoprozessieren mit PostgreSQL/PostGIS AGIT 2012 Referent: Harald Schwenk harald.schwenk@agentur-geoinfo.de ****************************************** ****************************************** Datenquelle: http://epp.eurostat.ec.europa.eu/portal/page/portal/gisco_Geographical_information_maps/geodata/reference Urheberrechtshinweis beachten! ***************************************** Datenbankmonitor starten ***************************************** psql -U user postgres ***************************************** DB anlegen und Daten laden ***************************************** CREATE DATABASE agit_2012 template template_postgis; \c agit_2012 SELECT version(); SELECT postgis_full_version(); \encoding --alternativ CREATE DATABASE agit_2012x; 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 \i /home/user/Desktop/at_3.sql \d SELECT DISTINCT ST_SRID(the_geom) FROM at_3; --europa; SELECT DISTINCT ST_GeometryType(the_geom) FROM at_3; --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 at_3 --europa --WHERE nuts_id LIKE 'AT%' AND stat_levl_ = 3 ORDER BY objectid, path; SELECT objectid, ST_NumGeometries(the_geom) FROM at_3 --europa --WHERE nuts_id LIKE 'AT%' AND stat_levl_ = 3 ; --differenz identifizieren SELECT objectid, COUNT(objectid) FROM (SELECT objectid, (ST_Dump(the_geom)).* FROM at_3 --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 at_3 --europa --WHERE nuts_id LIKE 'AT%' AND stat_levl_ = 3 ) AS foo ) - ( SELECT COUNT(*) FROM at_3 --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', 'table_union'); CREATE TABLE ws.table_union(gid serial PRIMARY KEY, methode varchar(19)); SELECT AddGeometryColumn('ws', 'table_union', 'the_geom', '4258', 'MULTIPOLYGON', 2); INSERT INTO ws.table_union(methode, the_geom) ( SELECT 'union'::text, ST_Union(the_geom) AS the_geom FROM at_3 --europa --WHERE nuts_id LIKE 'AT%' AND stat_levl_ = 3 ); SELECT COUNT(*) FROM ws.table_union; SELECT COUNT(*) FROM ( SELECT (St_Dump(the_geom)).geom FROM ws.table_union ) AS foo; --Funktion ST_Collect --SELECT DropGeometryTable('ws', 'table_collect'); CREATE TABLE ws.table_collect(gid serial PRIMARY KEY, methode varchar(19)); SELECT AddGeometryColumn('ws', 'table_collect', 'the_geom', '4258', 'GEOMETRY', 2); INSERT INTO ws.table_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.table_collect; SELECT COUNT(*) FROM ( SELECT (St_Dump(the_geom)).geom FROM ws.table_collect ) AS foo; SELECT ST_GeometryType(the_geom) FROM ws.table_collect; SELECT ST_GeometryType(ST_Collect(the_geom)) FROM ( SELECT (St_Dump(the_geom)).geom AS the_geom FROM ws.table_collect ) AS foo; !!!!!!!DELETE FROM ws.table_collect; INSERT INTO ws.table_collect(methode, the_geom) ( SELECT 'collect'::text, ST_Collect(the_geom) FROM ( SELECT (St_Dump(the_geom)).geom AS the_geom FROM ws.table_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 at_3; --europa; SELECT DISTINCT ST_GeometryType((ST_Dump(the_geom)).geom) FROM at_3; --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.table_boundary(gid serial PRIMARY KEY, methode varchar(19)); SELECT AddGeometryColumn('ws', 'table_boundary', 'the_geom', '4258', 'LINESTRING', 2); INSERT INTO ws.table_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', 'table_boundary'); DELETE FROM ws.table_boundary; CREATE TABLE ws.table_difference(gid serial PRIMARY KEY, methode varchar(19)); SELECT AddGeometryColumn('ws', 'table_difference', 'the_geom', '4258', 'GEOMETRY', 2); INSERT INTO ws.table_difference(methode, the_geom) ( SELECT 'difference'::text, ST_Difference(a.geom, b.geom) FROM (SELECT (ST_DumpRings(polygon.geom)).* FROM (SELECT (ST_Dump(the_geom)).* FROM (SELECT ST_Union(the_geom) AS the_geom FROM at_3 ) AS multipolygon ) AS polygon ) AS a, (SELECT (ST_DumpRings(polygon.geom)).* FROM (SELECT (ST_Dump(the_geom)).* FROM (SELECT ST_Union(the_geom) AS the_geom FROM at_3 ) AS multipolygon ) AS polygon ) AS b WHERE a.path[1] = 0 AND b.path[1] > 0 ) ; SELECT DropGeometryTable('ws', 'table_difference'); DELETE FROM ws.table_difference; CREATE TABLE ws.innerer_ring(gid serial PRIMARY KEY, methode varchar(19)); SELECT AddGeometryColumn('ws', 'innerer_ring', 'the_geom', '4258', 'POLYGON', 2); INSERT INTO ws.innerer_ring(methode, the_geom) ( SELECT 'innerer_ring'::text, a.geom FROM ( SELECT (ST_DumpRings(polygon.geom)).* FROM (SELECT (ST_Dump(the_geom)).* FROM (SELECT ST_Union(the_geom) AS the_geom FROM at_3 ) AS multipolygon ) AS polygon ) AS a WHERE a.path[1] > 0 ) ; ************************************* Tests zur Datenqualität Überlappung ************************************* SELECT a.objectid, ST_Overlaps(a.the_geom, b.the_geom), b.objectid FROM at_3 a, at_3 b --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.table_intersection(gid serial PRIMARY KEY, methode varchar(19)); SELECT AddGeometryColumn('ws', 'table_intersection', 'the_geom', '4258', 'GEOMETRY', 2); INSERT INTO ws.table_intersection(methode, the_geom) ( SELECT 'intersection'::text, (ST_Dump(ST_Intersection(a.the_geom, b.the_geom))).geom FROM at_3 a, at_3 b --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', 'table_intersection'); DELETE FROM ws.table_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;