[postgis-users] Normalizing nationalatlas.gov data

[Paragon Corporation]

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: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] 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 at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users