<html><head></head><body>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.<br>
<br>
Jim<br><br><div class="gmail_quote">On September 2, 2015 2:32:40 PM EDT, Travis Kirstine <traviskirstine@gmail.com> wrote:<blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
<div dir="ltr"><div>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.<br /><br /></div><div>The approach works OK when dealing with a few records but fails miserably when run against larger tables <br /><br /></div><div>Any suggestions<br /></div><div> <br /></div><div><br /> CREATE TABLE results (<br /> id SERIAL,<br /> pin VARCHAR(9),<br /> zone VARCHAR,<br /> base_name VARCHAR(9)<br /> );<br /> SELECT AddGeometryColumn('results', 'geom', 4326, 'GEOMETRY',
2 );<br /><br /><br /> CREATE OR REPLACE FUNCTION genTiles() RETURNS int4 AS '<br /> DECLARE r RECORD;<br /> BEGIN<br /><br /> FOR r IN SELECT * FROM grid_table <br /> LOOP<br /> INSERT INTO results (zone, base_name, pin, geom)<br /> SELECT <br /> r.zone, r.base_name,<br /> p.pin, <br /> ST_Intersection((ST_Dump(ST_Boundary(p.geom))).geom, r.geom) AS geom<br /> FROM <br /> parcel p<br /> WHERE <br /> ST_Intersects(p.geom, r.geom);<br /><br /><br /> INSERT INTO results (pin, zone, base_name, geom)<br /> SELECT <br /> r.zone, r.base_name,<br /> p.pin, <br />
ST_Intersection((ST_Dump(ST_PointOnSurface(p.geom))).geom, r.geom) AS geom<br /> FROM <br /> parcels p<br /> WHERE <br /> ST_Intersects(ST_PointOnSurface(p.geom), r.geom);<br /><br /> END LOOP;<br /> return 1; <br /> END;<br /> ' LANGUAGE plpgsql;<br /><br /> SELECT genTiles() as output<br /></div></div>
<p style="margin-top: 2.5em; margin-bottom: 1em; border-bottom: 1px solid #000"></p><pre class="k9mail"><hr /><br />postgis-users mailing list<br />postgis-users@lists.osgeo.org<br /><a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a></pre></blockquote></div><br>
-- <br>
Sent from my Android device with K-9 Mail. Please excuse my brevity.</body></html>