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.
