<div>This question has a long history. Theoretically has good solutions that worked for me:</div><div><a href="http://trac.osgeo.org/postgis/wiki/UsersWikiExamplesOverlayTables">http://trac.osgeo.org/postgis/wiki/UsersWikiExamplesOverlayTables</a></div>
<div>A good hint for this solution is that input geometries MUST BE SINGLE not MULTI. It cost me a few days guessing that:</div><div><div><br></div><div>SELECT a.ogc_fid AS ogc_fid_a, b.ogc_fid AS ogc_fid_b, new_polys.wkb_geometry as wkb_geometry </div>
<div>FROM </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>(</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>SELECT geom AS wkb_geometry, ST_PointOnSurface(geom) AS pip </div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>FROM ST_Dump</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>(</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>( </div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>SELECT ST_Polygonize(wkb_geometry) AS wkb_geometry </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>FROM </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>(</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>SELECT ST_Union(wkb_geometry) AS wkb_geometry </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>FROM </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>(</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>SELECT ST_ExteriorRing(ST_GeometryN(wkb_geometry,1)) AS wkb_geometry </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>FROM table1 </div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>UNION ALL </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>SELECT ST_ExteriorRing(ST_GeometryN(wkb_geometry,1)) AS wkb_geometry </div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>FROM table2</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>) AS all_lines </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>) AS noded_lines</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>)</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>)</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>) AS new_polys</div>
<div>LEFT JOIN table1 a ON ST_Within(new_polys.pip, a.wkb_geometry) </div><div>LEFT JOIN table2 b ON ST_Within(new_polys.pip, b.wkb_geometry)</div></div><div><br></div><div><br></div><div>However, applying it into a real world case, give me a painful query plan result 158.66..5687848.83: </div>
<div><br></div><div><div>"Nested Loop Left Join (cost=158.66..5687848.83 rows=3685449 width=40)"</div><div>" Join Filter: st_within(st_pointonsurface(st_dump.geom), a.wkb_geometry)"</div><div>" InitPlan 1 (returns $0)"</div>
<div>" -> Aggregate (cost=158.65..158.66 rows=1 width=32)"</div><div>" -> Aggregate (cost=158.63..158.64 rows=1 width=32)"</div><div>" -> Append (cost=0.00..156.50 rows=850 width=32)"</div>
<div>" -> Seq Scan on table1 (cost=0.00..146.09 rows=809 width=32)"</div><div>" -> Seq Scan on table2 (cost=0.00..10.41 rows=41 width=32)"</div><div>
" -> Nested Loop Left Join (cost=0.00..21033.01 rows=13667 width=36)"</div><div>" Join Filter: st_within(st_pointonsurface(st_dump.geom), b.wkb_geometry)"</div><div>" -> Function Scan on st_dump (cost=0.00..10.00 rows=1000 width=32)"</div>
<div>" -> Materialize (cost=0.00..10.62 rows=41 width=165799)"</div><div>" -> Seq Scan on table2 b (cost=0.00..10.41 rows=41 width=165799)"</div><div>" -> Materialize (cost=0.00..150.13 rows=809 width=23296)"</div>
<div>" -> Seq Scan on table1 a (cost=0.00..146.09 rows=809 width=23296)"</div></div><div><br></div><div>My data:</div><div>table1:879 rows</div><div>table2:41 rows</div><div><br></div><div>Please some advice to make this query more time friendly, specially with st_pointonsurface part.</div>
<div><br clear="all">--<br><i>Yesid Carrillo</i><div><div><div><div><font face="arial, sans-serif" size="1"><br></font></div></div></div></div><br>
</div>