[postgis-users] Help?: Constructing a geometry collection

[John Poole]

That worked; sublimely simple.
Thank you!


On Mon, Sep 27, 2010 at 3:43 PM, Richard Greenwood
<richard.greenwood at gmail.com> wrote:
> I think this should do the trick:
>
>  SELECT EXTENT(the_geom)
>    FROM parcels_public
>    WHERE gid > 27611 AND gid < 28000;
>
>
> Rich
>
>
>
>
> On Mon, Sep 27, 2010 at 9:05 AM, John Poole <jlpoole56 at gmail.com> wrote:
>> I've been grappling with the following concept and reviewing the
>> PostGIS 1.5.1 Manual and cannot seem to come up with a working
>> solution to achieve this simple goal:
>>
>> GOAL: I want to collect various polygons and then determine the
>> bounding box or extent of the collection in order to display an
>> appropriately zoomed image on MapServer
>>
>> Here's the SQL I'm starting with:
>>
>>   select the_geom from parcels_public where gid > 27611 and gid < 28000
>> returns
>>  388 rows of geometries.
>>
>> How do I create a geometry collection from a set of rows?  I've been
>> looking at ST_Collect and trying:
>>
>> * * * warning below is bad code * * *
>> Select ST_Collect((select the_geom from parcels_public where gid >
>> 27611 and gid < 28000)) as myCollectionObject
>>
>> which produces:
>>   ERROR:  more than one row returned by a subquery used as an expression
>>
>>   ********** Error **********
>>
>>   ERROR: more than one row returned by a subquery used as an expression
>>   SQL state: 21000
>>
>> * * *
>>
>> Referencing the thread in the documentation,
>> http://postgis.refractions.net/pipermail/postgis-users/2008-June/020331.html
>> ,
>> I tried this
>>
>> * * * warning below is bad code * * *
>> SELECT ST_Multi(ST_Collect(f.the_geom)) as singlegeom
>>        FROM (
>>                SELECT (
>>                        select the_geom
>>                        from parcels_public
>>                        where gid > 27611 and gid < 27640
>>                )
>>        ) As f
>> which produces:
>>  ERROR:  column f.the_geom does not exist
>>  LINE 1: SELECT ST_Multi(ST_Collect(f.the_geom)) as singlegeom
>>                                     ^
>>
>>  ********** Error **********
>>
>>  ERROR: column f.the_geom does not exist
>>  SQL state: 42703
>>  Character: 28
>> * * *
>>
>> I know this can be done, but I seemed to be missing something very
>> basic both in creating subqueries and in the documentation and I'm
>> confusing structure, e.g. rows returned vs. a geometry collection.
>> Maybe my problem is that I do not know how to construct a geometry
>> collection from a given set of rows?
>>
>> The end goal is to have the coordinates of the collection so I can use
>> them as an extent to send to MapServer to create an image that is
>> zoomed into the minimum size that displays all members of the
>> collection.  I anticipate using ST_Extent(myCollectionObject) once I'm
>> able to create the collection object.
>>
>> --
>> John L. Poole
>>
>> P.O. Box 6566
>> Napa, CA 94581-6566
>> 707-812-1323
>>
>> jlpoole56 at gmail.com
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>
>
>
> --
> Richard Greenwood
> richard.greenwood at gmail.com
> www.greenwoodmap.com
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>



-- 
John L. Poole

P.O. Box 6566
Napa, CA 94581-6566
707-812-1323

jlpoole56 at gmail.com