<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Hi Yesid,<br>
<br>
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.<br>
<br>
Good luck,<br>
<br>
Birgit.<br>
<br>
<br>
Am 22.10.2012 18:09, schrieb Yesid Carrillo Vega:
<blockquote
cite="mid:CAOEt3RQKxaJR-4PhL63DfEJh4Uw5FPXmwodBkzHxAE3fLYr2RQ@mail.gmail.com"
type="cite">
<div>This question has a long history. Theoretically has good
solutions that worked for me:</div>
<div><a moz-do-not-send="true"
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>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
</body>
</html>