[postgis-users] ArcGIS UNION for PostGIS - Reloaded

[Birgit Laggner]

Hi Yesid,

perhaps it would make sense to split the query after the creation of the 
pointonsurface points and to put a CREATE INDEX on the pointonsurface 
points and the wkb_geometries in between. I could imagine the left joins 
would be much faster then.

Good luck,

Birgit.


Am 22.10.2012 18:09, schrieb 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/
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20121023/fb270b41/attachment.html>