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.

This function is a test version. Any comments or ideas for improvement are welcome!

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
   

Final result of pgunion consists of 3 tables:

• merged result table of the spatial overlay
  
• 2 tables with input geometries causing Topology Exceptions during _pgunion_difference_a and _pgunion_difference_b

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).

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;