<html><head></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">Yes, casting to/from did the trick:<div><br></div><div><span class="Apple-tab-span" style="white-space:pre"> UPDATE territories SET border = (
SELECT ST_ConvexHull(ST_Collect(location::geometry)) FROM addresses WHERE territory_id = 3014
)::geography WHERE id = 3014;</span></div><div><br></div><div>Now, is it possible process all territory borders with one query? For example, this works for a single territory:</div><div><br></div><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;"><div><div>UPDATE territories SET border = (</div><div>SELECT ST_ConvexHull(ST_Collect(addresses.location::geometry)) FROM addresses WHERE addresses.territory_id = territories.id</div><div>)::geography WHERE id = 3014;</div><div>UPDATE 1</div></div></blockquote><div><div><br></div><div>But if I leave out the WHERE clause, it doesn't:</div><div><br></div></div><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;"><div><div>UPDATE territories SET border = (</div><div>SELECT ST_ConvexHull(ST_Collect(addresses.location::geometry)) FROM addresses WHERE addresses.territory_id = territories.id</div><div>)::geography;</div><div>ERROR: Geometry type (LineString) does not match column type (Polygon)</div></div><div><div><br></div></div></blockquote>Just wondering, is this kind of update query with a subselect possible?<br><div><br><div><div>On 2012-09-12, at 10:04 AM, Nicolas Ribot wrote:</div><br class="Apple-interchange-newline"><blockquote type="cite"><div>Hi,<br><br>I think you could cast geography to geometry to collect, then cast<br>back to geography to store ?<br><br><br><br>On 12 September 2012 17:42, René Fournier <<a href="mailto:m5@renefournier.com">m5@renefournier.com</a>> wrote:<br><blockquote type="cite">Yes, this works great.<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">UPDATE territories SET border = (SELECT<br></blockquote><blockquote type="cite">ST_ConvexHull(ST_Collect(ST_GeomFromText('MULTIPOINT(-111.868842 50.569455,<br></blockquote><blockquote type="cite">-111.898634 50.569992, -111.907919 50.583764, -111.868859 50.569452)') ) ))<br></blockquote><blockquote type="cite">WHERE id = 3014;<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">Now... I'm trying to figure out, how to do the same, except create the<br></blockquote><blockquote type="cite">border (polygon) from points in another table, e.g.:<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">UPDATE territories SET border = (SELECT ST_ConvexHull(ST_Collect( SELECT<br></blockquote><blockquote type="cite">location FROM addresses WHERE territory_id = 3014 ))) WHERE id = 3014;<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">...returns "ERROR: function st_collect(geography) does not exist HINT: No<br></blockquote><blockquote type="cite">function matches the given name and argument types. You might need to add<br></blockquote><blockquote type="cite">explicit type casts."<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">So I'm wondering, since both border (final polygon) and location (source of<br></blockquote><blockquote type="cite">points) are of type geography, what's the best way to go about this? Or<br></blockquote><blockquote type="cite">should I have just used geometry types? Sorry for the dumb questions, I'm<br></blockquote><blockquote type="cite">trying to define the schemas by best practices so I don't have to fix them<br></blockquote><blockquote type="cite">later. Geography seemed right when I started the project.<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">On 2012-09-11, at 1:18 AM, Paolo Corti wrote:<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">On Tue, Sep 11, 2012 at 3:44 AM, René Fournier <<a href="mailto:m5@renefournier.com">m5@renefournier.com</a>> wrote:<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">Thanks Nicolas,<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">UPDATE territories SET border =<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">ST_ConvexHull(ST_Collect(ST_GeomFromText('MULTIPOINT(50.569455 -111.868842,<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">50.569992 -111.898634, 50.583764 -111.907919, 50.569452 -111.868859)') ) )<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">WHERE id = 3014;<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">ERROR: cannot use aggregate function in UPDATE<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">LINE 1: UPDATE territories SET border = ST_ConvexHull(ST_Collect(ST_...<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">Using a sub-select should work:<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">UPDATE territories SET border = (SELECT<br></blockquote><blockquote type="cite">ST_ConvexHull(ST_Collect(ST_GeomFromText('MULTIPOINT(50.569455<br></blockquote><blockquote type="cite">-111.868842, 50.569992 -111.898634, 50.583764 -111.907919, 50.569452<br></blockquote><blockquote type="cite">-111.868859)') ) ))<br></blockquote><blockquote type="cite">WHERE id = 3014;<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">regards<br></blockquote><blockquote type="cite">p<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">--<br></blockquote><blockquote type="cite">Paolo Corti<br></blockquote><blockquote type="cite">Geospatial software developer<br></blockquote><blockquote type="cite">web: <a href="http://www.paolocorti.net">http://www.paolocorti.net</a><br></blockquote><blockquote type="cite">twitter: @capooti<br></blockquote><blockquote type="cite">skype: capooti<br></blockquote><blockquote type="cite">_______________________________________________<br></blockquote><blockquote type="cite">postgis-users mailing list<br></blockquote><blockquote type="cite"><a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br></blockquote><blockquote type="cite"><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">_______________________________________________<br></blockquote><blockquote type="cite">postgis-users mailing list<br></blockquote><blockquote type="cite"><a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br></blockquote><blockquote type="cite"><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br></blockquote><blockquote type="cite"><br></blockquote>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>http://postgis.refractions.net/mailman/listinfo/postgis-users<br></div></blockquote></div><br></div></body></html>