[postgis-users] Normalizing nationalatlas.gov data

[Paragon Corporation]

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

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users