Open Source Computing and GIS in the UK

Travels in a digital world

Thursday Tip Day: Mass Loading Shapefiles Into Postgis

| Comments

From the postgis maestro himself comes a handy tip for mass loading shapefiles of identical schemas into postgis:

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