"Seegrid will be due for a migration to confluence on the 1st of August. Any update on or after the 1st of August will NOT be migrated"

How to Setup a PostGIS Spatial Database

Contents

Introduction

This guide describes how to setup a PostGIS spatial database on Debian Etch. Other platforms should be similar.

Install PostGIS

PostGIS adds spatial types and functions to PostgreSQL. On Debian Etch, install these packages:
  • postgresql-8.1
  • postgresql-8.1-postgis
  • postgresql-contrib-8.1
  • postgis

Create PostGIS spatial database

Suppose we want to set up a spatial database called ${database} with a user called ${user}. Because some of these steps can only be performed by a PostgreSQL superuser, this is a slightly tricky process, as the special OpenGIS tables created in the database by the superuser have to be given to the ${user} user at the end of the process, because creating spatial tables requires write access to the geometry_columns table. Once the database has been configured as described below, the ${user} user can create spatial tables at will.
  • Login as root.
  • Switch to postgres so that we can connect to the database as superuser:
    • su postgres
  • Connect to the database server ("cluster"). The default ident authentication is used over this unix socket connection. The prompt should be postgres=# indicating that you are connected to the default postgres database and have superuser privileges.
    • psql
  • Create the ${database} database:
    • CREATE DATABASE ${database};
  • Connect to the ${database} database. The prompt should change to ${database}=# .
    • \c ${database}
  • Enable the PL/pgSQL language for this database. This is required because the spatial database script we will run contains PL/PGSQL commands.
    • CREATE LANGUAGE plpgsql;
  • Run lwpostgis.sql to load all the PostGIS geometry_columns. This step also loads C extensions, which only a superuser is permitted to do. This script creates the tables geometry_columns and spatial_ref_sys.
    • \i /usr/share/postgresql-8.1-postgis/lwpostgis.sql
  • Populate spatial_ref_sys with a large catalogue of spatial reference systems:
    • \i /usr/share/postgresql-8.1-postgis/spatial_ref_sys.sql
  • Now that the tasks that can only be performed by a superuser have been completed, we can create the ordinary user ${user} (allowed to login with ${password}) and give it ownership of the database and tables.
    • CREATE ROLE ${user} WITH LOGIN PASSWORD '${password}';
    • ALTER DATABASE ${database} OWNER TO ${user};
    • ALTER TABLE geometry_columns OWNER TO ${user};
    • ALTER TABLE spatial_ref_sys OWNER TO ${user};

Enable remote access (optional)

If you want to access PostgreSQL from a remote host:
  1. Enable remote access for this database/username to PostgreSQL with an entry in /etc/postgresql/8.1/main/pg_hba.conf such as
    • host ${database} ${user} ${addresses} md5
    • ${addresses} are the IP addresses permitted to connect, in CIDR xxx.xxx.xxx.xxx/yy notation, for example, 192.168.0.0/16 .
  2. Configure PostgreSQL to listen on all interfaces (not just localhost) by editing /etc/postgresql/8.1/main/postgresql.conf so that it contains
    • listen_addresses = '*'
  3. Restart PostgreSQL with
    • /etc/init.d/postgresql-8.1 restart

Test that the database is working (optional but recommended)

  • From a shell command line, connect to the database via TCP.
    • psql -h ${hostname} -d ${database} -U ${user} -W
    • You will be prompted for password.
    • To test remote access, use a remote host.
  • Create an ordinary table:
    • CREATE TABLE test_table (id INTEGER PRIMARY KEY, name VARCHAR(80) NOT NULL);
  • Add a spatial column to the table:_
    • SELECT AddGeometryColumn('test_table', 'geometry', 4326, 'POINT', 2);
  • Test insert (note: PostGIS uses lon-lat for EPSG:4326):
    • INSERT INTO test_table (id, name, geometry) VALUES (3, 'Example', GeomFromText('POINT(115.885999 -31.993491)', 4326));
  • Test select:
    • SELECT id, name, AsText(geometry) FROM test_table;
  • Remove the table:
    • DROP TABLE test_table;
  • Remove spatial metadata for test_table:
    • DELETE FROM geometry_columns WHERE f_table_name = 'test_table';


An alternative way to remove the table that has a geometry field is to do
  • SELECT DropGeometryColumn('test_table','geometry');
  • DROP TABLE test_table;

-- EricBoisvert - 27 Jul 2008

Thanks Eric. [I updated Eric's example to match the updated generic instructions.]

-- BenCaradocDavies - 15 Aug 2008

Spatial index

  • Before a geometry column can be used for spatial operations, it must have a spatial index.
  • GeoServer WMS always uses a spatial query (BBOX) and will fail to work if the source table has no spatial index.
  • To create a spatial index for the example above:
    • CREATE INDEX test_table_geometry_idx ON  test_table USING GIST(geometry);

How to Restore a PostGIS Spatial Database from dump file

This guide describes how to restore a PostGIS spatial database on Debian Etch from backup dump file. Other platforms should be similar. It is assumed that you already have a PostGIS database installed and a dump file ready.

Restore PostGIS spatial database

Suppose we want to set up a spatial database called ${database} with a user called ${user} and then to restore a dump file into it. Because some of these steps can only be performed by a PostgreSQL superuser, this is a slightly tricky process, as the special OpenGIS tables created in the database by the superuser have to be given to the ${user} user at the end of the process, because creating spatial tables requires write access to the geometry_columns table.

  • Login as root.
  • Upload a dump file into a temporary folder:
    • cd /usr/local/
    • mkdir ${database}
    • cd ${database}
    • wget --no-check-certificate ${url}
  • Switch to postgres so that we can connect to the database as superuser:
    • su postgres
  • Connect to the database server ("cluster"). The default ident authentication is used over this unix socket connection. The prompt should be postgres=# indicating that you are connected to the default postgres database and have superuser privileges.
    • psql
  • Create the ${database} database:
    • =CREATE DATABASE ${database} WITH ENCODING='UTF8' OWNER=${owner} TEMPLATE=template0;
  • Restore dump file into newly created database:
    • pg_restore -i -p 5432 -U ${user} -d ${database} -v "/usr/local/${database}/${dump_filename}"
  • Connect to the ${database} database. The prompt should change to ${database}=# .
    • \c ${database}
  • Check that database has been restored properly by checking that all tables exist:
    • \dt *.*

Enable remote access (optional)

If you want to access PostgreSQL from a remote host follow the instructions above.
Topic revision: r7 - 15 Oct 2010, UnknownUser
 

Current license: All material on this collaboration platform is licensed under a Creative Commons Attribution 3.0 Australia Licence (CC BY 3.0).