PostGIS Support in MapServer

---

The official PostGIS MapServer page is at: ​http://www.mapserver.org/input/vector/postgis.html

---

What is PostGIS?

PostgreSQL is a robust, fully ACID compliant, relational database management system. It is especially useful in high volume environments and has other features that make it ideal for use in science applications. PostgreSQL has a long and venerable history. It was originally developed in 1986 at the University of California, Berkeley as a research prototype, and is now distributed worldwide as open source software.

For working with MapServer, PostgreSQL requires an extension called PostGIS witch implements the Open GIS Consortium SQL object model (​http://www.opengis.org/docs/99-049.pdf). For more information about PostGIS you can visit its home page ​http://postgis.refractions.net/

Compiling

To use MapServer with PostGIS you have to add the --with-postgis switch on the configure script line.

Creating a spatial database

Asumming the server is correctly installed, configured and programs such as psql,createdb... are accesibles on the path, now is time to create a spatial database.

• Open a shell (for example: cmd.exe or xterm)
• Create the database. Run:

            createdb dbname
  

• Allow the usage of PL pgSQL (used by PostGIS)

       createlang plpgsql dbname
  

• Now, add the GIS extensions to the database. You must locate the files postgis.sql and spatial_ref_sys.sql. They should be on <pgSQL_install_dir>share/contrib/postgis or <pgSQL_install_dir>share/contrib directories.

            cd share/contrib
            psql -d dbname -f postgis.sql
            psql -d dbname -f spatial_ref_sys.sql
  

• In the PostGIS package there are two programs that allow us convert SHP files to SQL Statements and SQL statements to SHP. This programs are called shp2pgsql and pgsql2shp respectivelly. For example, to add a SHP file to a table we would have to run this two commands:

            shp2pgsql fichero.shp table_name > file.sql
            psql -U username -d dbname -f file.sql
  

To add a Layer that uses a PostGIS table on our MapFile we would have to add on the layer object these lines:

      DATA "the_geom from table_name"
      CONNECTION "user=username password=guess dbname=dbname host=localhost port=5432"
      CONNECTIONTYPE postgis

NOTICE: The SQL keyword "from" has to be written in small case letters!

This last step is fully explained in the MapFile Reference Documentation: ​http://www.mapserver.org/mapfile/layer.html)

You have more extended help on PostGIS documentation about MapServer: ​http://postgis.refractions.net/documentation/

Possible Problems / Debugging

For basic debugging of the MapServer - PostgreSQL - PostGIS connection, Paul Ramsey offers the following sequential checklist:

• Can you ping the IP address of the host you want to connect to? (If no, you have physical network or routing problems.) Yes?

• Can you ping the named address of the host you want to connect to? (If no, you have name resolution problems.) Yes?

• Can you connect to the service port you want directly? (In the case of PgSQL, telnet <yourhost> 5432"). (If no, your service is not listening on the port. This is the first PgSQL specific possibility. You might have to go into your postgres.conf file and set "tcpip_socket = yes", then restart the database. You should also check your pg_hba.conf file, which has host and user based access controls. The simplest setup is to add a trust line for a trusted subnetwork. If your connections are all local, just ensure that localhost (127.0.0.1) is trusted.). Yes?

• Can you connect to the PgSQL database as the "postgres" user with the "psql" commandline tool ("psql -h yourhost -U postgres template1")? (If no, check that your database is actually running. Check your pg_hba.conf file again for a trust relationship to the host you are connecting from) Yes?

• Can you connect to the PgSQL template1 database as yourself? (If no, you might not have created yourself as a user yet. As postgres, 'CREATE USER yourname CREATEDB CREATEUSER' to make yourself a superuser.) Yes?

• Can you connect to your PgSQL working database as yourself? (If no, have you created a working database? "CREATE DATABASE yourdb") Yes?

• Can you select data out of geometry tables from your working database as yourself? (If no, as postgres GRANT yourself the ability to SELECT). Yes?

• Does your version of mapserver include support for PostGIS at all? Run "mapserv -v" at the commandline and look for "INPUT=POSTGIS". If it's not there, go back and recompile your mapserver binary with --enable-postgis (or --with-postgis=/<path to pg_config> as specified in your version's install docs). Yes?

• Set up your mapserver connection string, check that the parameters you are providing in the connection string work in psql. "user=you dbname=thedb host=thehost port=5432" translates to "psql -h thehost -U you -p 5432 thedb". yes?

If problems continue, ask on the MapServer users list :)