Although this article is, primarily, aimed at novice users of Model Builder, it can be helpful for more advanced users since, even though, the procedure may seem simple at first, has a trap.
In our example, we will use an Excel table downloaded from INSEE, with unemployment data by municipality from 2001 to 2012 ( download link of the table , link of the download page ).
In addition to the Excel table, we will use the IGN Geofla Municipalities layer (link of the download page).
We want to create a points layer with unemployment data. To this end, we will use as information, in the Excel table, the INSEE town code (text field), and the same code is found in the GeoFla Municipalities layer. In the previous table, we find the XY coordinates of the municipalities centroid.
Therefore we will make a joint between the Excel table and the Municipalities table based on the INSEE code, then, we will create an XY event layer using the coordinates of the municipalities centroids. We will save everything in a new feature class that will be able to use directly later. The central tool of this model is the joint tool:
Followed by the creation of a points layer with the XY coordinates.
If you are new to Model Builder, you will try, in vain, to join your Excel table and your municipalities table with the Joint tool.
To succeed, the first clue is given by the title of the entries
in the tool. Always read these headings
carefully as they will always save you time!
Let’s
start with the second heading: “Joint Table”. You
want to attach your data to the attribute table of the Municipalities layer.
You will not have any problem.
Now
let’s look at the first heading: “Layer name or tabular view”.
There it goes. A layer necessarily implies an
existing geometry (point, line, polygon). Your
Excel table is not a geographic layer. It is
not a table in the sense of ArcGis (dbf of a shape, table of a geodatabase,
…). And a “Tabular View”, what is it?
A tabular view is an interface between the file on your disk
and the ArcGis tools. You cannot access data directly
from external files, you have to go through a view, an interface.
This is what ArcMap does when you load an Excel table or a
text file, it creates a view making it available to you in the legend window.
It
is this “View” that matches the ArcMap table field name to an Excel
column in your sheet.
If
you do not have a view, ArcMap cannot use Excel columns.
Therefore, you need a tabular
view and you only have one Excel table at the moment. You need a tool to solve this
problem and this tool is “Generate tabular view ” which you will
find in the toolbox ” Tools of Data Management ” -> ” Layers
and tabular views “.
The trap is here! Now we can configure the joint of the two data sources:
For the joints, pay attention to the order of the entries, whether in Model Builder or in ArcMap. The first entry is the table to which you want to add the attributes of the second.
If you now run the model you will have the unpleasant surprise that the model cannot succeed because an error message tells you that the input table does not have an OID (internal identifier). Even if in interactive mode you can apply a joint to an Excel table, you cannot with the geoprocessor tools.
To solve this problem, you must import the Excel table into an ArcGis table. You will use the Excel to Table command in the Data Conversion -> Excel toolbox .
Model continuation Now it remains to configure the generation of points (XY events):
But, this is not the end.
The events layer exists only within the current document and, moreover, it does not have an internal identifier (OID). This lack of identifier seriously limits the possibilities of working on the layer. So you have to create a real data layer, by registering this “events” layer in a geodatabase (or a shape file). We will use the ” Feature class to feature class ” tool from the toolbox ” Conversion tools ” -> Towards a geodatabase
We have finished building the flow model, still remains to define the model parameters for its execution:
For input data, right-click the blue ovals and select Model
Parameter
For
the output layer, do the same.
For fields to be used as X and Y, right-click the Generate
XY Event Layer -> Generate Variable -> Start Variable
tool and select X Field and Y Field .
Right-click
on the two blue ellipses and set them as model parameters.
We obtain the following model:
In order to use it as toolbox tool, rename the elements that will be presented as parameters with explicit texts:
You can save the template and run it from the toolbox:
The result of the model execution is as follows:
