[postgis-users] Normalizing nationalatlas.gov data

Obe, Regina robe.dnd at cityofboston.gov
Mon Jun 23 05:37:09 PDT 2008


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.




More information about the postgis-users mailing list