[postgis-users] Normalizing nationalatlas.gov data

[David Jantzen]

Wow, that one did it.  The data is correct and the query returns in a
couple seconds.  The query with ST_Union ran for almost 10 minutes so I
have no idea what it was doing.

I'll try to put together some repro steps with ST_Union and submit a
bug.  

Thanks for your help Regina.

David

On Mon, 2008-06-23 at 08:37 -0400, Obe, Regina wrote:
> David,
> 
> Union shouldn't cause any of those problems.  If you are sure they have
> states demarcated in them, then could be a bug in ST_Union - so you
> should post a bug report with sample data.
> 
> Other thought I just realized you can still use ST_Collect by doing the
> following
> 
>  
>   INSERT INTO newtable(stusps, the_geom) SELECT stusps, 
>   ST_Multi(ST_Collect(f.the_geom)) as singlegeom  
> FROM (SELECT stusps, (ST_Dump(the_geom)).geom As the_geom FROM
> somestatetable ) As f
>   GROUP BY stusps
> 
> 
> ST_Collect is less likely to cause any strange abberations since its
> just collecting the geometries.  Dump will dump out each POLYGON in the
> MULTIPOLYGON set.
> 
> Note that dump returns a struct of the form (path, geom) -- so that's
> the reason I have that funky (ST_Dump(the_geom)).geom
> 
> Since we really don't need the path.
> 
> 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: Sunday, June 22, 2008 8:21 PM
> To: PostGIS Users Discussion
> Subject: RE: [postgis-users] Normalizing nationalatlas.gov data
> 
> 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
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> -----------------------------------------
> The substance of this message, including any attachments, may be
> confidential, legally privileged and/or exempt from disclosure
> pursuant to Massachusetts law. It is intended
> solely for the addressee. If you received this in error, please
> contact the sender and delete the material from any computer.
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users