Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
    
I'm looking for a simlar answer to this. However i want to make it a batch job that runs everynight. In effect i want to make my PostGIS a slave to ArcSDE (for the time being). Being new to PostGIS and SQL, i get what the cmd.exe script would do but somehow its not sticking in my head. What i want to do is get a series of shp files that have been exported from ArcSDE as a batch job, then upload these into my PostGIS, which would overwrite the existing gis/tables in place already. –  geosmiles Aug 1 '11 at 18:47
    
I'd ask this as a new question, along the lines of "how to batch synchronize data from ArcSDE to PostGIS." There could be some interesting ideas. –  Mike T Aug 1 '11 at 19:14
add comment

4 Answers

up vote 17 down vote accepted

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

enter image description here

share|improve this answer
add comment

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

share|improve this answer
1  
Thought I might repost the link since yours is dead : docs.qgis.org/1.8/html/en/docs/user_manual/plugins/… –  Akheloes Aug 20 '13 at 21:19
add comment

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
share|improve this answer
add comment

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

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
done
share|improve this answer
    
would this work better in a single loop, 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 –  Sam007 Nov 21 '12 at 23:47
    
yup, it can be done in one command (but without the > 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. –  Mike T Nov 22 '12 at 0:37
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.