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.
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
for f in *.shp do shp2pgsql -s 4326 %f public.'basename $f .shp' > 'basename $f .shp'.sql | psql -h myserver -d mydb -U myuser done
> redirect part, as this breaks the | pipe part), as long as you are sure the SQL passed to psql is correct. I'd argue that this is not better, since there is no record of the data in SQL format.
If want to stick to a GUI then the newer version of pgAdmin has Shapefile Loader that can be used as a bulk load
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
You might also want to look at SPIT, which is a PostGIS loader plugin for QGIS
A bit late to the party, but this was the first result in a google search for this question.
I'm not sure how you've been able to do this, but you can now just drag any shapefiles you have loaded in QGIS (from the Layers panel) to whichever schema you want to load them to (in the Browser panel). You can do one or many.
