Bulk load multiple shapefiles into PostGIS

Question

I've got over a hundred data layers in shapefile format that I want to upload into a PostGIS database. They are all in the same projection, but represent different data layers so they do not have the same schema.

What is the most time-effective way to bulk convert all of these files into my PostGIS database (on windows 7 OS)?

UPDATE: Bulk importing in the pgAdmin3 "PostGIS shapefile uploader" (as noted below) is now available by default with PostGIS 2.0.

Answer 1

If you have a Windows computer, you can use good 'ol CMD.EXE with a few esoteric for-loops. Make sure you do this in a "contained" directory with only the shp/sql files that you need to load.

First step, create the SQL loader files (I also assumed you have Lat/Long WGS84 data with 4326 .. update this to your SRS):

for %f in (*shp) do shp2pgsql -s 4326 %f public.%~nf > %~nf.sql

Then check your SQL files to make sure they look good, then do a similar loop:

for %f in (*sql) do psql -h myserver -d mydb -U myuser -f %f > nul

---

The bash equivalent for POSIX folk (Linux, Mac OS X, etc) is something like:

for f in *.shp
do
    shp2pgsql -s 4326 %f public.`basename $f .shp` > `basename $f .shp`.sql
done

then

for f in *.sql
do
    psql -h myserver -d mydb -U myuser -f $f > /dev/null
done

or both parts piped into a single loop, if you don't need to keep the interim .sql files:

for f in *.shp
do
    shp2pgsql -s 4326 %f public.`basename $f .shp` | psql -d mydb > /dev/null
done

Answer 2

If want to stick to a GUI then the newer version of pgAdmin has Shapefile Loader that can be used as a bulk load

Answer 3

You can also use this single command which helps in looping much easier and also does not need to create .sql separately,

for f in *.shp
    do shp2pgsql -c -D -s 4326 -I $f public.${f%.*} | psql -h hostname -d dbname -U usrname
done

Answer 4

You might also want to look at SPIT, which is a PostGIS loader plugin for QGIS