13.5. Steps for Setting Up PostGIS

The PostGIS documentation is the best source for walking through all the steps of setting up PostGIS. This section provides a very brief run-through of steps. Depending on your skill and understanding of PostgreSQL and SQL scripts, these instructions may or may not be enough. If not, please refer to the main PostGIS documentation at http://postgis.refractions.net/docs.

Depending on the method you have used to get the PostgreSQL database set up and PostGIS installed, there will be different steps required to get started. Some installation packages include all the steps required to jump right into using PostGIS. Others don't, which means you still need to set up the final stages of PostGIS functionality. If you compile PostGIS from source code, you will always have the following steps to walk through. In a nutshell, these steps involve:

1. Getting PostgreSQL up and running

2. Enabling pl/pgsql language support

3. Loading the postgis.sql (or lwpostgis.sql) script

4. Loading the spatial_ref_sys.sql script

5. Creating a database

Other steps involving loading and accessing data are covered in later sections.

13.5.1. Getting PostgreSQL Up and Running

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.

13.5.2. Enabling pl/pgsql Language Support

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.

13.5.3. Loading the postgis.sql Script

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

SQL scripts, such as postgis.sql, are text files with a list of SQL commands that are used to interact with the database. If you are curious, you can open this file in a text editor or in a web browser and have a look at what commands are being used.

Later you will have to type in some SQL commands to test the database. We'll also use SQL to load geographic features into the database.

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.

13.5.4. Loading the spatial_ref_sys.sql 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.

Depending on your version of PostGIS, the final message may actually be VACUUM.

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.

13.5.5. Testing PostGIS Functionality

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)

To learn more about SQL commands, see the SQL entry in the WikiPedia at http://en.wikipedia.org/wiki/SQL.

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.

How do you know if it should be 1785 or something different? Just run a quick check from the operating system command line to see the number of times INSERT is used in spatial_ref_sys.sql:

 > 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/.