[postgis-users] Help me.

[Birgit Laggner]

Hallo Atul,

perhaps, I would split the query in two queries, because otherwise I 
think it might be difficult to use a spatial index on the second 
intersection. The queries could be like this:

--create a spatial index on each input table:
create index temp_outputtree_2_gist_geometry on temp_outputtree_2 using 
gist(geometry);
create index temp_outputtree2_4_gist_geometry on temp_outputtree2_4 
using gist(geometry);
create index temp_outputtree_5_gist_geometry on temp_outputtree_5 using 
gist(geometry);

--intersection of the first 2 tables using the spatial index, writing 
the result of it into a new table:
select st_intersection(t1.geometry,t2.geometry) as geometry into 
new_table from TEMP_OUTPUTTREE_2 t1 inner join TEMP_OUTPUTTREE2_4 t2 on 
t1.geometry && t2.geometry where st_intersects(t1.geometry,t2.geometry);

--create a spatial index on the new geometries of the first intersection:
create index new_table_gist_geometry on new_table using gist(geometry);

--intersection with the third table:
select st_intersection(nt.geometry,t3.geometry) as geometry from 
new_table nt inner join TEMP_OUTPUTTREE_5 t3 on nt.geometry && 
t3.geometry where st_intersects(nt.geometry,t3.geometry);

Hope that helps,

Birgit.

Am 23.11.2011 13:16, schrieb Atul Kumar:
>
> Hi All,
>
> I am trying  to intersection multiple sets of  geographical data using 
> ST_intersection function. But query execution time is long.
>
> My Query is :
>
> select st_intersection (st_intersection (t1.geometry, t2.geometry), 
> t3.geometry) geometry from TEMP_OUTPUTTREE_2 t1,TEMP_OUTPUTTREE2_4 t2, 
> TEMP_OUTPUTTREE_5 t3
>
> I am having three table its having geometry data. I want to intersect 
> operation on those data.
>
> Please suggest, Is there any optimal way to get the intersection with 
> less execution time?
>
> Thanks
>
> Atul Kumar
>
> DISCLAIMER ========== This e-mail may contain privileged and 
> confidential information which is the property of Persistent Systems 
> Ltd. It is intended only for the use of the individual or entity to 
> which it is addressed. If you are not the intended recipient, you are 
> not authorized to read, retain, copy, print, distribute or use this 
> message. If you have received this communication in error, please 
> notify the sender and delete all copies of this message. Persistent 
> Systems Ltd. does not accept any liability for virus infected mails.
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20111123/d7c86b99/attachment.html>