[postgis-users] Shared Paths query

[Sandro Santilli]

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;