[postgis-users] Union, Collects ?? - Need Help

Constantine.Carmen at epamail.epa.gov Constantine.Carmen at epamail.epa.gov
Tue Aug 26 10:55:30 PDT 2008

I am very new to PostGIS (about 1 month of trial and error). I think
    that I am close to solving this issue,  but I just can't get it.
Here is
    my issue:
    I have a table of us_states, 50 records, one per state. So even
    with multiple island polys are represented as on record. Each state
    a Region code. So, and example, Region 2 is NY and NJ; Region 1 is
    NH, VT, RI, CT, and MA; etc. I want to select-out each region as a
    I want that View to have one geometry. If the borders between states
    "dissolved", that's fine too, but not necessary. So in my View for
    Region 2, even though it will contain two states (NY, NJ), I would
    those two entities to behave as one geometry. Did I say that right?
    have Tried:
    ST_Union, ST_Collects, Geom_Union. In most cases a 'View' is
    but it will not display in QGIS. An error displays that indicates
    OID, or Keyfield does not exist. I have even tried a piece of code:
    GROUP BY. I am very familiar with ESRI, I am thinking that you need
    do a select by Region. This shouldn't be that difficult. Can someone
    please help me??
    Here are some examples:

       ST_Union(s.the_geom) AS reggeo
         FROM usa.us_states As s,
       GROUP BY reg


       SELECT ST_Collect(the_geom)AS reg_geom
          id, reg
          FROM usa.us_states
          WHERE reg = '03';

    Carmen Constantine
    US EPA Region 3 GIS Team
    Vistronix, Inc
    On-Site Contractor

More information about the postgis-users mailing list