David, Union shouldn't cause any of those problems. If you are sure they have states demarcated in them, then could be a bug in ST_Union - so you should post a bug report with sample data.
Other thought I just realized you can still use ST_Collect by doing the following INSERT INTO newtable(stusps, the_geom) SELECT stusps, ST_Multi(ST_Collect(f.the_geom)) as singlegeom FROM (SELECT stusps, (ST_Dump(the_geom)).geom As the_geom FROM somestatetable ) As f GROUP BY stusps ST_Collect is less likely to cause any strange abberations since its just collecting the geometries. Dump will dump out each POLYGON in the MULTIPOLYGON set. Note that dump returns a struct of the form (path, geom) -- so that's the reason I have that funky (ST_Dump(the_geom)).geom Since we really don't need the path. Hope that helps, Regina -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of David Jantzen Sent: Sunday, June 22, 2008 8:21 PM To: PostGIS Users Discussion Subject: RE: [postgis-users] Normalizing nationalatlas.gov data Okay, well that definitely gets me closer. Unioning creates single multipolygons for each state. However, for some reason some details are missing. For example, Puget Sound is gone, as are some of Great Lake borders. Any idea why that would be? Is data loss a known risk of unioning? On Sun, 2008-06-22 at 17:06 -0400, Paragon Corporation wrote: > 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 _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ----------------------------------------- The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer. _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
