This ArcFM Buzz blog post is the second in a series which will explore the functionality of ArcFM Advanced Labeling.  If you aren’t familiar with the basics of AAL, please read the first post in the series to get quickly up to speed.

In this post you will learn how to create labels based on data that resides in external databases. The companion video below shows ArcFM Advanced Labeling displaying the names of customers where the customer data resides in an external database.

Introduction: It’s quite common for utilities to have data relevant to the GIS stored in a database that is external to the spatial geodatabase.  A typical scenario is when a different group manages the data, as is often the case with customer data.  There may be other reasons for this separation such as database performance, security, support of legacy applications, and simply ease-of-use.   Unfortunately, there is no direct support in ArcGIS to relate this data to existing geodatabase feature classes, and even if there were, there is no native ArcGIS support for labeling on one-to-many relationships. AAL solves this problem by allowing you to define within a label expression how to connect to the external database, how to relate your GIS features to the data, and exactly what data you wish to extract. In this demonstration, we will label ServicePoints with the first and last names of the customer’s living at the service address. In addition, critical customers will be labeled in red. In this case, the customer data resides in a separate SQL Server Express database, but it could just as easily reside in Oracle, a Microsoft Access database table, a Microsoft Excel spreadsheet, or in another common database format such as DBase.

Let’s have a quick look at the expected labels in ArcMap, the related ServiceAddress table, and the external table named Rx_Customer which resides outside of the geodatabase.

Let’s get started. We’ll dissect each element in the label expression tree. The numbers in the screen shots correspond to the bulleted numbers in the text.

1. In this expression, we’ll be labeling ServicePoints which is a GIS feature class.  To get to the data that we want, we first must get to the related ServiceAddress records.  In the sample Minerville database, this is a one-to-many geodatabase relationship class.  For each ServicePoint that we are labeling, AAL will find all of the related ServiceAddress records.

1. Next we use the Database labeler to attach to and consume the data in the SQL Server database.  Technically, the Database Labeler can utilize any OleDb compliant data source which would include Microsoft Excel. Notice that the Database labeler is added underneath the ServiceAddress relationship labeler, thus implying that we are wanting to access data from a external data source that is in some way related to the rows of ServiceAddress.
2. We must define the Connection String to specify what database we want to connect to.  Your DBA can help you with this task, or you will find many examples at http://www.connectionstrings.com/
3. The primary key of the origin table is the field in the parent table (ServiceAddress in this case) that uniquely identifies the rows in that table.
4. Enter the name of the table in the database that you want to access and press the connection button to the right.  This will populate the drop-down lists seen in steps 5 and 6.
5. The Foreign key in the destination table (in this case Rx_Customer) is the field that matches the field identified in step #3.  The two fields do not have to have the same name.
6. The Primary key in the destination table is the field that uniquely identifies each row in the destination table (in this case Rx_Customers)
7. Optional: In a one-to-many relationship, you may have many related rows.  You can reduce that number by specifying a constraint.  This essentially becomes an SQL where clause.

1. Optional: After connecting to a table such as the Rx_Customers table, you can provide additional filtering by using the Conditional Labeler.  The Conditional Labeler allows you to type in an SQL where clause (e.g. CRITICALSTATUS = 1).  In this example, the Conditional labeler is used twice.  Ultimately, we’ll be able to produce different labels based on the critical status of each customer.

1. Finally, we use the ubiquitous Field/Text labeler to define exactly how we want the labels to look. In this case the first name of the customer, followed by a space, followed by the customer’s last name.
2. This list of fields that appears in the window is provided by the parent labeler (the Conditional labeler in this case). Since the Conditional Labeler provides rows from it’s parent (the Database Labeler) the list of fields we see is from the Rx_Customers table.
3. We can use whatever font / color we want for individual label elements in AAL.  In this example, critical customers are shown in red while non-critical customers use the default color.
4. Because we may have multiple customers at a given ServicePoint, we’ve chosen to add a carriage return after each record.

In summary, AAL’s Database labeler allows you to label based on data that resides external to the geodatabase. The Database Labeler can be used in conjunction with other labelers such as the Relationship Labeler and the Conditional Labeler to create the exact labels you are after.

On a related note, you might be asking yourself, “how’s the performance of labeling by accessing an external database?  Won’t this be slow?”. As we will see in upcoming ArcFM Buzz posts, AAL has several options including caching, retrieving labels in bulk-mode, and label time-outs that will serve to make your labeling quick as well as dynamic.

If you are interested in ArcFM Advanced Labeling and would like a trial version, please contact your Telvent account representative.