[postgis-users] Creating a simple polygon encompassing a given number of points

[René Fournier]

Yes, casting to/from did the trick:

	UPDATE territories SET border = (
SELECT ST_ConvexHull(ST_Collect(location::geometry)) FROM addresses WHERE territory_id = 3014
)::geography WHERE id = 3014;

Now, is it possible process all territory borders with one query? For example, this works for a single territory:

UPDATE territories SET border = (
SELECT ST_ConvexHull(ST_Collect(addresses.location::geometry)) FROM addresses WHERE addresses.territory_id = territories.id
)::geography WHERE id = 3014;
UPDATE 1

But if I leave out the WHERE clause, it doesn't:

UPDATE territories SET border = (
SELECT ST_ConvexHull(ST_Collect(addresses.location::geometry)) FROM addresses WHERE addresses.territory_id = territories.id
)::geography;
ERROR:  Geometry type (LineString) does not match column type (Polygon)

Just wondering, is this kind of update query with a subselect possible?

On 2012-09-12, at 10:04 AM, Nicolas Ribot wrote:

> Hi,
> 
> I think you could cast geography to geometry to collect, then cast
> back to geography to store ?
> 
> 
> 
> On 12 September 2012 17:42, René Fournier <m5 at renefournier.com> wrote:
>> Yes, this works great.
>> 
>> UPDATE territories SET border = (SELECT
>> ST_ConvexHull(ST_Collect(ST_GeomFromText('MULTIPOINT(-111.868842 50.569455,
>> -111.898634 50.569992, -111.907919 50.583764, -111.868859 50.569452)') ) ))
>> WHERE id = 3014;
>> 
>> 
>> Now... I'm trying to figure out, how to do the same, except create the
>> border (polygon) from points in another table, e.g.:
>> 
>> UPDATE territories SET border = (SELECT ST_ConvexHull(ST_Collect( SELECT
>> location FROM addresses WHERE territory_id = 3014 ))) WHERE id = 3014;
>> 
>> ...returns "ERROR:  function st_collect(geography) does not exist HINT:  No
>> function matches the given name and argument types. You might need to add
>> explicit type casts."
>> 
>> So I'm wondering, since both border (final polygon) and location (source of
>> points) are of type geography, what's the best way to go about this? Or
>> should I have just used geometry types? Sorry for the dumb questions, I'm
>> trying to define the schemas by best practices so I don't have to fix them
>> later. Geography seemed right when I started the project.
>> 
>> 
>> 
>> On 2012-09-11, at 1:18 AM, Paolo Corti wrote:
>> 
>> On Tue, Sep 11, 2012 at 3:44 AM, René Fournier <m5 at renefournier.com> wrote:
>> 
>> Thanks Nicolas,
>> 
>> 
>> UPDATE territories SET border =
>> 
>> ST_ConvexHull(ST_Collect(ST_GeomFromText('MULTIPOINT(50.569455 -111.868842,
>> 
>> 50.569992 -111.898634, 50.583764 -111.907919, 50.569452 -111.868859)') ) )
>> 
>> WHERE id = 3014;
>> 
>> ERROR:  cannot use aggregate function in UPDATE
>> 
>> LINE 1: UPDATE territories SET border = ST_ConvexHull(ST_Collect(ST_...
>> 
>> 
>> 
>> Using a sub-select should work:
>> 
>> UPDATE territories SET border = (SELECT
>> ST_ConvexHull(ST_Collect(ST_GeomFromText('MULTIPOINT(50.569455
>> -111.868842, 50.569992 -111.898634, 50.583764 -111.907919, 50.569452
>> -111.868859)') ) ))
>> WHERE id = 3014;
>> 
>> regards
>> p
>> 
>> --
>> Paolo Corti
>> Geospatial software developer
>> web: http://www.paolocorti.net
>> twitter: @capooti
>> skype: capooti
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> 
>> 
>> 
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120912/0aa53c1e/attachment.html>