<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body text="#000000" bgcolor="#FFFFFF">
Hi Atul,<br>
<br>
I often have tasks like this and in the end wrote a set of functions
to get the wanted result. (if you are interested, have look at:
<a class="moz-txt-link-freetext" href="http://trac.osgeo.org/postgis/wiki/UsersWikiExamplesOverlayTables2">http://trac.osgeo.org/postgis/wiki/UsersWikiExamplesOverlayTables2</a>)<br>
<br>
But I don't know if that wouldn't be to complicated in your use
case... You would have to test yourself which way would be more
suitable for you.<br>
<br>
But if you only want to display three different results together in
a map, why can't you display them as three separate layers (which
may be transparent, so you would see the borders of them all)?<br>
<br>
Regards,<br>
<br>
Birgit.<br>
<br>
<br>
Am 24.11.2011 11:47, schrieb Atul Kumar:
<blockquote
cite="mid:226D46DC86034D4985AAD4B4DF29F2B70C1087C2@HJ-MBX2.persistent.co.in"
type="cite">
<meta http-equiv="Content-Type" content="text/html;
charset=ISO-8859-1">
<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;}
@font-face
{font-family:"Times New Roman \, serif";}
/* 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.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:Consolas;
color:black;}
span.EmailStyle20
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.EmailStyle21
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle22
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle23
{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]-->
<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">I want all
boundaries of the geometries resulting from the three
queries to be display in one layer. Its better to me.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">If it is not
possible then will go with dissolving all inner boundaries .
<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">Please suggest.<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">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>
<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">
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>
[<a class="moz-txt-link-freetext" href="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</a>]
<b>On Behalf Of </b>Birgit Laggner<br>
<b>Sent:</b> Thursday, November 24, 2011 4:12 PM<br>
<b>To:</b> <a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><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">Hi Atul,<br>
<br>
with the st_union function, you aggregate all geometries
dissolving all inner boundaries. Is this what you want, or do
you want all boundaries of the geometries resulting from the
three queries to be displayed in one layer??<br>
<br>
Regards,<br>
<br>
Birgit.<br>
<br>
<br>
Am 24.11.2011 10:36, schrieb Atul Kumar: <o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D">Hi Birgit,</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D">I also don’t
know whether st_union is using spatial_index because new to
postgis.</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D">I am having GUI
which shows the data over map.
</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D">Suppose user
will make query on table1 and queried data display on map.
Queried data is geometry type data.</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D">Again user will
make query on table2 and queried data display on map.
Queried data is also geometry type data</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D">Again user will
make query on table3 and queried data display on map.
Queried data is also geometry type data</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D">At the end I
want to union above three queried data and display on map
using resultant queried union data.</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D">Please suggest
me is there other way to union geometry data.</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D">Thanks</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D">Atul </span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D"> </span><o:p></o:p></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">
<a moz-do-not-send="true"
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>
[<a moz-do-not-send="true"
href="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</a>]
<b>On Behalf Of </b>Birgit Laggner<br>
<b>Sent:</b> Thursday, November 24, 2011 2:42 PM<br>
<b>To:</b> <a moz-do-not-send="true"
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<b>Subject:</b> Re: [postgis-users] Help me.</span><o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">Hi Atul,<br>
<br>
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...
<br>
<br>
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:<br>
<br>
select (st_dump(st_union(t1.geometry, t2.geometry))).geom as
geometry into new_table from TEMP_OUTPUTTREE_2 t1,
TEMP_OUTPUTTREE2_4 t2;<br>
<br>
select (st_dump(st_union(nt.geometry, t3.geometry))).geom as
geometry from new_table nt, TEMP_OUTPUTTREE_5 t3;<br>
<br>
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.<br>
<br>
Good luck and regards,<br>
<br>
Birgit.<br>
<br>
<br>
<br>
Am 24.11.2011 07:37, schrieb Atul Kumar: <o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D">Hi Birgit,</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D">Now its taking
less time as compare to previous one.</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D">One more help.
</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D">Is there any
way to optimize this query because its also taking long time
to execute.</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D"> </span><o:p></o:p></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</span><o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D">Thanks</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D">Atul</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D"> </span><o:p></o:p></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">
<a moz-do-not-send="true"
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>
[<a moz-do-not-send="true"
href="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</a>]
<b>On Behalf Of </b>Birgit Laggner<br>
<b>Sent:</b> Wednesday, November 23, 2011 6:07 PM<br>
<b>To:</b> <a moz-do-not-send="true"
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<b>Subject:</b> Re: [postgis-users] Help me.</span><o:p></o:p></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"><br>
<br>
<br>
<br>
<br>
</span><o:p></o:p></p>
<pre>_______________________________________________<o:p></o:p></pre>
<pre>postgis-users mailing list<o:p></o:p></pre>
<pre><a moz-do-not-send="true" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><o:p></o:p></pre>
<pre><a moz-do-not-send="true" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><o:p></o:p></pre>
<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","serif""><br>
<br>
<br>
<br>
</span><o:p></o:p></p>
<pre>_______________________________________________<o:p></o:p></pre>
<pre>postgis-users mailing list<o:p></o:p></pre>
<pre><a moz-do-not-send="true" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><o:p></o:p></pre>
<pre><a moz-do-not-send="true" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><o:p></o:p></pre>
<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 moz-do-not-send="true" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><o:p></o:p></pre>
<pre><a moz-do-not-send="true" 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>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
</body>
</html>