<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
you can alss use a "with" statement as indicated by nicolas, but be
certain that what you want is to get the "sum" of all polygon of
your collection<br>
<br>
bye<br>
<br>
Le 26/04/2012 17:51, ju a écrit :
<blockquote cite="mid:4F996F1B.2030409@ecrins-parcnational.fr"
type="cite">
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
ok so this is second solution.<br>
<br>
1) fisrt count the number of objet of the collection<br>
<br>
select ST_NumGeometries(the_geom) as the_nb from YOURTABLE<br>
<br>
you will obtain the number of collection, so probably 4 polygon<br>
<br>
2) then "explose" theses 4 polygon :<br>
<br>
SELECT <br>
what you want....,<br>
n, <br>
ST_GeometryN(the_geom, n) as geom_unique,<br>
ST_astext(ST_GeometryN(the_geom, n)) as geom_unique_txt<br>
FROM YOURTABLE CROSS JOIN generate_series(1,100) n<br>
WHERE n <= ST_NumGeometries(the_geom);<br>
<br>
note : 100 is default for serie, if you count 4 objet in
collection you can write serie(1,4)<br>
<br>
you shoud obtain 4 rows, the text format is juste here so as you
see the polygon coordinate<br>
<br>
<br>
3) 2 choices<br>
<br>
3.1 if one of the 4 polygon is what you want, then use it. for
example if it is the n number 3 then update your table with<br>
<br>
update YOURTABLE set the_geom =<br>
ST_GeometryN(the_geom, 3)<br>
(dont forget a WHERE .... id of the row you want to update)<br>
<br>
3.2) if you want to group all the 4 polygone to make a new one
there are other possibility<br>
- for example make a table with the 4 polygons ( same query but
"select into table" .....)<br>
with this table make a dissolve query to group the 4 objetc<br>
- update your table with the result of dissolve<br>
<br>
- you can also use array to group, etc...<br>
<br>
<br>
tip : sometimes it is quick to copy /past result wkt of query<br>
for example you copy the result wkt of the query for objet
collection number 3:<br>
<br>
POLYGON((892426.5625 6463663,892432.9375 6463678,892434.4375
6463685,892454.75 6463802,892457.0625 6463879.5,892456.6875
6463879.5,892453.6875 6463931,892453 6463931))<br>
<br>
then you make a polygon with it :<br>
<br>
<pre>update matable set the_geom = ST_GeometryFromText('POLYGON((892426.5625 6463663,892432.9375 6463678,892434.4375
6463685,892454.75 6463802,892457.0625 6463879.5,892456.6875
6463879.5,892453.6875 6463931,892453 6463931))'
(dont forget a WHERE .... id of the row you want to update)
</pre>
<br>
<br>
Le 26/04/2012 17:22, Mehmet Erkek a écrit :
<blockquote
cite="mid:C3446A1A12F8D240B6A9683F82A8D195619C84@EXVBE014-14.exch014.msoutlookonline.net"
type="cite">
<meta http-equiv="Content-Type" content="text/html;
charset=ISO-8859-1">
<meta name="Generator" content="Microsoft Word 14 (filtered
medium)">
<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]-->
<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:Verdana;
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:0cm;
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;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0cm;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";
color:black;}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate
{mso-style-priority:99;
mso-style-link:"Balloon Text Char";
margin:0cm;
margin-bottom:.0001pt;
font-size:8.0pt;
font-family:"Tahoma","sans-serif";
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.BalloonTextChar
{mso-style-name:"Balloon Text Char";
mso-style-priority:99;
mso-style-link:"Balloon Text";
font-family:"Tahoma","sans-serif";}
span.EmailStyle21
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.EmailStyle22
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></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="WordSection1">
<p class="MsoNormal"><span style="color:windowtext">Hi Juilen,
thanks for quick answer. <o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:windowtext"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:windowtext">I have </span><span
style="color:windowtext">1 geometry and 1 row on the
table. this geometry is multiploygon which consists of 4
polygons.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:windowtext">I want to
convert this mutlipolygon to one single polygon..<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:windowtext"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:windowtext">Any idea
how I can do this?<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:windowtext">I hope it
is clearer now.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:windowtext"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:windowtext">Thanks.<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 0cm 0cm 0cm">
<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"
class="moz-txt-link-abbreviated"
href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>
[<a moz-do-not-send="true"
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>ju<br>
<b>Sent:</b> 26 Nisan 2012 18:02<br>
<b>To:</b> PostGIS Users Discussion<br>
<b>Subject:</b> Re: [postgis-users] [!! SPAM]
multipolygon to single polygon<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">not clear...<br>
<br>
1) if you have 4 polygons in 4 rows table distinct, this is
not a multi object issu. for this case use<br>
dissolve to group polygone into only one, this is not a
topic of dump.<br>
<br>
<br>
2) if you have 1 geometry and 1 row on the tablme, so this
is a multi object topic<br>
you must sperate the differents objet of the collection<br>
<br>
here : <a moz-do-not-send="true"
href="http://www.postgis.org/docs/ST_GeometryN.html">http://www.postgis.org/docs/ST_GeometryN.html</a><o:p></o:p></p>
<pre>--Extracting all geometries (useful when you want to assign an id)<o:p></o:p></pre>
<pre>SELECT gid, n, ST_GeometryN(the_geom, n)<o:p></o:p></pre>
<pre>FROM sometable CROSS JOIN generate_series(1,100) n<o:p></o:p></pre>
<pre>WHERE n <= ST_NumGeometries(the_geom);<o:p></o:p></pre>
<p class="MsoNormal"><br>
best regards, julien<br>
<br>
<br>
<br>
Le 26/04/2012 16:27, Mehmet Erkek a écrit : <o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">I have multipolygons which I would like
to convert a single polygon. is there a way to do this?
please check the attached image. I tried st_dump and
afterwards st_union , but eventually I get a multipolygon
again, not polygon. <o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal"><img id="Picture_x0020_1"
src="cid:part4.05010208.06020901@ecrins-parcnational.fr"
border="0" height="341" width="851"><o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal"><b><span style="font-size:10.0pt">Mehmet
Erkek</span></b><o:p></o:p></p>
<p class="MsoNormal"><a moz-do-not-send="true"
href="http://www.reidin.com/"><span
style="font-size:7.0pt;font-family:"Verdana","sans-serif";color:black">www.REIDIN.com</span></a><span
style="font-size:7.0pt;font-family:"Verdana","sans-serif""> </span><o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal"><span
style="font-size:12.0pt;font-family:"Times New
Roman","serif""><br>
This message is for the designated recipient only and may
contain privileged, proprietary, or otherwise private
information. If you have received it in error, please
notify the sender immediately and delete the original. Any
other use of the email by you is prohibited. Please
Consider the Environment Before Printing This Email <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>
<p class="MsoNormal"><span
style="font-size:12.0pt;font-family:"Times New
Roman","serif""><br>
<br>
<o:p></o:p></span></p>
<pre>-- <o:p></o:p></pre>
<pre>Julien-Pierre Guilloux<o:p></o:p></pre>
<pre>Service scientifique - Géomatique<o:p></o:p></pre>
<pre>Parc National des Ecrins<o:p></o:p></pre>
<pre>Domaine de Charance<o:p></o:p></pre>
<pre>05 000 GAP<o:p></o:p></pre>
<pre>tél : 04.92.40.20.10<o:p></o:p></pre>
<pre>fax : 04.92.52.38.34<o:p></o:p></pre>
<pre><a moz-do-not-send="true" href="mailto:julien.guilloux@ecrins-parcnational.fr">julien.guilloux@ecrins-parcnational.fr</a><o:p></o:p></pre>
</div>
<br>
This message is for the designated recipient only and may
contain privileged, proprietary, or otherwise private
information. If you have received it in error, please notify the
sender immediately and delete the original. Any other use of the
email by you is prohibited. Please Consider the Environment
Before Printing This Email <br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
postgis-users mailing list
<a moz-do-not-send="true" class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a moz-do-not-send="true" 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>
<br>
<pre class="moz-signature" cols="72">--
Julien-Pierre Guilloux
Service scientifique - Géomatique
Parc National des Ecrins
Domaine de Charance
05 000 GAP
tél : 04.92.40.20.10
fax : 04.92.52.38.34
<a moz-do-not-send="true" class="moz-txt-link-abbreviated" href="mailto:julien.guilloux@ecrins-parcnational.fr">julien.guilloux@ecrins-parcnational.fr</a></pre>
<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>
<br>
<pre class="moz-signature" cols="72">--
Julien-Pierre Guilloux
Service scientifique - Géomatique
Parc National des Ecrins
Domaine de Charance
05 000 GAP
tél : 04.92.40.20.10
fax : 04.92.52.38.34
<a class="moz-txt-link-abbreviated" href="mailto:julien.guilloux@ecrins-parcnational.fr">julien.guilloux@ecrins-parcnational.fr</a></pre>
</body>
</html>