Mass Shape File Load into PostGIS18 May 2007
I needed some test data to do some performance investigations, and had to load 235 shape files, all of identical schema. Here’s what I did.
First, get the table schema into the database, by loading a small file, and then deleting the data. We delete the data so we can loop through all the files later without worrying about duplicating the data from the initial file.
shp2pgsql -s 3005 -i -D lwssvict.shp lwss | psql mydatabase psql -c "delete from lwss" mydatabase
Then use the shell to loop through all the shape files and append them into the table.
foreach f (*.shp) foreach? shp2pgsql -s 3005 -i -D $f -a lwss | psql mydatabase end
Note the “-a” switch to tell
shp2pgsql we are in append mode, rather than the default create mode. Add a spatial index, and we’re done.
psql -c "create index lwss_gix on lwss using gist (the_geom)" mydatabase
Seven hundred thousand line segments, ready to play!
psql -c "select count(*) from lwss" mydatabase count -------- 755373 (1 row)