[mapserver-users] Using PostGIS with MapServer

[Paul Ramsey]

Here is a brief description of a LAYER entry using PostGIS
(http://postgis.refractions.net) as a spatial data source:

PostGIS provides a SQL interface to large sets of spatial data. The
'CONNECTIONTYPE' is 'postgis'. The spatial data is stored in tables as a
column alongside the attribute data. Correspondingly, for MapServer to
build a PostGIS layer, it needs to know:

- What database to connect to (CONNECTION)
- While table to get the data from (DATA)
- Which column in the table holds the spatial objects (DATA)
- Any extra filtering information used to subset the data to be
displayed (FILTER)

The database connection is governed by the a 'connection string' which
is a standard set of keys and values like this (with the default values
in <>):

"user=<username> password=<password> dbname=<username> host=<localhost>
port=<5432>"

An empty connection string is still valid, and any of the key/value
pairs can be omitted. At a minimum you will generall supply the database
name and username to connect with.

The connection string goes in the 'CONNECTION' parameter of the LAYER
object:

CONNECTION "user=dbuser dbname=gisdatabase"

A complete SQL query which pulls a windowed subset of spatial data from
a PostGIS database might look like this:

a)   SELECT 
b)   geom FROM roads 
c)   WHERE 
d)   geom && 'BOX3D(1423141 1512324 1513214 1652151)'::box3d 
e)   AND
f)   type = 'highway'

When Mapserver pulls data from the database, it constructs this kind of
query. Parts a, c and e are just syntactical. Part d is derived from the
current extents automatically. Parts b and f are supplied in the map
file, in the 'DATA' and 'FILTER' lines of the layer definition
repectively. The example below gives a complete layer, with filtering
and expressions: note that the expressions in the CLASS objects do *not*
use SQL logical syntax, they use Mapserver's internal syntax for
describing logical comparisons.

    LAYER
      CONNECTIONTYPE postgis
      NAME "widehighways"
      # Connect to a remote spatial database
      CONNECTION "user=dbuser dbname=gisdatabase host=bigserver"
      # Get the lines from the 'geom' column of the 'roads' table
      DATA "geom from roads"
      STATUS ON
      TYPE LINE
      # Of the lines in the extents, only render the wide highways
      FILTER "type = 'highway' and numlanes >= 4"
      CLASS
        # Make the superhighways brighter and 2 pixels wide
        EXPRESSION ([numlanes] >= 6)
        COLOR 255 22 22      
        SYMBOL "solid"
        SIZE 2
      END
      CLASS
        # All the rest are darker and only 1 pixel wide
        EXPRESSION ([numlanes] < 6)
        COLOR 205 92 82      
      END
    END

-- 
      __
     /
     | Paul Ramsey
     | Refractions Research
     | Email: pramsey at refractions.net
     | Phone: (250) 885-0632
     \_