Hi,<div><br></div><div>Yes, -d option will alllow you to do that.</div><div><br></div><div>Regarding the second point, will the database contain only tables from Shapefiles from the directory, or also other kind of tables ?</div>
<div>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.</div><div>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.</div>
<div><br></div><div>Concerning the last point, pipe the 2 commands with the pipe (|) operator:</div><div><br></div><div><span style="color:rgb(80,0,80);font-family:arial,sans-serif;font-size:13px">for %%f in (*.shp) do shp2pgsql -d -I -s 103749 -W LATIN1 %%f %%~nf | </span><span style="color:rgb(80,0,80);font-family:arial,sans-serif;font-size:13px">psql -w -U postgres -d postgis20 </span><br style="color:rgb(80,0,80);font-family:arial,sans-serif;font-size:13px">
</div><div><span style="color:rgb(80,0,80);font-family:arial,sans-serif;font-size:13px"><br></span></div><div><span style="color:rgb(80,0,80);font-family:arial,sans-serif;font-size:13px">(I deliberately removed default options, like the 5432 port)</span></div>
<div><span style="color:rgb(80,0,80);font-family:arial,sans-serif;font-size:13px"><br></span></div><div><span style="color:rgb(80,0,80);font-family:arial,sans-serif;font-size:13px">Nicolas</span></div><div class="gmail_extra">
<br><br><div class="gmail_quote">On 29 November 2012 15:38, Mark Volz <span dir="ltr"><<a href="mailto:MarkVolz@co.lyon.mn.us" target="_blank">MarkVolz@co.lyon.mn.us</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Nicolas,<br>
<br>
<br>
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.<br>
<br>
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.<br>
<br>
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?<br>
<div><div class="h5"><br>
for %%f in (*.shp) do shp2pgsql -d -I -s 103749 -W LATIN1 %%f %%~nf > %%~nf.sql<br>
for %%f in (*.sql) do psql -p 5432 -w -U postgres --dbname postgis20 -f %%f<br>
<br>
<br>
> Hi,<br>
><br>
> -d option will try to drop the table, will display an error message if it<br>
> can't, but after that, table is created and populated.<br>
> The drop is done outside the main creation/insertion transaction.<br>
><br>
> So if you do not depend on ERROR message generated by your script, it<br>
> should be fine to always call shp2pgsql with the -d option.<br>
><br>
> (by the way, you really want to generate .sql files on your disk ? If you<br>
> have a lot of shapefiles and/or big shapefiles, your script will generate<br>
> potentially big sql files.<br>
> you can pipe the output of shp2pgsql to psql to avoid this step)<br>
><br>
> By updating, you mean erase and replace ? or update existing tables by<br>
> appending data if needed ?<br>
><br>
> Nicolas<br>
><br>
><br>
> On 28 November 2012 16:53, Mark Volz <<a href="mailto:MarkVolz@co.lyon.mn.us">MarkVolz@co.lyon.mn.us</a>> wrote:<br>
><br>
> > Hello<br>
> ><br>
> > I would like to set up an windows batch file that will automatically<br>
> > update PostGIS from a directory of shapefiles. I have a couple potential<br>
> > code samples, and would appreciate comments. Thanks<br>
> ><br>
> > Example 1 - This code will create new tables in postgis from tables,<br>
> > however it doesn't seem to replace existing tables with existing shapefiles:<br>
> > for %%f in (*.shp) do shp2pgsql -c -I -s 103749 -W LATIN1 %%f %%~nf ><br>
> > %%~nf.sql<br>
> > for %%f in (*.sql) do psql -p 5432 -w -U postgres --dbname postgis20 -f %%f<br>
> ><br>
> > Example 2 - This code will replace existing tables, however it doesn't<br>
> > seem to create new tables.<br>
> > for %%f in (*.shp) do shp2pgsql -d -I -s 103749 -W LATIN1 %%f %%~nf ><br>
> > %%~nf.sql<br>
> > for %%f in (*.sql) do psql -p 5432 -w -U postgres --dbname postgis20 -f %%f<br>
> ><br>
> > Example 3 - I also came across this code as well that will delete all the<br>
> > tables in a database, which then I could turn around and use shp2pgsql<br>
> > with the -c option. However I don't want it to take down the spatial<br>
> > reference table:<br>
> > psql -U postgres -t -d postgis20 -c "SELECT 'DROP TABLE ' || n.nspname ||<br>
> > '.' || c.relname || ' CASCADE;' FROM pg_catalog.pg_class AS c LEFT JOIN<br>
> > pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace WHERE relkind<br>
> = 'r'<br>
> > AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND<br>
> > pg_catalog.pg_table_is_visible(c.oid)" >/tmp/droptables<br>
> > psql -d postgis20 -f /tmp/droptables<br>
> ><br>
> > Questions:<br>
> > 1) Is there a way to protect the spatial reference table so that it would<br>
> > drop when using psql?<br>
> > 2) Are my Assumptions about the -c and -d flag correct? Where -c will<br>
> > work only if the table is new, and -d will work only if the table already<br>
> > exists?<br>
> > 3) Does anyone have any automatic update scripts?<br>
> ><br>
> > Thank You<br>
> ><br>
> > Mark Volz<br>
> > GIS Specialist<br>
</div></div>> ------------------------------<br>
<div class="HOEnZb"><div class="h5">_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
</div></div></blockquote></div><br></div>