To use PostGIS with Mapserver, you will need to know about how to configure Mapserver, which is beyond the scope of this documentation. This section will cover specific PostGIS issues and configuration details.

To use PostGIS with Mapserver, you will need:

Mapserver accesses PostGIS/PostgreSQL data like any other PostgreSQL client -- using libpq. This means that Mapserver can be installed on any machine with network access to the PostGIS server, as long as the system has the libpq PostgreSQL client libraries.

The USING pseudo-SQL clause is used to add some information to help mapserver understand the results of more complex queries. More specifically, when either a view or a subselect is used as the source table (the thing to the right of "FROM" in a DATA definition) it is more difficult for mapserver to automatically determine a unique identifier for each row and also the SRID for the table. The USING clause can provide mapserver with these two pieces of information as follows:

DATA "the_geom FROM (
  SELECT 
    table1.the_geom AS the_geom, 
    table1.oid AS oid, 
    table2.data AS data 
  FROM table1 
  LEFT JOIN table2 
  ON table1.id = table2.id
) AS new_table USING UNIQUE oid USING SRID=-1"

	The parser for Mapserver PostGIS layers is fairly primitive, and is case sensitive in a few areas. Be careful to ensure that all SQL keywords and all your USING clauses are in upper case, and that your USING UNIQUE clause precedes your USING SRID clause.

Lets start with a simple example and work our way up. Consider the following Mapserver layer definition:

LAYER 
  CONNECTIONTYPE postgis 
  NAME "roads"
  CONNECTION "user=theuser password=thepass dbname=thedb host=theserver" 
  DATA "the_geom FROM roads" 
  STATUS ON 
  TYPE LINE 
  CLASS 
    STYLE
      COLOR 0 0 0 
    END
  END 
END

This layer will display all the road geometries in the roads table as black lines.

Now lets say we want to show only the highways until we get zoomed in to at least a 1:100000 scale - the next two layers will achieve this effect:

LAYER 
  CONNECTIONTYPE postgis 
  CONNECTION "user=theuser password=thepass dbname=thedb host=theserver" 
  PROCESSING "CLOSE_CONNECTION=DEFER"
  DATA "the_geom FROM roads"
  MINSCALE 100000 
  STATUS ON 
  TYPE LINE 
  FILTER "road_type = 'highway'" 
  CLASS 
    COLOR 0 0 0 
  END 
END 
LAYER 
  CONNECTIONTYPE postgis 
  CONNECTION "user=theuser password=thepass dbname=thedb host=theserver"
  PROCESSING "CLOSE_CONNECTION=DEFER"
  DATA "the_geom FROM roads" 
  MAXSCALE 100000 
  STATUS ON 
  TYPE LINE
  CLASSITEM road_type 
  CLASS 
    EXPRESSION "highway" 
    STYLE
      WIDTH 2 
      COLOR 255 0 0  
    END
  END 
  CLASS  
    STYLE
      COLOR 0 0 0 
    END
  END 
END

The first layer is used when the scale is greater than 1:100000, and displays only the roads of type "highway" as black lines. The FILTER option causes only roads of type "highway" to be displayed.

The second layer is used when the scale is less than 1:100000, and will display highways as double-thick red lines, and other roads as regular black lines.

So, we have done a couple of interesting things using only mapserver functionality, but our DATA SQL statement has remained simple. Suppose that the name of the road is stored in another table (for whatever reason) and we need to do a join to get it and label our roads.

LAYER 
  CONNECTIONTYPE postgis
  CONNECTION "user=theuser password=thepass dbname=thedb host=theserver" 
  DATA "the_geom FROM (SELECT roads.oid AS oid, roads.the_geom AS the_geom, 
        road_names.name as name FROM roads LEFT JOIN road_names ON 
        roads.road_name_id = road_names.road_name_id) 
        AS named_roads USING UNIQUE oid USING SRID=-1" 
  MAXSCALE 20000 
  STATUS ON 
  TYPE ANNOTATION 
  LABELITEM name
  CLASS 
    LABEL 
      ANGLE auto 
      SIZE 8 
      COLOR 0 192 0 
      TYPE truetype 
      FONT arial
    END
  END 
END

This annotation layer adds green labels to all the roads when the scale gets down to 1:20000 or less. It also demonstrates how to use an SQL join in a DATA definition.

...

...