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:
