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 <m...@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 <m...@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@postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users@postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
> _______________________________________________
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
