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 [email protected] _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
