Changes between Initial Version and Version 1 of UsersWikiExamplesOverlayTables2

Timestamp:

Mar 10, 2011, 5:26:43 AM (13 years ago)

Author:

blaggner

Comment:

—

UsersWikiExamplesOverlayTables2

@@ +1 @@
+
+== Spatial Overlay of 2 Polygon Tables - pgunion ==
+
+This user example describes a set of functions named pgunion. The functions of pgunion implement a spatial overlay of 2 polygon tables, which means that borders and areas of all geometries will be transferred to the result table forming new geometries. Every output geometry gets the IDs of the origin input geometries so that assignment of the attributes is possible.
+
+[[Image(pgunion_input.png)]] [[Image(pgunion_output.png)]]
+
+pgunion consists of 6 separate functions which have to be called all in a determined order:[[BR]]
+1. _pgunion_start[[BR]]
+2. _pgunion_intersection[[BR]]
+3. _pgunion_no_intersection[[BR]]
+4. _pgunion_difference_a[[BR]]
+5. _pgunion_difference_b[[BR]]
+6. _pgunion_merge[[BR]]
+
+Final result of pgunion consists of 3 tables:[[BR]]
+- merged result table of the spatial overlay[[BR]]
+- 2 tables with input geometries causing Topology Exceptions during _pgunion_difference_a and _pgunion_difference_b
+
+
+Preconditions for the tables to be pgunioned:[[BR]]
+- gid column of data type integer or serial, unique, with btree index!! [[BR]]
+- the_geom column of data type geometry and geometry type POLYGON, with gist index!! [[BR]]
+- column of data type integer (name is variable) for subsequent joining of attributes with btree index!! [[BR]]
+- geometries should be valid (otherwise number of exceptions rises)[[BR]]
+- geometries of each table should not overlap (otherwise result contains duplicate or overlapping geometries with different id-combinations)
+
+
+'''Caution:''' Do not call the functions twice at a time!! - Intermediate tables have fixed names and are locked during function processing (otherwise their content would be overwritten).
+
+'''Example:'''
+
+{{{
+CREATE TABLE pgunion_bsp_a (gid serial PRIMARY KEY, a_id integer, the_geom geometry);
+INSERT INTO pgunion_bsp_a (a_id, the_geom) VALUES
+(1,ST_GeomFromText('POLYGON((3565961 5361731,3570432 5359825,3574390 5362977,3577469 5360631,3582526 5360192,3591616 5351909,3592715 5343113,3591835 5337395,3586265 5339448,3579008 5336882,3572045 5340254,3567500 5338788,3564421 5342013,3562296 5353301,3566327 5356527,3565961 5361731))',31467)),
+(2,ST_GeomFromText('POLYGON((3587846 5359101,3587353 5362736,3592036 5366371,3598813 5365755,3601708 5361997,3592528 5358855,3587846 5359101))',31467)),
+(3,ST_GeomFromText('POLYGON((3600429 5353669,3606823 5355001,3612240 5361751,3622453 5355801,3637461 5355179,3635951 5340171,3617036 5337240,3605225 5340260,3600429 5353669))',31467)),
+(4,ST_GeomFromText('POLYGON((3580359 5328360,3588529 5330935,3597943 5323120,3606024 5322587,3612951 5315217,3608155 5306691,3603804 5305093,3596166 5306159,3591105 5310244,3584799 5310688,3576540 5317437,3580359 5328360))',31467));
+
+CREATE TABLE pgunion_bsp_b (gid serial PRIMARY KEY, b_id integer, the_geom geometry);
+INSERT INTO pgunion_bsp_b (b_id, the_geom) VALUES
+(1,ST_GeomFromText('POLYGON((3568123 5343184,3577776 5350721,3586922 5344624,3584890 5336664,3593104 5330312,3586499 5322014,3572187 5325232,3568123 5343184))',31467)),
+(2,ST_GeomFromText('POLYGON((3594628 5349620,3601741 5358935,3617153 5357580,3621387 5348519,3617153 5339712,3610040 5334293,3616137 5322945,3607923 5311598,3600302 5314392,3596068 5319727,3600725 5329974,3596406 5339882,3594628 5349620))',31467)),
+(3,ST_GeomFromText('POLYGON((3622319 5325401,3625029 5327433,3630194 5329466,3635191 5326502,3638747 5317610,3631719 5311598,3622150 5316594,3622319 5325401))',31467));
+
+SELECT _pgunion_start('public','pgunion_bsp_a','a_id','public','pgunion_bsp_b','b_id','public');
+SELECT _pgunion_intersection('public','pgunion_bsp_a','a_id','public','pgunion_bsp_b','b_id','public','pgunion_bsp_intsec');
+SELECT _pgunion_no_intersection('public','pgunion_bsp_a','a_id','public','pgunion_bsp_b','b_id','public','pgunion_bsp_intsec','pgunion_bsp_nointsec_a','pgunion_bsp_nointsec_b');
+SELECT _pgunion_difference_a('public','pgunion_bsp_a','a_id','public','pgunion_bsp_b','b_id','public','pgunion_bsp_diff_a','pgunion_bsp_diff_a_exceptions');
+SELECT _pgunion_difference_b('public','pgunion_bsp_a','a_id','public','pgunion_bsp_b','b_id','public','pgunion_bsp_diff_b','pgunion_bsp_diff_b_exceptions');
+SELECT _pgunion_merge('a_id','b_id','public','pgunion_bsp_intsec','pgunion_bsp_nointsec_a','pgunion_bsp_nointsec_b','pgunion_bsp_diff_a','pgunion_bsp_diff_b','pgunion_bsp_result');
+}}}
+
+
+'''Functions:'''
+{{{
+/*
+pgunion consists of 6 separate functions which have to be called all in a determined order:
+1. _pgunion_start
+2. _pgunion_intersection
+3. _pgunion_no_intersection
+4. _pgunion_difference_a
+5. _pgunion_difference_b
+6. _pgunion_merge
+
+Preconditions for the tables to be pgunioned:
+- gid column of data type integer or serial, unique, with btree index!!
+- the_geom column of data type geometry and geometry type POLYGON, with gist index!!
+- column of data type integer (name is variable) for subsequent joining of attributes with btree index!!
+- geometries should be valid (otherwise number of exceptions rises)
+- geometries of each table should not overlap (otherwise result contains duplicate or overlapping geometries with different id-combinations)
+
+Caution: Do not call the functions twice at a time!! - Intermediate tables have fixed names and are locked during function processing (otherwise their content would be overwritten).
+*/
+
+
+CREATE OR REPLACE FUNCTION _pgunion_start(
+ schema_a varchar,
+ table_a varchar,
+ a_id varchar,
+ schema_b varchar,
+ table_b varchar,
+ b_id varchar,
+ schema_z varchar)
+RETURNS void AS 
+$BODY$
+
+/*
+$Id: pgunion_wiki.sql 2011-03-10 09:45Z Birgit Laggner $
+
+pgunion - spatial overlay of 2 tables, returns table with new geometries and assignments to original ids, as well as 2 tables with input geometries causing Topology Exceptions during difference function
+_pgunion_start: 1st part of pgunion, establishes spatial relation between intersecting geometries of input tables
+
+schema_a:      database schema where 1st table is located
+table_a:       name of 1st table
+a_id:          id column name in 1st table
+schema_b:      database schema where 2nd table is located
+table_b:       name of 2nd table
+b_id:          id column name in 2nd table
+schema_z:      database schema where result table shall be located
+
+Copyright (C) 2011 Johann Heinrich von Thünen-Institute (vTI) - Federal Research Institute for Rural Areas, Forestry and Fisheries, Institute of Rural Studies, Braunschweig, Germany (http://www.vti.bund.de)
+Version 0.1
+contact: birgit dot laggner at vti dot bund dot de
+
+This is free software; you can redistribute and/or modify it under
+the terms of the GNU General Public Licence. This software is without any warrenty and you use it at your own risk.
+*/
+
+BEGIN
+
+EXECUTE
+'DROP TABLE IF EXISTS '||schema_z||'.intersection_inner_join;';
+
+EXECUTE
+'CREATE TABLE '||schema_z||'.intersection_inner_join (gid serial PRIMARY KEY, a_id integer, a_geom geometry, b_id integer, b_geom geometry);';
+EXECUTE
+'INSERT INTO '||schema_z||'.intersection_inner_join (a_id, a_geom, b_id, b_geom)
+SELECT
+a.'||a_id||',
+a.the_geom,
+b.'||b_id||',
+b.the_geom
+FROM
+'||schema_a||'.'||table_a||' a
+INNER JOIN
+'||schema_b||'.'||table_b||' b
+ON a.the_geom && b.the_geom
+WHERE
+ST_Intersects(a.the_geom, b.the_geom);';
+
+EXECUTE
+'CREATE INDEX intersection_inner_join_btree_a_id ON '||schema_z||'.intersection_inner_join USING btree(a_id);';
+EXECUTE
+'CREATE INDEX intersection_inner_join_btree_b_id ON '||schema_z||'.intersection_inner_join USING btree(b_id);';
+EXECUTE
+'CREATE INDEX intersection_inner_join_gist_a ON '||schema_z||'.intersection_inner_join USING gist(a_geom);';
+EXECUTE
+'CREATE INDEX intersection_inner_join_gist_b ON '||schema_z||'.intersection_inner_join USING gist(b_geom);';
+
+ END;
+$BODY$
+LANGUAGE 'plpgsql' VOLATILE;
+ALTER FUNCTION _pgunion_start(varchar, varchar, varchar, varchar, varchar, varchar, varchar) OWNER TO postgres;
+
+
+--Intersection:
+
+CREATE OR REPLACE FUNCTION _pgunion_intersection(
+  schema_a varchar,
+  table_a varchar,
+  a_id varchar,
+  schema_b varchar,
+  table_b varchar,
+  b_id varchar,
+  schema_z varchar,
+  intersection varchar)
+RETURNS void AS
+$BODY$
+
+/*
+$Id: pgunion_wiki.sql 2011-03-10 09:45Z Birgit Laggner $
+
+pgunion - spatial overlay of 2 tables, returns table with new geometries and assignments to original ids, as well as 2 tables with input geometries causing Topology Exceptions during difference function
+_pgunion_intersection: 2nd part of pgunion, creates table with intersection polygons
+
+schema_a:      database schema where 1st table is located
+table_a:       name of 1st table
+a_id:          id column name in 1st table
+schema_b:      database schema where 2nd table is located
+table_b:       name of 2nd table
+b_id:          id column name in 2nd table
+schema_z:      database schema where result table is located
+intersection:  table name for intersection table
+
+Copyright: 2011 Johann Heinrich von Thünen-Institute (vTI) - Federal Research Institute for Rural Areas, Forestry and Fisheries, Institute of Rural Studies, Braunschweig, Germany (http://www.vti.bund.de)
+Version 0.1
+contact: birgit dot laggner at vti dot bund dot de
+
+This is free software; you can redistribute and/or modify it under
+the terms of the GNU General Public Licence. This software is without any warrenty and you use it at your own risk.
+*/
+
+DECLARE
+
+i integer;
+sql_1 text;
+
+BEGIN
+
+EXECUTE
+'DROP TABLE IF EXISTS '||schema_z||'.'||intersection||';';
+
+EXECUTE
+'CREATE TABLE '||schema_z||'.'||intersection||' ('||
+ 'gid serial PRIMARY KEY, '||
+ ''||a_id||' integer, '||
+ ''||b_id||' integer '||
+ ');';
+
+EXECUTE
+'SELECT AddGeometryColumn('''||schema_z||''', '''||intersection||''',''the_geom'',(SELECT DISTINCT ST_SRID(the_geom) FROM '||schema_a||'.'||table_a||'),''POLYGON'',2);';
+
+EXECUTE
+'ALTER TABLE '||schema_z||'.'||intersection||' DROP CONSTRAINT enforce_geotype_the_geom;';
+
+
+RAISE NOTICE 'Starting Intersection. ';
+
+i:=0;
+
+sql_1:='SELECT gid FROM '||schema_z||'.intersection_inner_join ORDER BY gid;';
+
+FOR i IN EXECUTE sql_1 LOOP
+
+BEGIN
+  EXECUTE
+    'INSERT INTO 
+     '||schema_z||'.'||intersection||' 
+      ('||a_id||', '||b_id||', the_geom)
+      SELECT
+      a_id,
+      b_id,
+      (ST_Dump(ST_Intersection(a_geom, b_geom))).geom
+      FROM '||schema_z||'.intersection_inner_join 
+      WHERE gid='||i||'';
+
+EXCEPTION
+WHEN internal_error
+THEN
+  EXECUTE
+    'INSERT INTO 
+     '||schema_z||'.'||intersection||' 
+      ('||a_id||', '||b_id||', the_geom)
+      SELECT
+      a_id,
+      b_id,
+      (ST_Dump
+       (ST_Intersection
+        (ST_SnapToGrid(ST_Buffer(a_geom,0.001),0.001), 
+         ST_SnapToGrid(ST_Buffer(b_geom,0.001),0.001))
+       )).geom
+      FROM '||schema_z||'.intersection_inner_join
+      WHERE gid='||i||'';
+
+END;    
+END LOOP;
+
+EXECUTE
+'DELETE FROM '||schema_z||'.'||intersection||' WHERE
+ ST_GeometryType(the_geom) IN (
+ ''ST_Point'',
+ ''ST_MultiPoint'',
+ ''ST_Linestring'',
+ ''ST_MultiLinestring'',
+ ''ST_Line'') OR
+ ST_IsEmpty(the_geom)=''t'';';
+
+EXECUTE
+'DELETE FROM '||schema_z||'.intersection_inner_join WHERE
+ gid IN (
+  SELECT a.gid
+  FROM
+  '||schema_z||'.intersection_inner_join a
+  LEFT JOIN
+  '||schema_z||'.'||intersection||' b
+  ON a.a_id=b.'||a_id||' AND a.b_id=b.'||b_id||'
+  WHERE b.'||a_id||' IS NULL AND b.'||b_id||' IS NULL);';
+
+
+RAISE NOTICE 'Intersection finished. ';
+
+
+END;
+$BODY$ 
+LANGUAGE 'plpgsql' VOLATILE;
+ALTER FUNCTION _pgunion_intersection(varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar)
+ OWNER TO postgres;
+
+
+--No Intersection
+
+CREATE OR REPLACE FUNCTION _pgunion_no_intersection(
+ schema_a varchar, 
+ table_a varchar, 
+ a_id varchar,
+ schema_b varchar,
+ table_b varchar, 
+ b_id varchar,
+ schema_z varchar,
+ intersection varchar,
+ nointersect_a varchar, 
+ nointersect_b varchar)
+RETURNS void AS 
+$BODY$
+
+/*
+$Id: pgunion_wiki.sql 2011-03-10 09:45Z Birgit Laggner $
+
+pgunion - spatial overlay of 2 tables, returns table with new geometries and assignments to original ids, as well as 2 tables with input geometries causing Topology Exceptions during difference function
+_pgunion_no_intersection: 3rd part of pgunion, selects not intersecting polygons of both input tables 
+
+schema_a:      database schema where 1st table is located
+table_a:       name of 1st table
+a_id:          id column name in 1st table
+schema_b:      database schema where 2nd table is located
+table_b:       name of 2nd table
+b_id:          id column name in 2nd table
+schema_z:      database schema where result table is located
+intersection:  table name for intersection table
+nointersect_a: table name for no intersection of 1st table
+nointersect_b: table name for no intersection of 2nd table
+
+Copyright: 2011 Johann Heinrich von Thünen-Institute (vTI) - Federal Research Institute for Rural Areas, Forestry and Fisheries, Institute of Rural Studies, Braunschweig, Germany (http://www.vti.bund.de)
+Version 0.1
+contact: birgit dot laggner at vti dot bund dot de
+
+This is free software; you can redistribute and/or modify it under
+the terms of the GNU General Public Licence. This software is without any warrenty and you use it at your own risk.
+*/
+
+ BEGIN
+  
+--No Intersection a:
+
+EXECUTE
+'DROP TABLE IF EXISTS '||schema_z||'.'||nointersect_a||';';
+EXECUTE
+'DROP TABLE IF EXISTS '||schema_z||'.'||nointersect_b||';';
+
+EXECUTE
+'CREATE TABLE '||schema_z||'.'||nointersect_a||' ('||
+ 'gid serial PRIMARY KEY, '||
+ ''||a_id||' integer, '||
+ ''||b_id||' integer '||
+ ');';
+
+EXECUTE
+'SELECT AddGeometryColumn('''||schema_z||''', '''||nointersect_a||''',''the_geom'',(SELECT DISTINCT ST_SRID(the_geom) FROM '||schema_a||'.'||table_a||'),''POLYGON'',2);';
+
+EXECUTE
+'ALTER TABLE '||schema_z||'.'||nointersect_a||' DROP CONSTRAINT enforce_geotype_the_geom;';
+
+EXECUTE
+'CREATE TABLE '||schema_z||'.'||nointersect_b||' ('||
+ 'gid serial PRIMARY KEY, '||
+ ''||a_id||' integer, '||
+ ''||b_id||' integer '||
+ ');';
+
+EXECUTE
+'SELECT AddGeometryColumn('''||schema_z||''', '''||nointersect_b||''',''the_geom'',(SELECT DISTINCT ST_SRID(the_geom) FROM '||schema_a||'.'||table_a||'),''POLYGON'',2);';
+ 
+EXECUTE
+'ALTER TABLE '||schema_z||'.'||nointersect_b||' DROP CONSTRAINT enforce_geotype_the_geom;';
+
+
+RAISE NOTICE 'Starting No Intersection for 1st table. ';
+
+EXECUTE
+ 'INSERT INTO 
+ '||schema_z||'.'||nointersect_a||'
+  ('||a_id||', '||b_id||', the_geom)
+  SELECT 
+   a.'||a_id||',
+   NULL AS '||b_id||',            
+   a.the_geom
+  FROM '||schema_a||'.'||table_a||' a LEFT JOIN 
+       '||schema_z||'.'||intersection||' b ON 
+       a.'||a_id||'=b.'||a_id||'
+ WHERE b.'||a_id||' is NULL';
+
+RAISE NOTICE 'finished ';
+
+--No Intersection b:
+
+RAISE NOTICE 'Starting No Intersection for 2nd table. ';
+
+EXECUTE
+ 'INSERT INTO
+  '||schema_z||'.'||nointersect_b||' 
+  ('||a_id||', '||b_id||', the_geom)
+ SELECT 
+  NULL AS '||a_id||',
+  a.'||b_id||',            
+  a.the_geom
+ FROM '||schema_b||'.'||table_b||' a LEFT JOIN 
+      '||schema_z||'.'||intersection||' b ON 
+       a.'||b_id||'=b.'||b_id||'
+ WHERE b.'||b_id||' is NULL';
+
+RAISE NOTICE 'finished ';
+
+ END;
+$BODY$
+LANGUAGE 'plpgsql' VOLATILE;
+ALTER FUNCTION _pgunion_no_intersection(varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar)
+OWNER TO postgres;
+
+--Difference a
+
+CREATE OR REPLACE FUNCTION _pgunion_difference_a(
+ schema_a varchar,
+ table_a varchar,
+ a_id varchar,
+ schema_b varchar,
+ table_b varchar,
+ b_id varchar,
+ schema_z varchar,
+ diff_a varchar,
+ diff_a_exceptions varchar)
+RETURNS void AS 
+$BODY$
+
+/*
+$Id: pgunion_wiki.sql 2011-03-10 09:45Z Birgit Laggner $
+
+pgunion - spatial overlay of 2 tables, returns table with new geometries and assignments to original ids, as well as 2 tables with input geometries causing Topology Exceptions during difference function
+_pgunion_difference_a: 4th part of pgunion, generates difference part of intersecting geometries of 1st input table, inserts geometries causing a topology exception into a separate table for later treatment
+
+schema_a:          database schema where 1st table is located
+table_a:           name of 1st table
+a_id:              id column name in 1st table
+schema_b:          database schema where 2nd table is located
+table_b:           name of 2nd table
+b_id:              id column name in 2nd table
+schema_z:          database schema where result table is located
+diff_a:            table name for difference of 1st table
+diff_a_exceptions: table name for geometries causing a topology exception
+                   during difference
+
+Copyright: 2011 Johann Heinrich von Thünen-Institute (vTI) - Federal Research Institute for Rural Areas, Forestry and Fisheries, Institute of Rural Studies, Braunschweig, Germany (http://www.vti.bund.de)
+Version 0.1
+contact: birgit dot laggner at vti dot bund dot de
+
+This is free software; you can redistribute and/or modify it under
+the terms of the GNU General Public Licence. This software is without any warrenty and you use it at your own risk.
+*/
+
+DECLARE
+i     integer;
+sql_1 text;
+
+BEGIN
+       
+--4. Difference a:
+
+EXECUTE
+'DROP TABLE IF EXISTS '||schema_z||'.'||diff_a||';';
+
+EXECUTE
+'CREATE TABLE '||schema_z||'.'||diff_a||' ('||
+ 'gid serial PRIMARY KEY, '||
+ ''||a_id||' integer, '||
+ ''||b_id||' integer '||
+ ');';
+
+EXECUTE
+'SELECT AddGeometryColumn('''||schema_z||''', '''||diff_a||''',''the_geom'',(SELECT DISTINCT ST_SRID(the_geom) FROM '||schema_a||'.'||table_a||'),''POLYGON'',2);';
+
+EXECUTE
+'ALTER TABLE '||schema_z||'.'||diff_a||' DROP CONSTRAINT enforce_geotype_the_geom;';
+
+
+ EXECUTE
+ 'CREATE TABLE '||schema_z||'.tmp1 (gid serial, a_id integer, a_geom geometry, bu_geom geometry);';
+
+BEGIN
+EXECUTE
+'CREATE TABLE '||schema_z||'.'||diff_a_exceptions||' (gid serial, '||a_id||' integer, a_geom geometry, bu_geom geometry);';
+EXCEPTION WHEN duplicate_table THEN
+EXECUTE
+'DROP TABLE '||schema_z||'.'||diff_a_exceptions||';';
+EXECUTE
+'CREATE TABLE '||schema_z||'.'||diff_a_exceptions||' (gid serial, '||a_id||' integer, a_geom geometry, bu_geom geometry);';
+END;
+
+EXECUTE
+'INSERT INTO '||schema_z||'.tmp1 
+  (a_id, a_geom, bu_geom)
+ SELECT
+  a_id,
+  a_geom,
+  CASE WHEN ST_Union(b_geom) IS NULL
+    THEN ST_Collect(b_geom)
+    ELSE ST_Union(b_geom)
+    END
+  FROM '||schema_z||'.intersection_inner_join
+  GROUP BY a_id, a_geom;';
+
+RAISE NOTICE 'diff_a: Insert in tmp1 finished. Starting Difference. ';
+
+i:=0;
+sql_1:='SELECT a_id FROM '||schema_z||'.intersection_inner_join GROUP BY a_id;';
+FOR i IN EXECUTE sql_1 LOOP
+
+IF i IN (100000, 200000, 300000, 400000, 500000, 600000, 700000, 800000, 900000, 1000000, 1100000, 1200000, 1300000, 1400000, 1500000, 1600000, 1700000, 1800000, 1900000)
+THEN
+RAISE NOTICE 'diff_a: loop % ', i;
+END IF;
+        
+ BEGIN
+
+  EXECUTE
+  'INSERT INTO '||schema_z||'.'||diff_a||'
+   ('||a_id||',
+       the_geom)
+   SELECT 
+    a_id,
+    ST_Difference(a_geom, bu_geom)
+   FROM '||schema_z||'.tmp1
+   WHERE
+   a_id='||i||' AND
+   ST_IsEmpty(ST_Difference(a_geom, bu_geom))=''f'';';
+
+  EXCEPTION
+   WHEN  internal_error
+   THEN --insert geometries causing errors into separate table
+  EXECUTE
+  'INSERT INTO '||schema_z||'.'||diff_a_exceptions||' 
+   ('||a_id||', a_geom, bu_geom)
+  SELECT
+   a_id,
+   a_geom,
+   bu_geom
+   FROM '||schema_z||'.tmp1
+   WHERE a_id='||i||';';
+
+  END;
+
+  END LOOP;
+
+  EXECUTE
+ 'DROP TABLE '||schema_z||'.tmp1;';
+
+RAISE NOTICE 'diff_a finished. ';
+
+ END;
+$BODY$
+LANGUAGE 'plpgsql' VOLATILE;
+ALTER FUNCTION _pgunion_difference_a(varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar)
+OWNER TO postgres;
+
+
+--Difference b:
+
+CREATE OR REPLACE FUNCTION _pgunion_difference_b(
+ schema_a varchar,
+ table_a varchar,
+ a_id varchar,
+ schema_b varchar,
+ table_b varchar,
+ b_id varchar,
+ schema_z varchar,
+ diff_b varchar,
+ diff_b_exceptions varchar)
+RETURNS void AS 
+$BODY$
+
+/*
+$Id: pgunion_wiki.sql 2011-03-10 09:45Z Birgit Laggner $
+
+pgunion - spatial overlay of 2 tables, returns table with new geometries and assignments to original ids, as well as 2 tables with input geometries causing Topology Exceptions during difference function
+_pgunion_difference_b: 5th part of pgunion, generates difference part of intersecting geometries of 2nd input table, inserts geometries causing a topology exception into a separate table for later treatment
+
+schema_a:          database schema where 1st table is located
+table_a:           name of 1st table
+a_id:              id column name in 1st table
+schema_b:          database schema where 2nd table is located
+table_b:           name of 2nd table
+b_id:              id column name in 2nd table
+schema_z:          database schema where result table is located
+diff_b:            table name for difference of 2nd table
+diff_b_exceptions: table name for geometries causing a topology exception
+                   during difference
+
+Copyright: 2011 Johann Heinrich von Thünen-Institute (vTI) - Federal Research Institute for Rural Areas, Forestry and Fisheries, Institute of Rural Studies, Braunschweig, Germany (http://www.vti.bund.de)
+Version 0.1
+contact: birgit dot laggner at vti dot bund dot de
+
+This is free software; you can redistribute and/or modify it under
+the terms of the GNU General Public Licence. This software is without any warrenty and you use it at your own risk.
+*/
+
+DECLARE
+i     integer;
+sql_1 text;
+
+BEGIN
+       
+--5. Difference b:
+
+EXECUTE
+'DROP TABLE IF EXISTS '||schema_z||'.'||diff_b||';';
+
+EXECUTE
+'CREATE TABLE '||schema_z||'.'||diff_b||' ('||
+ 'gid serial PRIMARY KEY, '||
+ ''||a_id||' integer, '||
+ ''||b_id||' integer '||
+ ');';
+
+EXECUTE
+'SELECT AddGeometryColumn('''||schema_z||''', '''||diff_b||''',''the_geom'',(SELECT DISTINCT ST_SRID(the_geom) FROM '||schema_a||'.'||table_a||'),''POLYGON'',2);';
+
+EXECUTE
+'ALTER TABLE '||schema_z||'.'||diff_b||' DROP CONSTRAINT enforce_geotype_the_geom;';
+
+
+EXECUTE
+ 'CREATE TABLE '||schema_z||'.tmp1 (gid serial, b_id integer, b_geom geometry, au_geom geometry);';
+
+BEGIN
+EXECUTE
+'CREATE TABLE '||schema_z||'.'||diff_b_exceptions||' (gid serial, '||b_id||' integer, b_geom geometry, au_geom geometry);';
+EXCEPTION WHEN duplicate_table THEN
+EXECUTE
+'DROP TABLE '||schema_z||'.'||diff_b_exceptions||';';
+EXECUTE
+'CREATE TABLE '||schema_z||'.'||diff_b_exceptions||' (gid serial, '||b_id||' integer, b_geom geometry, au_geom geometry);';
+END;
+
+
+EXECUTE
+ 'INSERT INTO '||schema_z||'.tmp1 
+  (b_id, b_geom, au_geom)
+ SELECT
+  b_id,
+  b_geom,
+  CASE WHEN ST_Union(a_geom) IS NULL
+    THEN ST_Collect(a_geom)
+    ELSE ST_Union(a_geom)
+    END
+ FROM '||schema_z||'.intersection_inner_join
+ GROUP BY b_id, b_geom;';
+
+RAISE NOTICE 'diff_b: Insert in tmp1 finished. Starting Difference. ';
+
+i:=0;
+sql_1:='SELECT b_id FROM '||schema_z||'.tmp1 GROUP BY b_id ORDER BY b_id;';
+FOR i IN EXECUTE sql_1 LOOP
+
+IF i IN (100000, 200000, 300000, 400000, 500000, 600000, 700000, 800000, 900000, 1000000, 1100000, 1200000, 1300000, 1400000, 1500000, 1600000, 1700000, 1800000, 1900000)
+THEN
+RAISE NOTICE 'diff_b: loop % ', i;
+END IF;
+
+BEGIN
+
+  EXECUTE
+  'INSERT INTO '||schema_z||'.'||diff_b||'
+   ('||b_id||',
+       the_geom)
+   SELECT 
+    b_id,
+    ST_Difference(b_geom, au_geom)
+   FROM '||schema_z||'.tmp1
+   WHERE 
+   b_id='||i||' AND
+   ST_IsEmpty(ST_Difference(b_geom, au_geom))=''f'';';
+
+  EXCEPTION
+   WHEN  internal_error
+   THEN --insert geometries causing errors into separate table
+  EXECUTE
+  'INSERT INTO '||schema_z||'.'||diff_b_exceptions||' 
+   ('||b_id||', b_geom, au_geom)
+  SELECT
+   b_id,
+   b_geom,
+   au_geom
+   FROM '||schema_z||'.tmp1
+   WHERE b_id='||i||';';
+
+  END;
+
+  END LOOP;
+ 
+  EXECUTE
+ 'DROP TABLE '||schema_z||'.tmp1;';
+
+RAISE NOTICE 'diff_b finished. ';
+
+ END;
+$BODY$
+LANGUAGE 'plpgsql' VOLATILE;
+ALTER FUNCTION _pgunion_difference_b(varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar)
+OWNER TO postgres;
+
+--Merge:
+
+CREATE OR REPLACE FUNCTION _pgunion_merge(
+ a_id varchar, 
+ b_id varchar, 
+ schema_z varchar, 
+ intersection varchar, 
+ nointersect_a varchar, 
+ nointersect_b varchar, 
+ diff_a varchar, 
+ diff_b varchar, 
+ result varchar)
+ RETURNS void AS 
+$BODY$
+
+/*
+$Id: pgunion_wiki.sql 2011-03-10 09:45Z Birgit Laggner $
+
+pgunion - spatial overlay of 2 tables, returns table with new geometries and assignments to original ids, as well as 2 tables with input geometries causing Topology Exceptions during difference function
+_pgunion_merge: 6th part of pgunion, merges the results of each step into one table
+
+a_id:          id column name in 1st table
+b_id:          id column name in 2nd table
+schema_z:      database schema where result table is located
+intersection:  table name for intersection table
+nointersect_a: table name for no intersection of 1st table
+nointersect_b: table name for no intersection of 2nd table
+diff_a:        table name for difference of 1st table
+diff_b:        table name for difference of 2nd table
+result:        table name for result table
+
+Copyright: 2011 Johann Heinrich von Thünen-Institute (vTI) - Federal Research Institute for Rural Areas, Forestry and Fisheries, Institute of Rural Studies, Braunschweig, Germany (http://www.vti.bund.de)
+Version 0.1
+contact: birgit dot laggner at vti dot bund dot de
+
+This is free software; you can redistribute and/or modify it under
+the terms of the GNU General Public Licence. This software is without any warrenty and you use it at your own risk.
+*/
+
+BEGIN
+
+EXECUTE
+'DROP TABLE IF EXISTS '||schema_z||'.'||result||';';
+
+EXECUTE
+'CREATE TABLE '||schema_z||'.'||result||' (gid serial PRIMARY KEY, '||a_id||' integer, '||b_id||' integer);';
+EXECUTE
+'SELECT AddGeometryColumn('''||schema_z||''', '''||result||''',''the_geom'',(SELECT DISTINCT ST_SRID(the_geom) FROM '||schema_z||'.'||intersection||'),''POLYGON'',2);';
+EXECUTE
+'ALTER TABLE '||schema_z||'.'||result||' DROP CONSTRAINT enforce_geotype_the_geom;';
+
+EXECUTE
+'INSERT INTO '||schema_z||'.'||result||'
+ ('||a_id||','||b_id||', the_geom)
+ SELECT
+   a.'||a_id||',
+   a.'||b_id||',
+   (ST_Dump(a.the_geom)).geom
+  FROM '||schema_z||'.'||intersection||' a;';
+
+EXECUTE
+'INSERT INTO '||schema_z||'.'||result||'
+ ('||a_id||','||b_id||', the_geom)
+ SELECT
+   b.'||a_id||',
+   b.'||b_id||',
+   (ST_Dump(b.the_geom)).geom
+  FROM '||schema_z||'.'||nointersect_a||' b;';
+
+EXECUTE
+'INSERT INTO '||schema_z||'.'||result||'
+ ('||a_id||','||b_id||', the_geom)
+ SELECT
+   c.'||a_id||',
+   c.'||b_id||',
+   (ST_Dump(c.the_geom)).geom
+  FROM '||schema_z||'.'||nointersect_b||' c;';
+
+EXECUTE
+'INSERT INTO '||schema_z||'.'||result||'
+ ('||a_id||','||b_id||', the_geom)
+ SELECT
+   d.'||a_id||',
+   d.'||b_id||',
+   (ST_Dump(d.the_geom)).geom
+  FROM '||schema_z||'.'||diff_a||' d;';
+
+EXECUTE
+'INSERT INTO '||schema_z||'.'||result||'
+ ('||a_id||','||b_id||', the_geom)
+ SELECT
+   e.'||a_id||',
+   e.'||b_id||',
+   (ST_Dump(e.the_geom)).geom
+  FROM '||schema_z||'.'||diff_b||' e;';
+
+--Remove intermediate tables:
+
+EXECUTE
+'SELECT DropGeometryTable('''||schema_z||''','''||intersection||''');';
+EXECUTE
+'SELECT DropGeometryTable('''||schema_z||''','''||nointersect_a||''');';
+EXECUTE
+'SELECT DropGeometryTable('''||schema_z||''','''||nointersect_b||''');';
+EXECUTE
+'SELECT DropGeometryTable('''||schema_z||''','''||diff_a||''');';
+EXECUTE
+'SELECT DropGeometryTable('''||schema_z||''','''||diff_b||''');';
+
+
+ END;
+$BODY$
+LANGUAGE 'plpgsql' VOLATILE;
+ALTER FUNCTION _pgunion_merge(varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar)
+OWNER TO postgres;
+
+}}}