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 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 for PostgreSQL:

CONNECTION "host=127.0.0.1 port=5432 user=postgres password=postgres dbname=somename"

CONNECTIONTYPE [string]

Type of connection (not required for DBF or CSV joins). The following is an example for PostgreSQL:

CONNECTIONTYPE ogr

FROM [item]

Join item in the dataset. This is case sensitive.

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

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

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

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>