[postgis-users] Clip a table using a donut geometry

[Ralf Suhr]

Hi Júlio,

your query is fast if it use a geometry index. With the explain command you 
can look for slow/ expensive subquerys or the absence of a needed index.

EXPLAIN SELECT r.*, ST_Intersection(r.the_geom, m.the_geom) AS int_geom 
FROM big_table AS r
  INNER JOIN
  (
    SELECT ST_Difference(the_geom, st_buffer(the_geom, buffer_radius)) as 
the_geom
    from designprocess.dp_zn_boundaries r
    where r.type = site_name
  ) AS m ON (ST_Intersects(r.the_geom, m.the_geom))
;

Gr
Ralf

Am Montag 09 Mai 2011, 13:32:40 schrieb Júlio Almeida:
> Hello,
> 
> I'm trying do clip some geometry tables (very big) using a donut geometry.
> This geometry is the difference between a site area and a buffer area
> arround de same site.
> I wrote:
> SELECT r.*, ST_Intersection(r.the_geom, m.the_geom) AS int_geom FROM
> big_table AS r,
>      (SELECT ST_Difference(b.the_geom,a.the_geom) as the_geom from (select
> st_buffer(a.the_geom,buffer_radius) as the_geom from
>      (select the_geom from designprocess.dp_zn_boundaries AS r where r.type
> = site_name) as a) as b,
>      (select the_geom from designprocess.dp_zn_boundaries AS r where r.type
> = site_name) as a) AS m
>      WHERE ST_Intersects(r.the_geom, m.the_geom);
> 
> but it's to slow.
> In what order should I do the operation? Intersection, difference,
> buffer... Can any one help me.
> Thanks,
> 
> júlio

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110509/0e925bf3/attachment.html>