[postgis-users] Help me.

[Birgit Laggner]

Hi Atul,

are you really sure, you want to union all geometries of the three 
tables into one big multipolygon? It seems to me that this might be a 
little bit too complex and big...

But, if you really want to union all these geometries (and I am not 
sure, if this would really be your plan - because maybe you are mistaken 
that union in PostGIS means the same as in ArcGIS??), then I would split 
the query again to first do the union of the first two tables, while 
immediately dumping the resulting multipolygon into the consisting 
single polygons, and next, I would go for the union with the third 
table. The queries could look like this:

select (st_dump(st_union(t1.geometry, t2.geometry))).geom as geometry 
into new_table from TEMP_OUTPUTTREE_2 t1, TEMP_OUTPUTTREE2_4 t2;

select (st_dump(st_union(nt.geometry, t3.geometry))).geom as geometry 
from new_table nt, TEMP_OUTPUTTREE_5 t3;

I don't know if st_union is using the spatial index. If the answer would 
be yes, it might be useful to create one on the new_table. But, 
depending on the size of your tables, I am afraid, that the queries will 
still be much slower than your intersection-queries.

Good luck and regards,

Birgit.



Am 24.11.2011 07:37, schrieb Atul Kumar:
>
> Hi Birgit,
>
> Now its taking less time as compare to previous one.
>
> One more help.
>
> Is there any way to optimize this query because its also taking long 
> time to execute.
>
> select st_union (st_union (t1.geometry, t2.geometry), t3.geometry) 
> geometry from TEMP_OUTPUTTREE_2 t1,TEMP_OUTPUTTREE2_4 t2, 
> TEMP_OUTPUTTREE_5 t3
>
> Thanks
>
> Atul
>
> *From:*postgis-users-bounces at postgis.refractions.net 
> [mailto:postgis-users-bounces at postgis.refractions.net] *On Behalf Of 
> *Birgit Laggner
> *Sent:* Wednesday, November 23, 2011 6:07 PM
> *To:* postgis-users at postgis.refractions.net
> *Subject:* Re: [postgis-users] Help me.
>
> 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  <mailto:postgis-users at postgis.refractions.net>
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
> 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/20111124/b767ca0d/attachment.html>