# Intersection of multiple layers in PostGIS

I need to write PL/pgSQL stored procedure for calculation of intersection of multiple layers. For example, for three layers A, B, C this function should return ABC geometry:

Function takes as input the id of layers which need to be intersected. Can anyone give me advice for building this function? My thoughts about this I need to intersect first layer and second then result of this intersection intersect with third layer etc.

• Is this question the same as what you are looking for: gis.stackexchange.com/q/83/97 Jul 3, 2011 at 22:51
• No, this is different question. Jul 4, 2011 at 2:59

Please give me any comments on my proposed solution:

``````CREATE OR REPLACE FUNCTION fp_intersect(lids varchar)
RETURNS integer AS
\$\$
DECLARE
lid_new  integer;
lndx     integer := 1;
lids_arr integer[];
BEGIN

IF regexp_replace(lids, E'\\s+', '', 'g') ~ E'^-?\\d+\$' THEN
RETURN -1;
END IF;

SELECT nextval ('g_layer_lid_seq') INTO lid_new;

lids_arr := string_to_array(regexp_replace(lids, E'\\s+', '', 'g'), ',');
DROP TABLE IF EXISTS tmp_intersect;
CREATE TEMPORARY TABLE tmp_intersect AS SELECT geom FROM g_lgeom WHERE lid = lids_arr[1];

WHILE lndx < array_length(lids_arr, 1) LOOP
DROP TABLE IF EXISTS tmp;
CREATE TEMPORARY TABLE tmp AS SELECT ST_Intersection(geom, g_next) AS geom
FROM tmp_intersect
JOIN (
SELECT geom AS g_next
FROM g_lgeom
WHERE lid = lids_arr[lndx+1]
) AS _
ON ST_Intersects(geom, g_next);
lndx := lndx+1;
DROP TABLE IF EXISTS tmp_intersect;
CREATE TEMPORARY TABLE tmp_intersect AS SELECT geom FROM tmp;
END LOOP;

INSERT INTO g_lgeom(lid, geom) SELECT lid_new, (_.p_geom).geom FROM (SELECT ST_Dump(geom) AS p_geom FROM tmp_intersect) AS _;

RETURN lid_new;
END
\$\$
LANGUAGE plpgsql;
``````