Joins between tables or layers pose virtually no problem when we have a
unique match between the two elements we are trying to join. For one record of
the reference table or layer, there is one or no record in the table or layer
to be joined. The resulting table or layer of the join can be treated as any
other table or layer.
Things always get complicated when the table or layer to be joined
contains more than one record corresponding to the same reference record. This
is called a 1-n relationship. We will discuss a hidden option that allows us to
answer some of the questions we have in the event of 1-n type space joins.
Firstly, let’s use a simple example.
We have a layer of protected areas, for example the ZNIEFFs (natural
areas of fauna, flora or ecological
interest).
You want to know which municipalities are affected by each zone present
in the ZNIEFF layer.
The simplest would be to add an attribute to the ZNIEFF table containing
the list of the communes affected by each zone
Therefore, we obtain the ZNIEFF layer and a Municipalities
layer
The available join in the layer contextual menu in ArcMap only allows
the calculation of the number of municipalities that intersect the zone.
We have at our disposal the Spatial Join
tool in the Analysis -> Overlay toolbox
This
tool has an option which allows for single or multiple joins. But if we
run the tool with the multiple option :
the
result obtained is the following:
The area with the id 142 (Target_id) is over the municipalities of
Saint-Eloy and Tréhou. The tool produces two identical polygons, one
with the attributes of Saint-Eloy and the other with the attributes of the
Tréhou.
But that’s not what we want. In order to know which are the affected
municipalities by this area, we have to make a request. What we want is an
attribute that contains the list of municipalities.
It is there where the hidden option will allow us to solve our problem.
We will use the spatial join tool, but contrary to what we could think,
we will leave the option one to one because it is actually what we want:
an input zone must produce an output zone, and not several as previously
happened.
On the other hand, it is at the output field level that we are going to
change something.
In the attributes of the common layer we have a field that contains the
name of the town: NOM_COM . If you right-click this field in the
list of output fields, you get a context menu with an option: Properties
. This is the field we want to use to create a list of municipalities.
Then,
you will get the properties window.
- Be sure to give enough space to
contain the resulting list. - In the Merge Rule field , select Join .
- As Separator , select the comma .
Run the tool, and the result is as follows:
Right now, the tool has created a single polygon per input field, and
the COM_NAME field contains the list of affected municipalities separated by commas.
Only problem with this trick: the Joining merge option is
only available for Text fields. So depending on the format of
your output table you will be limited in the field length:
- if you work with shapefiles, the
result is limited to 254 characters.
if you are working with a personal geodatabase,
forget to work with the current version of ArcGis (10.3), it crashes! The tool tries to create a shape in an Access
- database …
- if you are working with a
geodatabase file, the length limit is 2 147 483 647 characters …
always enough! On the other hand, it is you who
set the size of the field before executing the tool. If you do not give enough space for the output list
you will get an error message and the tool will stop running.
