[postgis-users] Normalizing nationalatlas.gov data

[David Jantzen]

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: 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
> 
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users