Setting up a geospatial database

Setting up PostgreSQL/PostGIS

1. Install database packages

   sudo apt-get install postgresql postgresql-client postgresql-contrib
   sudo apt-get install pgadmin3
   sudo apt-get install postgresql-8.4-postgis
   

2. Set a password for the system user postgres

   sudo passwd postgres
   

3. Set a password for the PostgreSQL user postgres

   sudo -u postgres psql postgres 
   \password postgres
   \q
   

4. Modify PostgreSQL access policy to use md5

   su - postgres
   vim /etc/postgresql/8.4/main/pg_hba.conf
   Modify "local, all, all, ident" line to "local, all, all, md5"
   exit
   sudo service postgresql restart
   

5. Create a DB user to work with the PostGIS database

   sudo -u postgres createuser -d -P dbuser
   

6. Create a spatial database template

   su - postgres
   createdb -E UTF8 template_postgis
   createlang -d template_postgis plpgsql 
   psql -d template_postgis -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
   psql -d template_postgis -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql	
   exit
   

7. Assign permissions to the template

   su - postgres
   psql -d template_postgis
   ALTER TABLE geometry_columns OWNER TO dbuser;
   ALTER TABLE spatial_ref_sys OWNER TO dbuser;
   \q
   exit
   

8. Create a spatial database

   sudo -u postgres createdb -O dbuser -T template_postgis dbname
   

Install GeoDjango requirements

1. Install Python and PostgreSQL C++ libraries

   sudo apt-get install python-dev libpq-dev
   

2. Install GEOS library

   wget http://download.osgeo.org/geos/geos-3.3.1.tar.bz2
   tar xjf geos-3.3.1.tar.bz2
   cd geos-3.3.1
   ./configure
   make
   sudo make install
   cd ..
   

3. Install PROJ.4 library:

   wget http://download.osgeo.org/proj/proj-4.7.0.tar.gz
   wget http://download.osgeo.org/proj/proj-datumgrid-1.5.zip
   tar xzf proj-4.7.0.tar.gz
   cd proj-4.7.0/nad
   unzip ../../proj-datumgrid-1.5.zip
   cd ..
   ./configure
   make
   sudo make install
   cd ..
   

4. Install GDAL library

   wget http://download.osgeo.org/gdal/gdal-1.8.1.tar.gz
   tar xzf gdal-1.8.1.tar.gz
   cd gdal-1.8.1
   ./configure
   make
   sudo make install
   cd ..
   

5. Refresh linker settings

   sudo ldconfig
   

Backup and restore

It may be useful to perform backups of Transit Near Me data tables. If you are using a PostgreSQL/PostGIS database as created with the above procedure, the most efficient way to do this is using the pg_dump and pg_restore commands. To backup all TNM-related tables:

pg_dump -Ft -t api_* -t gtfs_* -t transitapis_* -U dbuser dbname > archive.tar

This creates a binary archive (in .tar format) that can later be used to restore the database. If restoring into an empty, newly created database, you can do:

pg_restore -U dbuser -1 -d dbname archive.tar

Do this before running manage.py syncdb to create Django tables as described in the installation instructions.