Maximo/GIS spatial query

Question

I have a work order (WO) in Maximo:

• The WO has Latitude(Y) and Longitude(X) coordinates in the Service Address tab.
• The WO has a custom zone field.

And there is a spatial table (polygon layer) in a separate GIS database.

---

I want to do spatial query to return an attribute from the polygon record that it intersects and use it to populate zone in the WO.

How can I do this?

(Maximo 7.6.1.1 including Maximo Spatial)

Answer 1

Create a Jython automation script that executes at the desired event (via an object launch point).

Adapt the code in the Library Scripts section of Maximo 76 Scripting Features (pdf):

#What the script does:
#     1. Takes the X&Y coordinates of a work order in Maximo
#     2. Generates a URL from the coordinates
#     3. Executes the URL via a separate script/library (LIB_HTTPCLIENT)
#     4. Performs a spatial query in an ESRI REST feature service (a separate GIS system)
#     5. Returns JSON text to Maximo with the attributes of the zone that the work 
#        order intersected
#     6. Parses the zone number from the JSON text
#     7. Inserts the zone number into the work order record

from psdi.mbo import MboConstants
from java.util import HashMap
from com.ibm.json.java import JSONObject

field_to_update = "ZONE"
gis_field_name = "ROADS_ZONE"

def get_coords():
    """
    Get the y and x coordinates(UTM projection) from the WOSERVICEADDRESS table
    via the SERVICEADDRESS system relationship.
    The datatype of the LatitdeY and LongitudeX fields is decimal.
    """
    laty  = mbo.getDouble("SERVICEADDRESS.LatitudeY")
    longx = mbo.getDouble("SERVICEADDRESS.LongitudeX")

    #Test values
    #laty  = 4444444.7001941890
    #longx = 666666.0312127020

    return laty, longx


def is_latlong_valid(laty, longx):
    #Verify if the numbers are legitimate UTM coordinates
    return (4000000 <= laty <= 5000000 and
            600000 <= longx <= 700000)


def make_url(laty, longx, gis_field_name):
    """
    Assembles the URL (including the longx and the laty).
    Note: The coordinates are flipped in the url.
    """

    url = (
        "http://hostname.port"
        "/arcgis/rest/services/Example"
        "/Zones/MapServer/15/query?"
        "geometry={0}%2C{1}&"
        "geometryType=esriGeometryPoint&"
        "spatialRel=esriSpatialRelIntersects&"
        "outFields={2}&"
        "returnGeometry=false&"
        "f=pjson"
    ).format(longx, laty, gis_field_name)

    return url


def fetch_zone(url):
    # Get the JSON text from the feature service (the JSON text contains the zone value).
    ctx = HashMap()
    ctx.put("url", url)
    service.invokeScript("LIBHTTPCLIENT", ctx)
    json_text = str(ctx.get("response"))

    # Parse the zone value from the JSON text
    obj = JSONObject.parse(json_text)
    parsed_val = obj.get("features")[0].get("attributes").get(gis_field_name)

    return parsed_val


try:
    laty, longx = get_coords()
    if not is_latlong_valid(laty, longx):
        service.log('Invalid coordinates')
    else:
        url = make_url(laty, longx, gis_field_name)
        zone = fetch_zone(url)

        #Insert the zone value into the zone field in the work order
        mbo.setValue(field_to_update, zone, MboConstants.NOACCESSCHECK)
        service.log(zone)
except:
    #If the script fails, then set the field value to null.
    mbo.setValue(field_to_update, None, MboConstants.NOACCESSCHECK)
    service.log("An exception occurred")

LIBHTTPCLIENT: (a reusable Jython library script)

from psdi.iface.router import HTTPHandler
from java.util import HashMap
from java.lang import String

handler = HTTPHandler()
map = HashMap()
map.put("URL", url)
map.put("HTTPMETHOD", "GET")
responseBytes = handler.invoke(map, None)
response = String(responseBytes, "utf-8")

---

Assorted Notes:

1. The Jython script uses Java's JSONObject package.
2. I had the script code-reviewed on https://codereview.stackexchange.com/. The reviews can be found here and here.
3. General Maximo scripting info can be found here: http://maximoscripting.stroske.de/
4. I learned the hard way that some of Jython's standard libraries have been excluded from Maximo's implementation of Jython: https://www.ibm.com/developerworks/community/forums/html/topic?id=263b632d-beef-4231-b19a-fa1254194960&ps=25

5. I had problems using a URL with HTTPS in it -- and problems with a URL that used a domain name in it.

   • For example, this did not work: https://exampleGIS.com/arcgis/rest/services/Example/Zones/MapServer/15/query?geometry=666666.0312127020%2C4444444.7001941890&geometryType=esriGeometryPoint&spatialRel=esriSpatialRelIntersects&outFields=ZONE&returnGeometry=false&f=pjson

   • But this worked: http://hostname.port/arcgis/rest/services/Example/Zones/MapServer/15/query?geometry=666666.0312127020%2C4444444.7001941890&geometryType=esriGeometryPoint&spatialRel=esriSpatialRelIntersects&outFields=ZONE&returnGeometry=false&f=pjson.

   • Sorry, but all I can say is what worked and what didn't work. I can't provide details as to why one thing worked and the other didn't.

Good luck to whoever else tries this! It was more difficult than it needed to be.

Answer 2

To do this live in Maximo using an automation script is possible or by writing custom code into Spatial (more challenging). You want to use the /MapServer/identify tool and post the geometry xy, coordinate system, and the layer you want to query. identify window

You will have to format the geometry object correctly and test your post from the window. I usually grab the post from the network section of developer tools once I get it to work and change the output format to json and use it in my code.

Answer 3

You may actually not need to touch your Maximo environment at all. How about just using a trigger on your work orders table ? That trigger can then automatically fill the zone ID from a simple select statement that matches x and y with the zones in the zones table. Here is how that could look like.

This assumes that your work orders are in a table like this:

create table work_orders (
  wo_id number primary key,
  x number,
  y number,
  zone_id number
);

and the zones in a table like this

create table zones (
  zone_id number primary key,
  shape st_geometry
)

Then the trigger would be like this

create or replace trigger work_orders_fill_zone
  before insert or update of x,y on work_orders
  for each row
begin
  select zone_id
  into :new.zone_id
  from zones
  where sde.st_contains (zone_shape, sde.st_point (:new.x, :new.y, 4326) ) = 1;
end;
/

Some assumptions:

1. The x and y columns contain coordinates in WGS84 longitude/latitude (not in some projection or some other long/lat coordinate system)

2. Zones don't overlap: a work order point is always therefore in one and only one zone. If not, then the query may return multiple results, which you then need to handle.

3. Zones fully cover the territory your work orders can take place in. If a work order location can be outside all your zones, then you also need to handle that (the query would return no result).

4. The x and y columns are always filled. If they are optional, then you also need to handle that case (set zone_id to NULL if either x or y is NULL)

After that, each time a new work order is inserted in the work_orders table, the zone_id column will be automatically updated.

You can initialize zone_id in your existing work orders with a simple update:

update work_orders set x=x, y=y;

This will make the trigger run for each row in the table ... It may take some time to complete if the table is large.