[postgis-users] Postigs layer in Mapserver

[Paul Ramsey]

You also made a small error in your example of what cannot be included 
in a "GROUP BY" situation. Citing the example

SELECT COUNT(*) FROM THETABLE GROUP BY POSTAL_CODE

you said that one could not just include other attributes in the SQL, 
such as POSTAL_CODE, for example.  However, POSTAL_CODE is the one 
attribute you *can* include in this situation, since it is the grouping 
variable.

SELECT POSTAL_CODE,COUNT(*) from THETABLE GROUP BY POSTAL_CODE

will return the count of postal codes for each distinct postal code. As 
you noted, for any *other* variable, the only way to include them is to 
use an aggregate function, like min() or max().

Note that you can also include geometries in these statements, but only 
if you use an spatial aggregate function. Currently, there are only two 
spatial aggregates in PostGIS: extent(), and collect(). Extent() returns 
the maximum bounding box of the set of geometries, and collect() returns 
a geometrycollection of the set.  Once we have spatial operators like 
union(<geometry>,<geometry>) it might make sense to also create an 
aggregate function union() (although it would be incredibly slow I am sure).

P.

Jan Hartmann wrote:
> Sorry, typo again (forgot to install my SQL-checker). The final select 
> should be:

-- 
       __
      /
      | Paul Ramsey
      | Refractions Research
      | Email: pramsey at refractions.net
      | Phone: (250) 885-0632
      \_