<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 12 (filtered medium)">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
color:black;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman","serif";
color:black;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0in;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";
color:black;}
span.EmailStyle17
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:Consolas;
color:black;}
span.EmailStyle21
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.Section1
{page:Section1;}
-->
</style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body bgcolor="white" lang="EN-US" link="blue" vlink="purple">
<div class="Section1">
<p class="MsoNormal"><span style="color:#1F497D">Hi Birgit,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Now its taking less time as compare to previous one.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">One more help. <o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Is there any way to optimize this query because its also taking long time to execute.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">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<o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span style="color:#1F497D">Thanks<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Atul<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif";
color:windowtext">From:</span></b><span style="font-size:10.0pt;font-family:
"Tahoma","sans-serif";color:windowtext"> postgis-users-bounces@postgis.refractions.net [mailto:postgis-users-bounces@postgis.refractions.net]
<b>On Behalf Of </b>Birgit Laggner<br>
<b>Sent:</b> Wednesday, November 23, 2011 6:07 PM<br>
<b>To:</b> postgis-users@postgis.refractions.net<br>
<b>Subject:</b> Re: [postgis-users] Help me.<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Hallo Atul,<br>
<br>
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:<br>
<br>
--create a spatial index on each input table:<br>
create index temp_outputtree_2_gist_geometry on temp_outputtree_2 using gist(geometry);<br>
create index temp_outputtree2_4_gist_geometry on temp_outputtree2_4 using gist(geometry);<br>
create index temp_outputtree_5_gist_geometry on temp_outputtree_5 using gist(geometry);<br>
<br>
--intersection of the first 2 tables using the spatial index, writing the result of it into a new table:<br>
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);<br>
<br>
--create a spatial index on the new geometries of the first intersection:<br>
create index new_table_gist_geometry on new_table using gist(geometry);<br>
<br>
--intersection with the third table:<br>
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);<br>
<br>
Hope that helps,<br>
<br>
Birgit.<br>
<br>
Am 23.11.2011 13:16, schrieb Atul Kumar: <o:p></o:p></p>
<p class="MsoNormal">Hi All,<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">I am trying to intersection multiple sets of geographical data using ST_intersection function. But query execution time is long.
<o:p></o:p></p>
<p class="MsoNormal">My Query is : <o:p></o:p></p>
<p class="MsoNormal">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<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">I am having three table its having geometry data. I want to intersect operation on those data.<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">Please suggest, Is there any optimal way to get the intersection with less execution time?<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">Thanks<o:p></o:p></p>
<p class="MsoNormal">Atul Kumar<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p>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.<o:p></o:p></p>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Times New Roman","serif""><br>
<br>
<br>
<o:p></o:p></span></p>
<pre>_______________________________________________<o:p></o:p></pre>
<pre>postgis-users mailing list<o:p></o:p></pre>
<pre><a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><o:p></o:p></pre>
<pre><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><o:p></o:p></pre>
</div>
<p>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.</p>
</body>
</html>