Have you tried something like 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
