Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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
1  
Is this question the same as what you are looking for: gis.stackexchange.com/q/83/97 –  Nathan W Jul 3 '11 at 22:51
    
No, this is different question. –  drnextgis Jul 4 '11 at 2:59

1 Answer 1

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

Your Answer

 
discard

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.