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