[postgis-users] PostGIS command line options

[Brent Wood]

--- Leigh Durland <leighd at midwestsurveys.com> wrote:

> Hi all,
> 
>  
> 
>  Newbie here. My task is to load and append multiple shapefiles into
> PostGIS into one table once a day replacing the previous and then
> creating a WMS with the newly updated table. The loading part I can do
> through a shell script with probably very little problem. What I want to
> know is it possible to do the “SELECT postloading” and VACUUM ANALYZE
> from this same shell script that I have created?  Any code would be
> greatly appreciated.


Yes, though I'm not sure what SELECT you want to run postloading, or why.
If the table is the same structure, why do you need to change the SELECT, and
what do you want to change it to?

This assumes you can drop the old table then rebuild it, you may need to build
the new one, then drop the old one & rename the new one, depending on how long
it takes & any downtime issues you have.

>From memory, something along the lines of this (with shp2pgsql, note you could
also use ogr2ogr) might be suitable:

Cheers,

  Brent Wood


#!/bin/bash

# clean up old files & set variables
rm -f *.sql
COUNTER=0
DB=<database name>
MAPFILE=</.../..../.../data.map>


#loop through all the shapefiles generating the SQL files to load them
for SHP in `ls *.shp` ; do
  COUNTER=`expr $COUNTER + 1`
  if [ $COUNTER = 1 ] ; then
    # write the first SQL with -d to drop & rebuild the table
    # it would make sense to use -I to generate a PostGIS index here as well
    pgsql2shp -d $SHP -I geotable > load_${COUNTER}.sql
  else
    # write further sqls to append the data
    pgsql2shp -a $SHP geotable > load_${COUNTER}.sql
  fi
done


# loop through the new SQL files to load all the data
for SQL in `ls load*.sql` ; do
  psql -d $DB -f $SQL
done
 
# vacuum analyse the new table
psql -d $DB -c "vacuum analyse geotable;"

# to change a string in the mapfile, to do something like change a date
# get old date (this could be run to get the value from the old table, before 
# dropping & replacing it)

# for example, get max date - one day as the max date for the previous table
DATE0=`psql -d $DB -Atc "select max(timer)-interval '1 day'::date from
geotable;"
DATE1=`psql -d $DB -Atc "select max(timer)::date from geotable;"

cat $MAPFILE | sed 's/$DATE0/$DATE1/' > new.map
mv new.map $MAPFILE

# don't forget to sort out perms on the new mapfile if necessary
chmod ..... $MAPFILE