[postgis-users] perform intersection on large tables

[James Keener]

Is there a reason you don't simply join and insist on looping? Index the geometry fields and joining on st_contains or something should have decent performance.

Jim

On September 2, 2015 2:32:40 PM EDT, Travis Kirstine <traviskirstine at gmail.com> wrote:
>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
>
>
>------------------------------------------------------------------------
>
>_______________________________________________
>postgis-users mailing list
>postgis-users at lists.osgeo.org
>http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150902/e4605a0f/attachment.html>