[postgis-users] Shared Paths query
Sandro Santilli
strk at keybit.net
Mon Jul 22 09:59:45 PDT 2013
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;
More information about the postgis-users
mailing list