JOIN

Description

Joins are defined within a 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 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 PostgreSQL:

CONNECTION "host=127.0.0.1 port=5432 user=postgres password=postgres dbname=somename"
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.

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 support this is the name of the PostgreSQL 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 Shape dataset to DBF file

Mapfile Layer

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

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

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

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