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

[Burgholzer,Robert]

Carmen,
The trouble here is not with PostGIS per se, but with the SQL statement
and what Qgis expects.  When Qgis loads a table from postgres, it wants
there to be a column named OID.  

You can do this by creating a view that includes an OID column -
although this is most easily done if your table already has an OID
column -- can you view your table of states in Qgis??.  If your table
has an OID column and you can view it in Qgis, you could do this:

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

The other option is to create a new table of regions, given the fact
that the EPA regions (I assume this is what you are getting at) are
static, this is a decent option as well.  You could do this by:

CREATE TABLE epa_regions (
OID SERIAL,
reg varchar(64)
);

select addGeometryColumn('public', 'epa_regions', 'MULTIPOLYGON', 2);
INSERT INTO epa_regions (reg, the_geom) 
SELECT reg, ST_Union(s.the_geom) AS reggeo 
FROM usa.us_states As s,
GROUP BY reg;

This should work for you.  Good luck!!

r.b.

Robert W. Burgholzer
Surface Water Modeler
Office of Water Supply and Planning
Virginia Department of Environmental Quality
rwburgholzer at deq.virginia.gov
804-698-4405
Open Source Modeling Tools:
http://sourceforge.net/projects/npsource/

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Constantine.Carmen at epamail.epa.gov
Sent: Tuesday, August 26, 2008 1:56 PM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] Union, Collects ?? - Need Help


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
states
    with multiple island polys are represented as on record. Each state
has
    a Region code. So, and example, Region 2 is NY and NJ; Region 1 is
ME,
    NH, VT, RI, CT, and MA; etc. I want to select-out each region as a
View.
    I want that View to have one geometry. If the borders between states
are
    "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
like
    those two entities to behave as one geometry. Did I say that right?
I
    have Tried:
    ST_Union, ST_Collects, Geom_Union. In most cases a 'View' is
created,
    but it will not display in QGIS. An error displays that indicates
that
    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
to
    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

       OR

       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
    215-814-5418

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users