[postgis-users] ArcGIS UNION for PostGIS - Reloaded

[Yesid Carrillo Vega]

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*
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20121022/fab18137/attachment.html>