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

[Richard Greenwood]

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