[postgis-users] help with SQL/geomunion useage/combining polygons

[Brent Wood]

I have a table with polygon, and other attributes including date and a number
representing sea ice cover (ct value).

(I plan to use this with PostGIS & with Quantum GIS, the sql in the script
below to create the entry in the geometry_columns table is to allow QGIS to
plot the view)

What I'd like to do is generate a new table (or view) which has touching
polygons merged (combined) by date & ct, eg: for a given date, give me the
merged polygons representing all ice areas with a ct between 1 and 35.

I have tried a few versions of the script below. While it does work as far as
it goes, I'm still getting contiguous polygons in the output. My hope was that
GeomUnion would dissolve the shared boundries of contiguous polygons, however
it does not seem to do this.

Ideally a function like "envelope", but returning the outer perimeter, not the
bounding box.


Any suggestions as to how this can be done in POstGIS appreciated....

  Brent Wood



#!/bin/bash
# test script to create ice geomunions
#
# B Wood March 2005

DB=antarctic
TAB=natice
VIEW=v_natice


psql $DB -c "drop view $VIEW;"
psql $DB -c "delete from geometry_columns
             where f_table_name = '$VIEW';"

psql $DB -c "create view $VIEW as
             select min(oid) as oid,
                    _date,
                    35 as ct,
                    geomunion(the_geom) as the_geom
             from $TAB
             where _date = '1997-10-27'
               and ct > 0
               and ct <= 35
             group by ct,
                      _date;"

  psql $DB -c "insert into geometry_columns
                (f_table_catalog,
                 f_table_schema,
                 f_table_name,
                 f_geometry_column,
                 coord_dimension,
                 srid,
                 type)
               values
                ('',
                 'public',
                 '$VIEW',
                 'the_geom',
                 2,
                 20001,
                 'MULTIPOLYGON');"