[postgis-users] perform intersection on large tables

[Travis Kirstine]

I'm trying to perform an intersection using two tables, one table contains
a regular grid of polygon geometries the other table contains parcels
polygons.  I need to perform an intersection to extract the parcels as
lines with a label point for each polygon in the grid table.  My novice
approach was to create new table with a generic geometry type and then loop
through each row in the grid table to run the intersection against the
parcels and insert the results into the table.

The approach works OK when dealing with a few records but fails miserably
when run against larger tables

Any suggestions


   CREATE TABLE results (
        id SERIAL,
        pin VARCHAR(9),
        zone VARCHAR,
        base_name VARCHAR(9)
    );
    SELECT AddGeometryColumn('results', 'geom', 4326, 'GEOMETRY', 2 );


    CREATE OR REPLACE FUNCTION genTiles() RETURNS int4 AS '
    DECLARE r RECORD;
    BEGIN

    FOR r IN SELECT * FROM grid_table
        LOOP
            INSERT INTO results (zone, base_name, pin, geom)
            SELECT
                r.zone, r.base_name,
                p.pin,
                ST_Intersection((ST_Dump(ST_Boundary(p.geom))).geom,
r.geom) AS geom
            FROM
                parcel p
            WHERE
                ST_Intersects(p.geom, r.geom);


           INSERT INTO results (pin, zone, base_name, geom)
            SELECT
                r.zone, r.base_name,
                p.pin,
                ST_Intersection((ST_Dump(ST_PointOnSurface(p.geom))).geom,
r.geom) AS geom
            FROM
                parcels p
            WHERE
                ST_Intersects(ST_PointOnSurface(p.geom), r.geom);

        END LOOP;
        return 1;
    END;
    ' LANGUAGE plpgsql;

    SELECT genTiles() as output
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150902/0c0c378f/attachment.html>