Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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: enter image description here

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.

share|improve this question
Is this question the same as what you are looking for: – Nathan W Jul 3 '11 at 22:51
No, this is different question. – drnextgis Jul 4 '11 at 2:59
up vote 2 down vote accepted

Please give me any comments on my proposed solution:

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

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

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
    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;

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;
LANGUAGE plpgsql;
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.