<div>Thanks Regina!<br><br></div>
<div class="gmail_quote">On Thu, May 1, 2008 at 9:19 AM, Obe, Regina <<a href="mailto:robe.dnd@cityofboston.gov">robe.dnd@cityofboston.gov</a>> wrote:<br>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: #ccc 1px solid">
<div>
<div dir="ltr" align="left"><span><font face="Arial" color="#0000ff" size="2">I wouldn't quite call it runnning the function in that table. Basically the SQL statement will create a temporary or in memory table so to speak. So short answer - yes it is correct - no need to create a new table or geometry column. Sometimes you may want to if you use it often or you are grouping many geometries since the planner has to recalculate each time if it is a dynamic query as below or view (a saved dynamic query as Kevin pointed out in last post) .</font></span></div>
<div dir="ltr" align="left"><span><font face="Arial" color="#0000ff" size="2"></font></span> </div>
<div dir="ltr" align="left"><span><font face="Arial" color="#0000ff" size="2"></font></span> </div>
<div dir="ltr" align="left"><span><font face="Arial" color="#0000ff" size="2">Well there should be an alias there otherwise it usually will just alias it as something dumb like ST_Union - so let me correct my mistake.</font></span></div>
<div dir="ltr" align="left"><span>SELECT somefield, ST_Union(the_geom) as newgeom
<div class="Ih2E3d"><br>FROM sometable<br>GROUP BY somefield;</div></span></div>
<div><span></span> </div>
<div><span><font face="Arial" color="#0000ff" size="2">If you wanted to materialize it, I tend to do something like</font></span></div>
<div><span><font face="Arial" color="#0000ff" size="2"></font></span> </div>
<div><span><font face="Arial" color="#0000ff" size="2">SELECT somefield, <font face="Times New Roman" color="#000000" size="3">ST_Union(the_geom) as newgeom</font><br>INTO somenewtable</font></span></div>
<div><span><font face="Arial" color="#0000ff" size="2">FROM sometable</font></span></div>
<div><span><font face="Arial" color="#0000ff" size="2">GROUP BY somefield;</font></span></div>
<div><span><font face="Arial" color="#0000ff" size="2"></font></span> </div>
<div><span><font face="Arial" color="#0000ff" size="2">A lot of people do </font></span></div>
<div><span><font face="Arial" color="#0000ff" size="2">CREATE TABLE somenewtable As </font></span></div>
<div><span>
<div><span><font face="Arial" color="#0000ff" size="2">SELECT somefield, <font face="Times New Roman" color="#000000" size="3">ST_Union(the_geom) as newgeom</font></font></span></div>
<div><span><font face="Arial" color="#0000ff" size="2">FROM sometable</font></span></div>
<div><span><font face="Arial" color="#0000ff" size="2">GROUP BY somefield;</font></span></div>
<div><span><font face="Arial" color="#0000ff" size="2"></font></span> </div>
<div><span><font face="Arial" color="#0000ff" size="2">But I tend to avoid that second syntax since its not as portable as option 1 (from DBMS to DBMS at least the DBMS I tend to deal with) and the speed is the same. Granted</font></span></div>
<div><span><font face="Arial" color="#0000ff" size="2">I guess the second version is a bit clearer.</font></span></div>
<div><span><font face="Arial" color="#0000ff" size="2"></font></span> </div>
<div><span><font face="Arial" color="#0000ff" size="2">Hope that helps,</font></span></div>
<div><span><font face="Arial" color="#0000ff" size="2">Regina</font></span></div></span><span></span></div>
<div dir="ltr" align="left"><br></div><br>
<div lang="en-us" dir="ltr" align="left">
<hr>
<font face="Tahoma" size="2"><b>From:</b> <a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank">postgis-users-bounces@postgis.refractions.net</a> [mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank">postgis-users-bounces@postgis.refractions.net</a>] <b>On Behalf Of </b>George Silva<br>
<b>Sent:</b> Wednesday, April 30, 2008 10:47 PM<br><b>To:</b> PostGIS Users Discussion<br><b>Subject:</b> Re: [postgis-users] Line To Path<br></font><br></div>
<div>
<div></div>
<div class="Wj3C7c">
<div></div>
<div>In the same trailing of that question and the answer, that select statement would run the function st_union in that table, without the need to create a new table or geometry column?</div>
<div> </div>
<div>Sorry to use this post for this, just tought its a quite novice question, so more people could use the answer.</div>
<div> </div>
<div>Thx</div>
<div> </div>
<div>Att.</div>
<div> </div>
<div>George<br><br></div>
<div class="gmail_quote">On Wed, Apr 30, 2008 at 7:20 PM, Paragon Corporation <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>> wrote:<br>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: #ccc 1px solid">Bob,<br><br>If I understand you correctly, I think you want to use one of the following<br><br>SELECT somefield, ST_Union(the_geom)<br>
FROM sometable<br>GROUP BY somefield<br><br>So lets say you want to collapse 3 rows into 1 then you just need to group<br>by some common field.<br><br>E.g. if somefield = 1 for your 3 records, then those would get rolled into<br>
the same record.<br><br>The above will give you a LINESTRING or MULTILINESTRING. If you have all<br>LINESTRINGS, then may be more efficient to do this. The below will first<br>collapse all with common somefield into a MULTILINESTRING and then the<br>
LineMerge will do the best it can to stitch back into a single line string.<br>This is not possible with completely disjoint linestrings.<br><br>SELECT somefield, ST_LineMerge(ST_Collect(the_geom))<br>FROM sometable<br>GROUP BY somefield<br>
<br><br>If you are using the older version of Postgis, you can just take out the ST_<br>in the examples I have above.<br><br>Hope that helps,<br>Regina<br>
<div>
<div></div>
<div><br>-----Original Message-----<br>From: <a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank">postgis-users-bounces@postgis.refractions.net</a><br>[mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank">postgis-users-bounces@postgis.refractions.net</a>] On Behalf Of Bob<br>
Pawley<br>Sent: Wednesday, April 30, 2008 12:40 PM<br>To: PostGIS Users Discussion<br>Subject: [postgis-users] Line To Path<br><br>Is there a method of converting three lines that require three rows into a<br>path that occupies a single row??<br>
<br>Bob Pawley<br><br>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br><br><br>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
</div></div></blockquote></div><br></div></div></div>
<div>
<p>
<hr size="1">
<p></p>
<p><strong>The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer. </strong></p>
</p></div>
<p>
<hr size="1">
<p></p>
<p><strong><font color="#339900" size="2">Help make the earth a greener place. If at all possible resist printing this email and join us in saving paper. </font></strong></p>
<p></p><br>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br></p></blockquote></div><br>