[postgis-users] Shared Paths query

[Andreas Neumann]

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?

Thank you for your ideas.

Andreas