Mass Shape File Load into PostGIS

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)