[postgis-users] Automatically updating postgis from a directory of shapefiles

Mark Volz MarkVolz at co.lyon.mn.us
Thu Nov 29 06:38:41 PST 2012


To clarify my goal, I want to have postgis mirror a copy all of the shapefiles in a specified directory.  So if there is a brand new shapefile with a unique name, I want that shapefile to be added to postgis.  On the other hand if the gis layer already exists in postgis, I want to remove the old table from postgis, and replace it with a fresh copy from the shapefile directory.  So it sounds like the -d option should work for either situation.

What might be even better is that if I could set up a query so that all the tables are removed from the database (except the spatial reference table) , then have all the shapefiles reloaded.   This way If a file is removed from the shapefile directory, then the table will be removed from postgis automatically.  But something that fancy can wait.

You also mentioned that I could load my data into postgis without creating an intermidiant .sql file.  How would I change the following code to do that?

for %%f in (*.shp) do shp2pgsql -d -I -s 103749 -W LATIN1 %%f %%~nf > %%~nf.sql
for %%f in (*.sql) do psql -p 5432 -w -U postgres --dbname postgis20 -f %%f

> Hi,
> -d option will try to drop the table, will display an error message if it
> can't, but after that, table is created and populated.
> The drop is done outside the main creation/insertion transaction.
> So if you do not depend on ERROR message generated by your script, it
> should be fine to always call shp2pgsql with the -d option.
> (by the way, you really want to generate .sql files on your disk ? If you
> have a lot of shapefiles and/or big shapefiles, your script will generate
> potentially big sql files.
> you can pipe the output of shp2pgsql to psql to avoid this step)
> By updating, you mean erase and replace ? or update existing tables by
> appending data if needed ?
> Nicolas
> On 28 November 2012 16:53, Mark Volz <MarkVolz at co.lyon.mn.us> wrote:
> > Hello
> >
> > I would like to set up an windows batch file that will automatically
> > update PostGIS from a directory of shapefiles.  I have a couple potential
> > code samples, and would appreciate comments.   Thanks
> >
> > Example 1  - This code will create new tables in postgis from tables,
> > however it doesn't seem to replace existing tables with existing shapefiles:
> > for %%f in (*.shp) do shp2pgsql -c -I -s 103749 -W LATIN1 %%f %%~nf >
> > %%~nf.sql
> > for %%f in (*.sql) do psql -p 5432 -w -U postgres --dbname postgis20 -f %%f
> >
> > Example 2 - This code will replace existing tables, however it doesn't
> > seem to create new tables.
> > for %%f in (*.shp) do shp2pgsql -d -I -s 103749 -W LATIN1 %%f %%~nf >
> > %%~nf.sql
> > for %%f in (*.sql) do psql -p 5432 -w -U postgres --dbname postgis20 -f %%f
> >
> > Example 3 - I also came across this code as well that will delete all the
> > tables in a database,  which then I could turn around and use shp2pgsql
> > with the -c option.  However I don't want it to take down the spatial
> > reference table:
> > psql  -U postgres -t -d postgis20 -c "SELECT 'DROP TABLE ' || n.nspname ||
> > '.' || c.relname || ' CASCADE;' FROM pg_catalog.pg_class AS c LEFT JOIN
> > pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace WHERE relkind
> = 'r'
> > AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
> > pg_catalog.pg_table_is_visible(c.oid)" >/tmp/droptables
> > psql  -d postgis20 -f /tmp/droptables
> >
> > Questions:
> > 1)  Is there a way to protect the spatial reference table so that it would
> > drop when using psql?
> > 2)  Are my Assumptions about the -c and -d flag correct?   Where -c will
> > work only if the table is new, and -d will work only if the table already
> > exists?
> > 3)  Does anyone have any automatic update scripts?
> >
> > Thank You
> >
> > Mark Volz
> > GIS Specialist
> ------------------------------

More information about the postgis-users mailing list