[postgis-users] Normalizing nationalatlas.gov data

[David Jantzen]

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