On Mon, Jul 22, 2013 at 05:10:22PM +0200, Andreas Neumann wrote: > Hi, > > I have a table with polygons and a table with shared boundaries. My goal > is to detect the left and right polygons and attach joined left and > righty polygon type to the LINESTRINGS of the shared boundaries. I want > to later label these linestrings. > > Here is my query: > > SELECT lin.gid, lin.the_geom, lin._tid, > array_to_string(array_textsort(array_distinct(array_agg(zonart.wert::text))),',') > AS zonen > FROM raumplanung.grundwasserschutz__grundwassers_zonen_geometrie lin > LEFT JOIN raumplanung.grundwasserschutz__grundwassers_zonen__areas poly > ON ST_Touches(lin.the_geom,poly.the_geom) AND > ST_LENGTH(ST_SharedPaths(lin.the_geom,ST_ExteriorRing(poly.the_geom))) > 0 > LEFT JOIN raumplanung._zonenart_gwszone zonart ON poly.art = zonart.code > GROUP BY lin.gid, lin.the_geom, lin._tid; > > This query seems to work fine. The results are plausible. The only > problem is the time it takes. For rather small datasets it already runs > more than a minute. > > Do you have any idea how I could speed up my query?
Did you try avoiding the ST_Touches and replacing with a simple && operator ? ST_SharedPaths should already return an empty set if they don't touch. --strk; _______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
