[postgis-users] Loading Large Datasets

[Randy George]

Hi Randy,

 

            We have loaded the TIGER 2000 dataset including all features
without a problem. We did divide the features into separate tables so that
our svg client viewer can selectively display layers. We ended up not using
the shp2sql translator because we preferred to reduce size by eliminating
unnecessary left/right attribute fields. Instead of using the TIGER
attributes to build polygons on the fly we also created separate polygon
tables with precompiled polygon geometry. The performance has been very good
for our GIS application, mostly polygon intersect and buffer queries so far.
We are adding thematic view capability tying the polygons to sf1/sf3
demographic data. Svg/xml allows us to build views with just a browser on
the client side.

 

            Our TIGER translator created PG copy ascii files(same approach
as shp2sql). We created one file for all states for each feature type for
example water or road. After loading this file we then ran the GIST index
and whatever other index we needed on the whole table. Waiting until
complete to index should save load time rather than loading by state.

 

Hope this helps

Randy

 

  _____  

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Randy
Page
Sent: Friday, July 09, 2004 7:10 AM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] Loading Large Datasets

 

Hi All,

 

I'm relatively new to PostGIS, but have a lot of experience with ArcSDE.
I'm getting ready to load the entire national set of Tiger data (roads,
tracts, block groups, etc.) into PostGIS.   I have a tool that allows me to
generate shape files either for the entire country or an entire state, so I
can either build a single large shape file for each feature, or start out
with smaller ones and append them to create a seamless coverage in PostGIS.
My goal is to have a single dataset for each feature type.   So, I have a
few questions for those of you who have experience loading large datasets.

 

1)  Are there any issues with loading a single large dataset such as
national coverage of roads (the SQL file is nearly 3GB?)   Is it preferable
to load the data in smaller batches maybe by state?

2)  I have been running a few tests, and have had no problems loading a few
individual counties.   However, I did try loading the entire national set of
streets using shp2pgsql and ran across a few problems while executing the
sql statement.   I found that about 1 in 3 blocks produced an error similar
to this:

 

NOTICE:  current transaction is aborted, queries ignored until end of
transaction block

 

I checked the sql statement, and it's using varchar for each data field, so
I don't think there is an issue with data types.   Any ideas?

 

Thanks in advance,

 

Randy

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20040709/44db7199/attachment.html>