Yes. Sorry I meant use ST_Union. INSERT INTO newtable(stusps, the_geom) SELECT stusps, ST_Multi(ST_Union(the_geom)) as singlegeom FROM somestatefield GROUP BY stusps
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of David Jantzen Sent: Sunday, June 22, 2008 12:32 AM To: PostGIS Users Discussion Subject: RE: [postgis-users] Normalizing nationalatlas.gov data Hi Regina, thanks for the response. The original data is made up of MultiPolygons, and so the result of ST_Collect is a GeometryCollection. ST_Multi fails to convert the collection into a MultiPolygon, maybe it can't do so. So, I guess the question is whether it's possible to convert a GeometryCollection of MultiPolygons into a single MultiPolygon. QuantumGIS can't read GeometryCollections (or at least 0.8.1 can't, maybe newer versions can?), which is a requirement for what I'm trying to do. I wonder if it could handle an array of MultiPolygons... Your second example looks the same as the first, did you mean to type something else? Thanks, David > INSERT INTO newtable(stusps, the_geom) SELECT stusps, > ST_Multi(ST_Collect(the_geom)) as singlegeom FROM somestatefield > GROUP BY stusps > > > Or > > INSERT INTO newtable(stusps, the_geom) SELECT stusps, > ST_Multi(ST_Collect(the_geom)) as singlegeom FROM somestatefield > GROUP BY stusps > > > ST_Collect tends to be faster and if they are single polygons is safe > to use, with MULTIS it will create GEOMETRYCollections which are hard > to deal with. > > ST_Union will dissolve some boundaries and can work with MULTIPOLYGONS > > Hope that helps, > Regina > > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of > David Jantzen > Sent: Saturday, June 21, 2008 7:37 PM > To: postgis > Subject: [postgis-users] Normalizing nationalatlas.gov data > > Hi All, > > I've been exploring the data up on nationalatlas.gov, in particular > the states, counties and urban areas data: > > http://nationalatlas.gov/atlasftp.html?openChapters=%2Cchpref% > 2Cchpbound#chppeopl > > I'd like to load these into a postgis-enabled database for use by our > website. At the same time, I'd strongly prefer a set of normalized > tables for all this information, so that we don't have redundant (and > inconsistent) state, county, city information, etc, but rather, > foreign keys. However, many of the data sets from nationalatlas are > comprised of multiple polygon records for each entity. For example, > the Washington state polygon data is spread across 50 rows. > > ?Is it possible to merge the multiple polygons into a single geometry > object using postgis functions? (I tried various operations > converting geometries to and from text without success.) Failing > that, are there comparable data sets out there that can be normalized > without turning the polygon data into a one-to-many relation? > > Thanks, > David > > > _______________________________________________ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > _______________________________________________ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
