.. _join:

*****************************************************************************
 JOIN
*****************************************************************************

.. index:: Join

Description
===========

Joins are defined within a :ref:`layer` object. It is important to understand
that JOINs are *ONLY* available once a query has been processed. You cannot
use joins to affect the look of a map. The primary purpose is to enable lookup
tables for coded data (e.g. 1 => Forest) but there are other possible uses.

Supported Formats
=================
 
- DBF/XBase files
- CSV (comma delimited text file)
- PostgreSQL and PostGIS tables
- MySQL tables

Mapfile Parameters:
===================

CONNECTION [string]
    Parameters required for the join table's database connection (not required
    for DBF or CSV joins). The following is an example connection for :ref:`PostgreSQL
    <input_postgis>`:

    .. code-block:: mapfile

      CONNECTION "host=127.0.0.1 port=5432 user=postgres password=postgres dbname=somename"
      CONNECTIONTYPE OGR
    
    The following is an example connection for MySQL:
    
    .. code-block:: mapfile
      
      CONNECTION "MYSQL:mydbname,user=root,password=mysql,port=3306"
      CONNECTIONTYPE OGR

CONNECTIONTYPE [string]
    Type of connection (not required for DBF or CSV joins). For PostgreSQL and
    MySQL connections use 'OGR'.
    
FOOTER [filename]
    Template to use *after* a layer's set of results have been sent. In other
    words, this header HTML will be displayed after the contents of the 
    `TEMPLATE` HTML.

FROM [item]
    Join item in the dataset.  This is case sensitive.
    
HEADER [filename]
    Template to use *before* a layer's set of results have been sent. In other
    words, this header HTML will be displayed before the contents of the 
    `TEMPLATE` HTML.

NAME [string]
    Unique name for this join. Required.  

TABLE [filename|tablename]
    For file-based joins this is the name of XBase or comma delimited file
    (relative to the location of the mapfile) to join TO. For PostgreSQL and
    MySQL support this is the name of the PostgreSQL/MySQL table to join TO.

TEMPLATE [filename]
    Template to use with one-to-many joins. The template is processed once 
    for each record and can only contain substitutions for items in the joined
    table.  Refer to the column in the joined table in your template like
    [joinname_columnname], where joinname is the NAME specified for the JOIN
    object.

TO [item]
    Join item in the table to be joined. This is case sensitive.

TYPE [ONE-TO-ONE|ONE-TO-MANY]
    The type of join. Default is one-to-one.

Example 1: Join from SHP file to DBF file
=========================================

Mapfile Layer
-------------

.. code-block:: mapfile

      LAYER
        NAME "prov_bound"
        TYPE POLYGON
        STATUS DEFAULT
        DATA "prov.shp"
        CLASS
          NAME "Province"
          STYLE
            OUTLINECOLOR 120 120 120
            COLOR 255 255 0
          END
        END
        TEMPLATE "../htdocs/cgi-query-templates/prov.html"
        HEADER "../htdocs/cgi-query-templates/prov-header.html"
        FOOTER "../htdocs/cgi-query-templates/footer.html"
        JOIN
          NAME "test"
          TABLE "../data/lookup.dbf"
          FROM "ID"
          TO "IDENT"
          TYPE ONE-TO-ONE
        END
      END # layer

Ogrinfo
-------

::

  >ogrinfo lookup.dbf lookup -summary
  INFO: Open of `lookup.dbf'
  using driver `ESRI Shapefile' successful.

  Layer name: lookup
  Geometry: None
  Feature Count: 12
  Layer SRS WKT:
  (unknown)
  IDENT: Integer (2.0)
  VAL: Integer (2.0)

::

  >ogrinfo prov.shp prov -summary
  INFO: Open of `prov.shp'
  using driver `ESRI Shapefile' successful.

  Layer name: prov
  Geometry: Polygon
  Feature Count: 12
  Extent: (-2340603.750000, -719746.062500) - (3009430.500000, 3836605.250000)
  Layer SRS WKT:
  (unknown)
  NAME: String (30.0)
  ID: Integer (2.0)

Template
--------

::

  <tr bgcolor="#EFEFEF"><td align="left">[NAME]</td><td align="left">[test_VAL]</td></tr> 


Example 2: Join from SHP file to PostgreSQL table
=================================================

Mapfile Layer
-------------

.. code-block:: mapfile

      LAYER
        NAME "prov_bound"
        TYPE POLYGON
        STATUS DEFAULT
        DATA "prov.shp"
        CLASS
          NAME "Province"
          STYLE
            OUTLINECOLOR 120 120 120
            COLOR 255 255 0
          END
        END
        TOLERANCE 20
        TEMPLATE "../htdocs/cgi-query-templates/prov.html"
        HEADER "../htdocs/cgi-query-templates/prov-header.html"
        FOOTER "../htdocs/cgi-query-templates/footer.html"  
        JOIN
          NAME "test"
          CONNECTION "host=127.0.0.1 port=5432 user=postgres password=postgres dbname=join"
          CONNECTIONTYPE ogr
          TABLE "lookup"
          FROM "ID"
          TO "ident"
          TYPE ONE-TO-ONE
        END
      END # layer

Ogrinfo
-------

::

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

  Layer name: lookup
  Geometry: Unknown (any)
  Feature Count: 12
  Layer SRS WKT:
  (unknown)
  ident: Integer (0.0)
  val: Integer (0.0)

Template
--------

::

  <tr bgcolor="#EFEFEF"><td align="left">[NAME]</td><td align="left">[test_val]</td></tr>

.. note::

    When testing with MapServer 4.10.0 on Windows this postgresql join caused a
    mapserv.exe crash. However when testing this with a MapServer build > 4.10.0
    the crash did not occur.

Example 3: Join from SHP file to CSV file
=========================================

Mapfile Layer
-------------

.. code-block:: mapfile

      LAYER
        NAME "prov_bound"
        TYPE POLYGON
        STATUS DEFAULT
        DATA "prov.shp"
        CLASS
          NAME "Province"
          STYLE
            OUTLINECOLOR 120 120 120
            COLOR 255 255 0
          END
        END
        TOLERANCE 20
        TEMPLATE "../htdocs/cgi-query-templates/prov.html"
        HEADER "../htdocs/cgi-query-templates/prov-header.html"
        FOOTER "../htdocs/cgi-query-templates/footer.html"  
        JOIN
          NAME "test"
          TABLE "../data/lookup.csv"
          FROM "ID"
          TO "IDENT"
          TYPE ONE-TO-ONE
        END
      END # layer

CSV File Structure
------------------

::

  "IDENT","VAL"
  1,12
  2,11
  3,10
  4,9
  5,8
  6,7
  7,6
  8,5
  9,4
  10,3
  11,2
  12,1

Ogrinfo
-------

::

  >ogrinfo lookup.csv lookup -summary
  INFO: Open of `lookup.csv'
  using driver `CSV' successful.

  Layer name: lookup
  Geometry: None
  Feature Count: 12
  Layer SRS WKT:
  (unknown)
  IDENT: String (0.0)
  VAL: String (0.0)

Template
--------

::

  <tr bgcolor="#EFEFEF"><td align="left">[NAME]</td><td align="left">[test_VAL]</td></tr>
  
Example 4: Join from SHP file to MySQL table
============================================

Mapfile Layer
-------------

.. code-block:: mapfile

      LAYER
        NAME "prov_bound"
        TYPE POLYGON
        STATUS DEFAULT
        DATA "prov.shp"
        CLASS
          NAME "Province"
          STYLE
            OUTLINECOLOR 120 120 120
            COLOR 255 255 0
          END
        END
        TOLERANCE 20
        TEMPLATE "../htdocs/cgi-query-templates/prov.html"
        HEADER "../htdocs/cgi-query-templates/prov-header.html"
        FOOTER "../htdocs/cgi-query-templates/footer.html"  
        JOIN
          NAME "test"
          CONNECTION "MYSQL:test,user=root,password=mysql,port=3306"
          CONNECTIONTYPE ogr
          TABLE "lookup"
          FROM "ID"
          TO "ident"
          TYPE ONE-TO-ONE
        END
      END # layer

Ogrinfo
-------

::

  >ogrinfo MYSQL:test,user=root,password=mysql,port=3306 lookup -summary
      INFO: Open of `MYSQL:test,user=root,password=mysql,port=3306'
      using driver `MySQL' successful.

     Layer name: lookup
     Geometry: None
     Feature Count: 48
     Layer SRS WKT:
     (unknown)
     FID Column = ident
     val: Integer (0.0)

Template
--------

::

  <tr bgcolor="#EFEFEF"><td align="left">[NAME]</td><td align="left">[test_val]</td></tr>

.. note::

    When testing with MapServer 5.6.5 and MySQL 5.1.49 on Windows this MySQL join caused a
    mapserv.exe crash.