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

[Nicolas Ribot]

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/20121128/809e7a37/attachment.html>