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@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
