.. index:: JOIN

.. _join:

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

.. index::
   pair: JOIN; LAYER
    
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.

.. index::
   pair: JOIN; Supported formats
    
Supported Formats
=================
 
- DBF/XBase files
- CSV (comma delimited text file)
- PostgreSQL tables
- MySQL tables

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

.. index::
   pair: JOIN; CONNECTION
    
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 POSTGRESQL
    
.. index::
   pair: JOIN; CONNECTIONTYPE
    
.. index::
   triple: JOIN; CONNECTIONTYPE; csv
    
.. index::
   triple: JOIN; CONNECTIONTYPE; mysql
    
.. index::
   triple: JOIN; CONNECTIONTYPE; postgresql
    
CONNECTIONTYPE [csv|mysql|postgresql]
    Type of connection (not required for DBF joins). For PostgreSQL
    use `postgresql`, for CSV use `csv`, for MySQL use `mysql`.
    
.. index::
   pair: JOIN; FOOTER
    
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.

.. index::
   pair: JOIN; FROM
    
FROM [item]
    Join item in the dataset.  This is case sensitive.
    
.. index::
   pair: JOIN; HEADER
    
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.

.. index::
   pair: JOIN; NAME
    
NAME [string]
    Unique name for this join. Required.  

.. index::
   pair: JOIN; TABLE
    
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
    support this is the name of the PostgreSQL table to join TO.

.. index::
   pair: JOIN; TEMPLATE
    
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.

.. index::
   pair: JOIN; TO
    
TO [item]
    Join item in the table to be joined. This is case sensitive.

.. index::
   pair: JOIN; TYPE
    
TYPE [ONE-TO-ONE|ONE-TO-MANY]
    The type of join. Default is one-to-one.

Example 1: Join from Shape dataset 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 Shape dataset 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 postgresql
          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>


Example 3: Join from Shape dataset 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"
          CONNECTIONTYPE CSV
          TABLE "../data/lookup.csv"
          FROM "ID"
          #TO "IDENT"  # see note below
          TO "1"       # see note below
          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

.. note::
   The CSV driver currently doesn't read column names from the first
   row. It just uses indexes (1, 2, ... n) to reference the
   columns. It's ok to leave column names as the first row since they
   likely won't match anything but they aren't used. Typically you'd
   see something like TO "1" in the JOIN block. Then in the template
   you'd use [name_1], [name_2], etc...

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 (prov.html)
--------------------

Ideally this the template should look like this::

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

But since attribute names are not supported for CSV files (see note
above), the following will have to be used::

  <!-- MapServer Template -->
  <tr bgcolor="#EFEFEF">
    <td align="left">[NAME]</td>
    <td align="left">[test_2]</td>
  </tr>

Example 4: Join from Shape dataset to MySQL
===========================================

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 # style
        END # class
        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 "mysql-join"
          CONNECTIONTYPE MYSQL
          CONNECTION 'server:user:password:database'
          TABLE "mysql-tablename"
          FROM "ID"
          TO "mysql-column"
          TYPE ONE-TO-ONE
        END # join
      END # layer