Table of Contents for

Web Mapping Illustrated

You must have a PostgreSQL database service running before setting up PostGIS. PostgreSQL runs in a similar fashion to other enterprise database services (MySQL, Oracle, DB2, SQL Server). It waits for requests to come from a client program and then responds by interacting with databases on the server that the service is running on.

To test that your database service is running, you can use the command-line tool psql with the list parameter to give you a list of available databases:

> psql -l
           List of databases
      Name      |  Owner   | Encoding
----------------+----------+-----------
 template0      | postgres | SQL_ASCII
 template1      | postgres | SQL_ASCII
(2 rows)

This shows two available databases that are part of every PostgreSQL system. The template1 database is the default used as a template for creating a new database. It is copied, and all settings in it are made available to any new database you create. To enable PostGIS functionality for a database, you load PostGIS settings and functions into that database. If you load them into template1, all the PostGIS functionality will be available in every subsequent database you create. Having every database loaded with PostGIS capabilities isn’t always desirable but may be reasonable in some cases.

If you are running the database service on a different computer than the one on which you are running the psql command, you will have to tell psql to connect to that computer. This is done by specifying some further parameters when running the psql command. The most important parameter will be the host name of the computer that is running the service. This is used to direct the psql command to look for the service on that remote computer. The -h option is used, followed by the name of the computer.

> psql -l -h myserver.com

This lists the databases available on the host myserver.com. If a PostgreSQL service isn’t running or isn’t accepting connections from you, you will get an error:

psql: could not connect to server:
   Is the server running on host "myserver.com" and accepting TCP/IP connections on port
5432?

Depending on how your database was installed, you may also need to specify a database username. In the previous example of the psql command, you can see that the user postgres owns the template databases. If you want to connect to the service as the postgres user, you use the -U parameter followed by the username:

> psql -l -h myserver.com -U postgres

There are other parameters that can be added to the command, as you will see in the following examples.

PostgreSQL has support for internal programming languages that can interact and be embedded within a database. This is a powerful feature of PostgreSQL and is used by PostGIS. Its functions are programmed using the pl/pgsql language. This language isn’t supported by default and needs to be explicitly enabled by running the command:

> createlang plpgsql template1

Specify template1 as the target database to add the language support to.

With the language support enabled, you can now begin loading the PostGIS functions using the postgis.sql or lwpostgis.sql SQL script.

Again, the psql command is used to do this from the command line. Because postgis.sql is a file on the system, you need to know the path to the file or change directories so that it is in the same directory from which you run the command:

> psql -f postgis.sql -d template1

The first parameter specifies the file to load. The second parameter specifies which database to run the SQL commands in. Loading them into template1 makes the resulting database functions available in the new database that will be created.

Information notices and warnings scroll up the screen while this script is being loaded. There are warnings you can ignore, and several status statements acknowledging commands that ran successfully. An all-out failure of the script causes it to halt and provide a daunting error report similar to:

...
psql:postgis.sql:37: ERROR:  function "histogram2d_in" already exists with same argument
types
psql:postgis.sql:42: ERROR:  current transaction is aborted, commands ignored until end
of transaction block
...

These errors are saying that part of PostGIS has already been loaded into this database.

If the command runs properly, there will be many new functions, datatypes, and other settings in the template1 database. There will also be a table called geometry_columns, and another called spatial_ref_sys. You will check this in a moment, but first you need to load one more script.

The other script is spatial_ref_sys.sql, which populates the spatial_ref_sys table with projection-related information. PostGIS has reprojection (or spatial reference system transformation) capabilities and relies on the data in this table.

You load this script the same way as the postgis.sql script:

> psql -f spatial_ref_sys.sql -d template1

The script inserts over 1,700 records into the spatial_ref_sys table. While this script is running, you will see several INSERT messages, followed by a number on your screen looking something like:

...
INSERT 797517 1
INSERT 797518 1
INSERT 797519 1
INSERT 797520 1
COMMIT

The final COMMIT and no error messages confirms that the table was populated successfully.

You will get error messages if you haven’t already run postgis.sql/lwpostgis.sql or if you have already loaded spatial_ref_sys.sql.

Before creating your first database, it is worthwhile to test the template1 database to make sure everything is installed properly. Because template1 is used to create your new databases, checking before you create them makes fixes and clean-up easier.

Some simple tests can be done using the psql command to connect to the template1 database. The psql command lists databases and runs scripts, as shown earlier, but it also provides an interface for typing in SQL commands. The program it launches allows interactive typing of SQL commands and shows query results. To do this, you start psql followed by the name of a database to connect to, in this case, template1:

               > psql template1
Welcome to psql 7.4.2, the PostgreSQL interactive terminal.
 
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit
 
template1=#

When psql starts up, it tells you the version of the program, gives you a few lines of helpful tips for using the program, and then leaves you with a prompt. The prompt is the name of the connected database followed by =#.

The program is now ready for some SQL (or psql specific) commands. The first test checks to see that the expected PostGIS-related tables have been created. To list all the main objects in the database, type \d or use \dt to list just the tables (t):

template1=# \dt
             List of relations
 Schema |       Name       | Type  | Owner
--------+------------------+-------+---------
 public | geometry_columns | table | postgres
 public | spatial_ref_sys  | table | postgres
(2 rows)

The first script you ran created these tables. If they don’t exist, the script didn’t run properly.

If the spatial_ref_sys.sql script ran properly, there should be records in the spatial_ref_sys table. To check this, you can use an SQL command that counts the number of rows in the table:

template1=# SELECT count(*) FROM spatial_ref_sys;
 count
-------
  1785
(1 row)

Don’t be confused by the message: (1 row). This just describes the number of lines in the report output. The actual result of the query is 1785, which tells you the table isn’t empty. This looks good. Newer versions of PostGIS will show more entries in this table; for example, v1.0 has 2671 entries.

> grep -c INSERT spatial_ref_sys.sql1785

The spatial functions added to the database are a large part of PostGIS. To do some further checks, peek at a list of the functions available in template1 to ensure they were loaded properly. Using \df describes all the functions in the database. Supplying a wildcard to search will help show only functions that start with the name postgis. PostGIS functions don’t all start with the word postgis, but this is a simple way to check that the database has some PostGIS-related functions properly loaded. Newer versions of PostGIS will show different results.

template1=# \df postgis*
                 List of functions
 Result data type | Schema |          Name           |
------------------+--------+-------------------------+
 double precision | public | postgis_gist_sel        |
 text             | public | postgis_version         |
 opaque           | public | postgisgistcostestimate |
(3 rows)

The function names are listed in the right column under Name. If one of these functions can be run, PostGIS has probably been installed properly. The postgis_version function is a good one to run as a test. It returns information about the PostGIS installation that is available:

template1=# SELECT postgis_version();
            postgis_version
---------------------------------------
 0.8 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)

The output tells what version of PostGIS is being used (0.8). It also shows that it can use the GEOS and PROJ libraries as part of PostGIS. These supporting libraries are used by certain PostGIS functions. Proj libraries are used to reproject or transform coordinates, and GEOS is an advanced geometry engine that allows a whole suite of manipulations and analysis of geometry data types.

To learn more about GEOS, including installation instructions, see the GEOS web site at http://geos.refractions.net/.