.. _input_postgis:

*****************************************************************************
 PostGIS/PostgreSQL
*****************************************************************************

:Last Updated: 2014-11-06

.. contents:: Table of Contents
    :depth: 3
    :backlinks: top

PostGIS/PostgreSQL
------------------

`PostGIS <http://postgis.net/>`_ spatially enables the 
Open Source `PostgreSQL`_ database.

.. _`PostgreSQL`: http://www.postgresql.org/

The `PostGIS wiki page <https://github.com/mapserver/mapserver/wiki/PostGIS>`__ 
may include additional information.


Data Access /Connection Method
------------------------------

PostGIS is supported directly by MapServer and must be compiled into
MapServer to work.

The PostgreSQL client libraries (libpq.so or libpq.dll) must be
present in the system's path environment for functionality to be
present.

The CONNECTIONTYPE parameter must be set to POSTGIS.

The CONNECTION parameter is used to specify the parameters to connect
to the database.  CONNECTION parameters can be in any order.  Most are
optional.  dbname is required.  user is required.  host defaults to
localhost, port defaults to 5432 (the standard port for PostgreSQL).

The DATA parameter is used to specify the data used to draw the map.
The form of DATA is "[geometry_column] from [table_name|sql_subquery]
using unique [unique_key] using srid=[spatial_reference_id]".  The
"using unique" and "using srid=" clauses are optional when drawing
features, but using them improves performance. If you want to make
MapServer query calls to a PostGIS layer, your DATA parameter must
include "using unique". Omitting it will cause the query to fail.

Here is a simple generic example:

::
  
  CONNECTIONTYPE POSTGIS
  CONNECTION "host=yourhostname dbname=yourdatabasename user=yourdbusername
              password=yourdbpassword port=yourpgport"
  DATA "geometrycolumn from yourtablename"

This example shows specifying the unique key and srid in the DATA line:

::

  CONNECTIONTYPE POSTGIS
  CONNECTION "dbname=yourdatabasename user=yourdbusername"
  DATA "the_geom from the_database using unique gid using srid=4326"

This example shows using a SQL subquery to perform a join inside the
database and map the result in MapServer. Note the "as subquery"
string in the statement -- everything between "from" and "using" is
sent to the database for evaluation:

::

  CONNECTIONTYPE POSTGIS
  CONNECTION "dbname=yourdatabasename user=yourdbusername"
  DATA "the_geom from (select g.gid, g.the_geom, a.attr1, a.attr2 from
        geotable g join attrtable a on g.gid = a.aid) as subquery
        using unique gid using srid=4326"

This example shows using a geometry function and database sort to
limit the number of features and vertices returned to MapServer:
  
::

  CONNECTIONTYPE POSTGIS
  CONNECTION "dbname=yourdatabasename user=yourdbusername"
  DATA "the_geom from (select g.gid, ST_Simplify(g.the_geom, 10.0) as
        the_geom from geotable g order by ST_Area(g.the_geom) desc
        limit 10) as subquery using unique gid using srid=4326"

This example shows the use of the !BOX! substitution string to
over-ride the default inclusion of the map bounding box in the SQL. By
default the spatial box clause is appended to the SQL in the DATA
clause, but you can use !BOX! to insert it anywhere you like in the
statement. In general, you won't need to use !BOX!, because the
PostgreSQL planner will generate the optimal plan from the generated
SQL, but in some cases (complex sub-queries) a better plan can be
generated by placing the !BOX! closer to the middle of the query:

::

  CONNECTIONTYPE POSTGIS
  CONNECTION "dbname=yourdatabasename user=yourdbusername"
  DATA "the_geom from (select g.gid, ST_Union(g.the_geom, 10.0) as
        the_geom from geotable g where ST_Intersects(g.geom,!BOX!)) as
        subquery using unique gid using srid=4326"


OGRINFO Examples
----------------

OGRINFO can be used to read out metadata about PostGIS tables directly
from the database.

First you should make sure that your GDAL/OGR build contains the
PostgreSQL driver, by using the '--formats' command:

::

  >ogrinfo --formats
    Loaded OGR Format Drivers:
    ...
    -> "PGeo" (readonly)
    -> "PostgreSQL" (read/write)
    -> "MySQL" (read/write)
    ...

If you don't have the driver, you might want to try the `FWTools`_ or
`MS4W`_ packages, which include the driver.

.. _`FWTools`: http://fwtools.maptools.org
.. _`MS4W`: http://www.ms4w.com

Once you have the driver you are ready to try an ogrinfo command on
your database to get a list of spatial tables:

::

  >ogrinfo PG:"host=127.0.0.1 user=postgres password=postgres dbname=canada port=5432"
        using driver `PostgreSQL' successful.
        1: province (Multi Polygon)

Now use ogrinfo to get information on the structure of the spatial table:

::

  >ogrinfo PG:"host=127.0.0.1 user=postgres password=postgres dbname=canada port=5432"
               province -summary
   INFO: Open of `PG:host=127.0.0.1 user=postgres password=postgres dbname=canada'
      using driver `PostgreSQL' successful.

    Layer name: province
    Geometry: Multi Polygon
    Feature Count: 1068
    Extent: (-2340603.750000, -719746.062500) - (3009430.500000, 3836605.250000)
    Layer SRS WKT:
    (unknown)
    FID Column = gid
    Geometry Column = the_geom
    area: Real (0.0)
    island: String (30.0)
    island_e: String (30.0)
    island_f: String (30.0)
    name: String (30.0)
    ...

Mapfile Example
---------------

::

  LAYER
    NAME "province"
    STATUS ON
    TYPE POLYGON
    CONNECTIONTYPE POSTGIS
    CONNECTION "host=127.0.0.1 port=5432 dbname=canada user=postgres password=postgres"
    DATA "the_geom from province"
    CLASS
       ...
    END
  END

For more info about PostGIS and MapServer see the PostGIS docs:
http://postgis.net/documentation/

Support for 2.5D geometries
---------------------------

In addition to horizontal coordinates (X,Y or longitude,latitude), PostGIS can
support geometries with a vertical component, often called 2.5D geometries.

As of MapServer 7.0, such 2.5D geometries will be taken into account if MapServer
is built with -DWITH_POINT_Z_M=ON.

.. note:: 

    Output of 2.5D geometries in WFS requires explicit metadata item to be
    specified at the layer level. See :ref:`WFS server <wfs_server>` documentation.

.. note:: 

    It is still possible to force 2D only geometries to be retrieved from PostGIS by
    setting the following PROCESSING option

    .. code-block:: mapfile

            PROCESSING "FORCE2D=YES"


Support for SQL/MM Curves
-------------------------

PostGIS is able to store circular interpolated curves, as part of the
SQL Multimedia Applications Spatial specification (read about the
`SQL/MM specification
<http://subs.emis.de/LNI/Proceedings/Proceedings26/GI-Proceedings.26-17.pdf>`__).

For more information about PostGIS' support, see the *SQL-MM Part 3*
section in the PostGIS documentation, such as `here
<http://www.postgis.net/docs/manual-1.5/ch04.html#SQL_MM_Part3>`__.

As of MapServer 6.0, the PostGIS features CircularString,
CompoundCurve, CurvePolygon, MultiCurve, and MultiSurface can be drawn
through MapServer directly.

Example#1: CircularString in MapServer
######################################

The following is the Well Known Text of the feature loading into PostGIS:

::

  INSERT INTO test ( g, id ) VALUES ( ST_GeomFromText('CIRCULARSTRING(0 0,
                                      4 0, 4 4, 0 4, 0 0)', -1), 2);

An example MapServer layer might look like:

::

  LAYER
    NAME "curves_poly"
    STATUS DEFAULT
    TYPE POLYGON
    CONNECTIONTYPE postgis
    CONNECTION "user=postgres password=postgres dbname=curves host=localhost port=5432"
    DATA "g from test using SRID=-1 using unique id"
    CLASS
      STYLE
        COLOR 128 128 128
        ANTIALIAS true   
      END      
    END
  END
  
And testing with :ref:`shp2img` should produce a map image of:

.. image:: ../../images/circularstring.png

Example#2: CompoundCurve in MapServer
#####################################

The following is the Well Known Text of the feature loading into PostGIS:

::

  INSERT INTO test ( g, id ) VALUES ( ST_GeomFromText('COMPOUNDCURVE(
                    CIRCULARSTRING(0 0, 1 1, 1 0),(1 0, 0 1))', -1), 3);

An example MapServer layer might look like:

::

  LAYER
    NAME "curves_poly"
    STATUS DEFAULT
    TYPE POLYGON
    CONNECTIONTYPE postgis
    CONNECTION "user=postgres password=postgres dbname=curves host=localhost port=5432"
    DATA "g from test using SRID=-1 using unique id"
    CLASS
      STYLE
        COLOR 128 128 128
        ANTIALIAS true   
      END      
    END
  END
  
And testing with :ref:`shp2img` should produce a map image of:

.. image:: ../../images/compoundcurve.png

Example#3: CurvePolygon in MapServer
####################################

The following is the Well Known Text of the feature loading into PostGIS:

::

  INSERT INTO test ( g, id ) VALUES ( ST_GeomFromText('CURVEPOLYGON(
                    CIRCULARSTRING(0 0, 4 0, 4 4, 0 4, 0 0),(1 1, 3 3,
                    3 1, 1 1))', -1), 4);

An example MapServer layer might look like:

::

  LAYER
    NAME "curves_poly"
    STATUS DEFAULT
    TYPE POLYGON
    CONNECTIONTYPE postgis
    CONNECTION "user=postgres password=postgres dbname=curves host=localhost port=5432"
    DATA "g from test using SRID=-1 using unique id"
    CLASS
      STYLE
        COLOR 128 128 128
        ANTIALIAS true   
      END      
    END
  END
  
And testing with :ref:`shp2img` should produce a map image of:

.. image:: ../../images/curvepolygon.png

Example#4: MultiCurve in MapServer
##################################

The following is the Well Known Text of the feature loading into PostGIS:

::

  INSERT INTO test ( g, id ) VALUES ( ST_GeomFromText('MULTICURVE((0 0,
                    5 5),CIRCULARSTRING(4 0, 4 4, 8 4))', -1), 6);

An example MapServer layer might look like:

::

  LAYER
    NAME "curves_poly"
    STATUS DEFAULT
    TYPE POLYGON
    CONNECTIONTYPE postgis
    CONNECTION "user=postgres password=postgres dbname=curves host=localhost port=5432"
    DATA "g from test using SRID=-1 using unique id"
    CLASS
      STYLE
        COLOR 128 128 128
        ANTIALIAS true   
      END      
    END
  END
  
And testing with :ref:`shp2img` should produce a map image of:

.. image:: ../../images/multicurve.png

Example#5: MultiSurface in MapServer
####################################

The following is the Well Known Text of the feature loading into PostGIS:

::

  INSERT INTO test ( g, id ) VALUES ( ST_GeomFromText('MULTISURFACE(
                    CURVEPOLYGON(CIRCULARSTRING(0 0, 4 0, 4 4, 0 4,
                    0 0),(1 1, 3 3, 3 1, 1 1)),((10 10, 14 12, 11 10,
                    10 10),(11 11, 11.5 11, 11 11.5, 11 11)))', -1), 7);

An example MapServer layer might look like:

::

  LAYER
    NAME "curves_poly"
    STATUS DEFAULT
    TYPE POLYGON
    CONNECTIONTYPE postgis
    CONNECTION "user=postgres password=postgres dbname=curves host=localhost port=5432"
    DATA "g from test using SRID=-1 using unique id"
    CLASS
      STYLE
        COLOR 128 128 128
        ANTIALIAS true   
      END      
    END
  END
  
And testing with :ref:`shp2img` should produce a map image of:

.. image:: ../../images/multisurface.png

Using MapServer < 6.0
#####################

If you cannot upgrade to MapServer 6.0, then you can use the PostGIS function *ST_CurveToLine()* in your
MapServer LAYER to draw the curves (note that this is much slower however):

::

  LAYER
    NAME "curves_poly"
    STATUS DEFAULT
    TYPE POLYGON
    CONNECTIONTYPE postgis
    CONNECTION "user=postgres password=postgres dbname=curves host=localhost port=5432"
    DATA "wkb_geometry from (select c.id, ST_CurveToLine(c.g) as
                             wkb_geometry from c) as subquery using
                             unique id using SRID=-1"  
    CLASS
      STYLE
        COLOR 128 128 128
        ANTIALIAS true   
      END      
    END
  END





 
 
