You're welcome to email me at firstname.lastname@example.org.
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.
How To Bring Linear Work Orders Into GIS
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.
Here are the general steps which we are going to take:
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.
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')
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.
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:
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.
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.