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

[Nicolas Ribot]

Hi,

Yes, -d option will alllow you to do that.

Regarding the second point, will the database contain only tables from
Shapefiles from the directory, or also other kind of tables ?
In the first case, you could drop the database at the beginning of the
script, recreate it, install PostGIS extension and then load the tables.
You could also, even better, store data from shapefiles in a schema
dedicated for that, then drop/recreate the schema each time you want to
sync DB and Shapefile folder.

Concerning the last point, pipe the 2 commands with the pipe (|) operator:

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

(I deliberately removed default options, like the 5432 port)

Nicolas


On 29 November 2012 15:38, Mark Volz <MarkVolz at co.lyon.mn.us> wrote:

> Nicolas,
>
>
> 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
> > ------------------------------
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20121129/a6ff6f80/attachment.html>