Daniel Ng
Email Me Contact Me

You're welcome to email me at dng11@sympatico.ca.

While I do like hearing from you, I am not interested in SEO services nor offshoring my business, so please don't offer me any.

Follow me on Twitter Subscribe to my RSS feed

How To Bring Linear Work Orders Into GIS
Originally posted by Daniel Ng on May 30, 2013.

Consider this scenario: You would like to visualize your linear work orders on a map. However, you do not want to dish out dough for products such as IBM Maximo Spatial Asset Manager and activeG MapEngine. What other options do you have?

Well, if you already have an enterprise Geographic Information System (GIS) (e.g., ESRI mapping software) in your organization, it makes sense for you to leverage that. This article outlines one approach which one could take to achieve this objective. As you will see, it is really not that complicated.

Before you begin, please review the Pre-requisites section.


In This Example

In this example, I will be using linear road assets and linear work orders.

  1. I have some linear road assets (i.e., 34 to be exact) in Maximo.
  2. I have a road feature layer in GIS and it contains 34 linear features. Each Maximo road asset has a corresponding feature in this layer. Each road feature has a unique ID. I will refer to this unique ID as the Route ID throughout this article. It doesn't matter whether this Route ID is numeric or string as long as it is unique.
  3. Also, I have some work orders on these linear road assets, e.g., for road reconstruction work and rehabilitation work. Most of these work orders have a Start Measure and End Measure defined to signify where work has been done on a specific section of a road. This linear referencing feature is only available to you if you have Linear Asset Manager installed.

Basic Steps

Here are the general steps which we are going to take:

  1. Set up a common join field between Maximo road assets and GIS road feature layer.
  2. Create a database view to expose Maximo linear work orders.
  3. Bring database view created in previous step into GIS.
  4. Create a Route Event layer in GIS.

Step 1: Set up a common join field between Maximo road assets and GIS road feature layer.

This step is about setting up a join between Maximo and GIS data. There are two basic approaches (as explained in table below). You will need to decide which one works best for you. One key consideration is who should be maintaining the data after, i.e., do you want your Maximo folks to maintain the data or the GIS folks? Consider Option #1 below for the former and Option #2 for the latter. I will be using Option #1.

Option #1: Set up new join field on Maximo-side

This means adding a new attribute to the Asset object (via Database Configuration application) and then exposing that attribute on the Assets application.

For each linear road asset record, you will need to assign the respective Route ID to it.
or

Option #2: Set up new join field on GIS-side

This means adding a new column to your GIS road layer's attribute table.

For each road feature, you will need to assign the respective Maximo asset number to it.

Step 2: Create a database view to expose Maximo linear work orders.

This step involves creating a database view table to expose your linear work orders data. One of the benefits of using a database view is that it handles all the complex table joins beforehand. You only need to bring into GIS this one table and not having to worry about bringing in multiple tables and doing table joins there. Also, if you wish to modify your database view (e.g., add/remove columns), you could do that easily.

I would recommend creating this database view inside your Maximo database. Unfortunately, you can only create this view on the database back-end and not from the Maximo front-end. You may or may not have the right to do this. If you don't, consult your DBA.

Below is a sample SQL SELECT script which you can use as a starter for your database view. As it stands, this script will pull all non-cancelled road-related work orders. You are free to modify this script as you see fit. Most fields are optional. The required fields are: L_STARTMEASURE, L_ENDMEASURE, ASSETNUM (if using Option #2) and your custom join field (if using Option #1).

WITH linearmeasurement(l_wonum, l_startmeasure, l_endmeasure) AS
(
	-- Get linear measurements that are specified in work orders.
	SELECT	multiassetlocci.recordkey AS l_wonum,
		multiassetlocci.startmeasure AS l_startmeasure,
		multiassetlocci.endmeasure AS l_endmeasure
	FROM	multiassetlocci 
	WHERE	multiassetlocci.recordclass = 'WORKORDER' AND multiassetlocci.isprimary = 1
)

SELECT	workorder.wonum,
	workorder.worktype,
	workorder.description,
	workorder.status,
	workorder.statusdate,
	workorder.assetnum,
	asset.col_assetnum, -- This is my custom join field. You need to change the name to reflect yours. If using option #2, skip this field altogether.
	workorder.location,
	workorder.actstart,
	workorder.actfinish,
	workorder.actlabhrs,
	workorder.actlabcost,
	workorder.actmatcost,
	workorder.actservcost,
	workorder.acttoolcost,
	linearmeasurement.l_startmeasure,
	linearmeasurement.l_endmeasure,
	workorder.changeby,
	workorder.changedate
FROM	workorder INNER JOIN
	asset ON workorder.assetnum = asset.assetnum AND asset.islinear = 1 AND asset.assettype = 'ROADS' LEFT OUTER JOIN
	linearmeasurement ON workorder.wonum = linearmeasurement.l_wonum
WHERE	(workorder.woclass = 'WORKORDER') AND
	(workorder.status <> 'CAN')

Step 3: Bring database view created in previous step into GIS.

ESRI ArcCatalog

Whoever needs to connect this database view must be granted the proper rights. A basic data reader role will suffice in this case. You may need to consult with your DBA on this.

Launch ESRI ArcCatalog. If you don't have one already created, you will need to create a new OLE DB Connection to your Maximo database. You will be prompted to choose an OLE DB Provider from a pre-defined list. If unsure which one to use, check out www.connectionstrings.com. If the provider is not among your list, you will need to download it from Microsoft site and install it. Since I have SQL Server 2008, I will be using a provider named SQL Server Native Client 10.0.

Once you are connected to the Maximo database, look up the database view which you created in previous step and click-and-drag the table into either a new or existing ArcMap project.


Step 4: Create a Route Event layer in GIS.

What you need to do next in ESRI ArcMap is to create something called a Route Event Layer. In order to create this layer, you will need 2 vital pieces of data: (1) the database view table which ArcMap will refer to as the Event Table, and (2) the road feature layer which ArcMap will refer to as either the Input Route Features or Route Reference depending on which method you choose to create this layer.

There are at least 2 methods to create this route event layer:

  1. Right-click on your database view table and then select Display Route Events... from the context menu. This will launch this window; or
  2. Open ArcToolbox and then select Linear Referencing Tools > Make Route Event Layer. This will launch this window instead.

Both windows may look somewhat different but you will notice that they require almost the same input parameters. It doesn't really matter which method you choose. The same looking event route layer will be generated and will automatically added to the map.

Tip: The route event layer you created is just a snapshot of your current linear work orders. If you created more work orders in Maximo, for instance, you will need to re-create this route event layer. In ArcMap, you can actually script this part so that with a click of a button, you can generate a new route event layer.

Final Notes

Route Event Layer

Here is a screenshot to show you what you can expect to see on your map.

The 2 thick yellow lines are my road assets. County road #12, i.e., Townsend Line, extends from east to west and County road # 79, i.e., Arkona Road, extends from north to south. The red lines are my route event layer, i.e., my linear work orders. As you can see, we are now able to visualize on a map where works have been done on specific sections of the County roads.

To be able to visualize the data is just a tip of an iceberg in GIS world. Now that we have a GIS layer for our linear work orders, we can include this layer in all sorts of spatial analysis. For ArcGIS 10 users, you can even use the new Time Slider tool to create a time-lapsed video showing where and when works have been done on all your linear assets. Sky's really the limit here.

Feel free to email me if you have any questions or concerns. You may also want to consider subscribing to my RSS feeds for future posts.

Copyright © 2013 www.xcentricway.ca. All rights reserved.