Enable location-aware event processing with WebSphere Business Events

WebSphere Business Events V7.0.1 provides methods for implementing location-aware business event processing using spatial data. This article provides compelling real-world examples that implement geofence detection and proximity alerting. Such techniques have many useful applications including managing service interuptions, delivery schedules and location-based marketing. This content is part of the IBM Business Process Management Journal.

Share:

Peter Crocker, WebSphere Client Technical Professional, IBM

Photo of Peter CrockerPeter Crocker has worked for IBM for 11 years, and is based at Hursley Park development laboratories near Winchester, UK. He has specialised in message broker technologies and business event processing, which has included roles in the development of WebSphere MQ and WebSphere Message Broker. He then spent time as an integration consultant supporting customers as they built solutions. This was followed by a time back in the lab as a product architect for WebSphere Business Events. Peter is now a WebSphere client technical professional once again out working with customers in the UK.

Peter has a MEng in Engineering Science and a MSc in Engineering Computer Science both from Oxford University.



Mark Walters (mark.walters@uk.ibm.com), Senior Application Designer and Developer, IBM

Mark Walters photoMark Walters joined the WebSphere Business Event team at Hursley Park, UK in 2008. He previously designed and developed support systems for Cognos, specialising in business process management and web-based infrastructure. With WebSphere Business Events, Mark has designed and implemented business-centric user interfaces and recently worked on location-awareness project implementation and visualisation.

He has an MEng in Computer Systems and Software Engineering from York University.



19 January 2011

Introduction

Location awareness is an important element of business event processing when the location of the events being processed forms a part of the patterns that are to be detected. Location-aware event processing is relevant across various industries--transportation, provision of services, and location-based marketing to name but a few. Following are some examples in such industries.

Example 1: Service interruptions

The first scenario, shown in Figure 1, considers a service where location is relevant to the provisioning of that service and where reports of interruptions to the delivery are likely to be clustered around allocation area or zone. The scenario considers problems being reported by customers to a service and how the customer care experience can be improved to best handle the case when the reports for an area hit a threshold. The scenario assumes that all reports are handled, but the issues that are impacting most customers for a given area are fast tracked. The problems for a mobile operator might be dropped conversations, poor signal quality or intermittent coverage. All of these will reduce customer satisfaction, ultimately leading to the loss customers.

The report being raised by a customer includes information about the location related to the incident. This would most likely be an address (a postal code). The location-aware piece of the solution translates this address into a location and looks for a pattern where three tickets are raised for the same service over a 30-minute period. If this condition is met, an action is sent to a supervisor with a higher importance than the individual reports so that any follow-on work to resolve the problem can be coordinated. Once the problem has been identified and an expectation for the resolution is known, a reply is sent to the originators. At this stage, we have the opportunity to leverage location awareness again to notify all people who are likely to be disrupted by the problem, not just the original reporters. Finally with the problem resolved, a follow-up message is sent to thank those who reported the original problem.

Figure 1. Service interruptions scenario
Event processing scenario for service interruptions using location information

The location awareness piece of this solution is where the issue’s location is identified as belonging to a specific area of coverage. The event processing is able to correlate these requests looking for patterns in both proximity and time.

Example 2: Multiple late deliveries

Another scenario where location awareness is important is seen in Figure 2. Consider a delivery company that wants to provide their customers with information about the likelihood of a delay and also to give them the opportunity of a discount if a delay is certain.

If we have a record of a vehicle’s position over time and know when it’s expected at a particular destination on a delivery route, we can use its current position to determine whether it’s likely to be late. If the vehicle is late multiple times on the same route, we can automatically recommend that the customer receive a discount, or take some other action knowing that it is likely to be late. This could also be useful for rerouting deliveries in the future to improve levels of service and customer expectations.

Figure 2. Multiple late deliveries scenario
Event processing scenario for multiple late deliveries using location information

Example 3: Location-based marketing

This scenario, shown in Figure 3, is an example of a service that's based of the knowledge of a person’s mobile phone location. This scenario requires customers to opt in to the service, because they will be required to surrender some of their privacy to the operator. However we're already seeing people beginning to release location information to trusted parties, such as Google Latitude, Facebook and Twitter.

This scenario assumes a person has shared their location with a third-party location-based marketing service that also has access to some of their previous purchases at retail locations that are also signed up for the offering. The scenario is triggered by a person moving near a retail location--in this example, a coffee shop. The system identifies that the person has made previous purchase at this location and, therefore, has in the past been interested in what they have to sell, such as coffee. If records show that they haven’t made any purchases in the past month then they are issued an offer to encourage them to return to the store.

Figure 3. Location-based marketing scenario
Event processing scenario for location based marketing

In this example, the location awareness piece of the solution is used to establish whether the event from the mobile is either within a predetermined area or within a certain distance of the store. Event processing stores and acts on both the proximity and the previous purchase history.

Deriving meaning from a location-based event

The fundamentals for deriving meaning from a location-based event are:

  • Considering events within an area
  • Distance from an event to a known point
  • Proximity to another event in both distance and time

Implementing the location-aware scenario demonstrates how you can use WebSphere Business Events to work with events containing location information to create actions that can alert and visualise this awareness for each of the three meaningful concepts.

For events within an area, we detect the areas containing the event, where the event sent to WebSphere Business Events (Business Events) contains location information and Business Events checks whether the location is inside the areas defined in our seeded database data. We're also able to detect whether an event is entering or exiting an area by comparing previous events containing areas with those of the new incoming event. This needs to be done in the context of an identifier for that event so we can keep track of different location events.

Three main components are used in the solution:

  • Business event processing is provided by WebSphere® Business Events V7.0.1.1,
  • Spatial database and calculations is provided by DB2® Spatial Extender.
  • Visualisation is provided by WebSphere ILog® JViews Maps.

Note that other providers such as ESRI® could be used for visualisation. Essentially all that is required is that the mapping and visualisation solution is able to be configured to understand the spatial data in our database.


The scenario

We've developed a simple scenario using database tables with some seeded data to represent:

  • Transports (for example, ships, planes, trucks)
  • Places (the point location of a place, such as the centre of an airport)
  • Areas (the geofence surrounding a place point, for example, airport airspace or harbour jurisdiction)

Business Events can be used to detect patterns related to the location of a transport by referring to the database tables when an event for updating a transport location arrives. A compelling business scenario can be developed from these concept tables, such as asset or package tracking. A list of packages could be loaded into another database table and then related to the transport by the transport ID. When the package moves to a different transport, the transport ID for the package can be updated to reflect this.

Knowing where the package is located by sending transport location updates to Business Events enables us to detect location-related patterns. For example:

  • Knowing when a transport has entered an area, the courier company can pass the information to the customer that the package is about to arrive at its destination. Similarly, if the transport leaves an area, the package can be considered on its way to a new destination. The knowledge that a package remains in an area is also useful. This information can be derived from the previous events.
  • If an incident occurs at a place and that place is the destination of the transport or the transport is within a certain distance of the place, an exception event can be raised to re-route the transport. This knowledge can be used to inform customers of late delivery and provide an opportunity to move the package to a different transport in order to continue the delivery.
  • If a position update to a transport gives the same location as before, the transport could be considered stopped for some reason and an exception event processed such that an intercept to that transport is required to continue the package delivery.
  • Knowing the position of a transport on a particular route can be used to compare with previous runs of that route and so give an accurate indication of the estimated arrival time.

Figure 4 is a simple diagram of the database layout for the scenario.

Figure 4. Scenario database layout
Scenario database layout

Implementing the location-aware scenario

The following describes how to implement the scenario using Business Events, and briefly demonstrates a simple implementation of tables and views so that detected events can be visualised.

This section assumes the reader has some knowledge of Business Events and builds on this knowledge by introducing the database spatial extensions. Each of the pattern types covered in the previous section are described in detail here.

The scenario implemented by the project and example database is simplistic, but could form the basis of a real-world project by adding features such as different transport types and incident types. Each incident type could be designed to trigger specific interaction sets to derive alerts to the visualisation and other actions.

Geofencing

Geofencing can be described as:

  • The ability to determine whether an event is within an area (frequently referred to as a geofence). For example, a truck being inside the area of a depot.
  • The ability to determine whether a point has entered or exited an area. For example, the truck has now arrived or left the depot.
Figure 5. Example geofence
Example geofence

For the purposes of visualising on a map and thus being able to make decisions based on the location of an object, it's often important to know whether an object is inside a particular area or geofence.

For example, you may be looking at a map of vessels near a port. Perhaps the port has an area of demarcation around it such that the port authorities are responsible for all vessels within that area. In that case, it would be important for the authorities to be aware at a glance of which vessels are within the area. This is also true for other types of transport, such as knowing whether an aeroplane is within the airspace of an airport or trucks inside the parking lot of a depot.

Using Business Events, it's possible to build a project so that an incoming event, such as the movement of a position of a vehicle, fires queries to a database to find matching geofences that the vehicle is currently within. The project can then be made to fire subsequent events (for further processing) or actions per matching geofence--it's perfectly reasonable that place areas could overlap and so a vehicle may be considered inside more than one geofence at a time.

The actions or subsequent events (synthetic events) could then perform updates to the database so anything visualising the vehicle could be made to show that the vehicle is within that particular place area.

All this relies on a database with seeded data as the basis for the data being queried, updated and used by the visualisation application. In this article, we'll show example tables containing spatial data types and how Business Events can be made to query and update those types for our example scenario.

To determine matching place areas for a vehicle (or transport as we'll call it here) we use a table containing a row for each place, where one of the columns is a geofence that describes the area of that place.

Geofencing database queries

These DB2 SQL code snippets show how the scenario database tables are built and example queries.

Listing 1. SQL for the creation of the AREAS table
CREATE TABLE AREAS
(id SMALLINT NOT NULL,
name VARCHAR(60),
geofence ST_POLYGON);
Listing 2. SQL to seed the AREAS table
INSERT INTO AREAS
    (id, name, geofence)
  VALUES
    (1, 'Southampton Docks',
     ST_Polygon('polygon((50.916 -1.459, ...))',1003)),
    ...);
Listing 3. Example SQL and results to determine which areas a location is within
SELECT id, name FROM AREAS
WHERE (DB2GSE.ST_CONTAINS(geofence,
  DB2GSE.ST_POINT(50.78,-1.3,1003)) = 1);

Area(s) containing the point 50.90,-1.42

ID     NAME
------ --------------------------------------------------------
     1 Southampton Docks
     2 Solent Waters

Note: The downloads provided with this article include files for the SQL to create the tables along with the accompanying example select statements.

Geofencing work in Design Data

A touchpoint named Transport contains an event named Position Update for Inside an Area. This event is used to send updates to the position of a given transport. The event contains an event object named Location Update, which in turn includes the fields Transport ID, Latitude and Longitude, as shown in Figure 6.

Figure 6. Location Update event fields
Location Update event fields

Note: To simplify the testing of the application, different event names have been used for each of the use cases. In this case, Position Update for Inside an Area is being used. In reality there would only be one event that updates a transports location and all event processing for that type of event would use that location update event.

This is used to construct two intermediate objects, Location Data and Areas. All of the fields are mapped to the Location Data; only Transport ID is mapped to the Areas intermediate object.

A SQL mapped expression is used to construct two fields (Area ID and Area Name) of the Areas intermediate object. As shown in Listing 4, the WHERE clause is used in this mapped expression, which also associates with the AREAS table within the SPATIAL database:

Listing 4. Geofencing Design Data mapped expression
WHERE (db2gse.st_contains(
  AREAS.GEOFENCE, db2gse.st_point(
    $(Location Data.Latitude),$(Location Data.Longitude),1003)) = 1)

Figure 7 shows the settings for the Area ID intermediate object field.

Figure 7. Area ID intermediate object
Area ID intermediate object

Figure 8 illustrates all of the mappings and field constructions that have been established in Design Data for identifying which areas a location is within.

Figure 8. Geofencing mappings and field constructors
Geofencing mappings and field constructors

The mapped expression is used to derive the Area ID and Area Names that match the SQL WHERE clause, using the AREAS table. The WHERE clause also uses the Latitude and Longitude fields of the incoming Position Update event, stored in the Location Data intermediate object.

The Transport in Area action created from the synthetic event of the same name needs to be set to generate multiple actions for each action object such that an action is created per matching geofence found using the SQL WHERE clause, as shown in Figure 9.

Figure 9. Transport in Area action properties
Transport in Area action properties

The Containing Area action object needs to be defined such that it will cause each action object to produce a new action, as shown in Figure 10.

Figure 10. Containing Area action object properties
Containing Area action object properties

Geofencing work in the Design widget

In Business Events Design an interaction set fires the synthetic event for the areas that contain the transport's location. A regular action is also fired so that the action and values can be observed within the Business Events Tester widget, as shown in Figure 11.

Figure 11. Inside an Area interaction
Inside an Area interaction

The synthetic events can now be used for further processing by interaction sets related either to the area, the transport or a combination of the two.

Testing the geofencing

You can trigger the interaction using the tester widget, by selecting the appropriate event template, as shown in Figure 12.

Figure 12. Selecting the template for inside an area
Selecting template for inside an area

Enter the event data, for example, this position matches two of the geofences in our example data, as shown in Figure 13.

Figure 13. Example event data for Location Update
Example event data for Location Update

When the event is sent, two actions are generated, each containing objects with fields specifying the matching geofence, as shown in Figure 14.

Figure 14. Actions from matching areas
Actions from matching areas

If user trace is enabled, you can see the query made to populate the intermediate objects used to create the actions, as shown in Listing 5.

Listing 5. User trace showing geofencing query
BEER9027 > Executing SQL 'select AREAS.ID, AREAS.NAME from AREAS WHERE ...
BEER9029   SQL Results : {1 ; Southampton Docks}{2 ; Solent Waters}
BEER9028 < Retrieved 2 rows.

Entering or exiting a geofence

As an extension to knowing whether something is within a particular geofence, it can be useful to visualise or alert when a transport has entered or exited a geofence. For example, an aeroplane entering a particular airspace may need to notify the airport authorities, or the authorities may need to be notified automatically and make contact with the craft. Being notified of this event is especially useful when represented visually.

Similarly if a transport leaves the area of a geofence, the authorities of that area may want to know that they're no longer responsible for that transport.

Entering or exiting database queries

Below is a sample of DB2 SQL and results, listing all areas that a point moving from 50.78,-1.3 (previous location) to 50.90,-1.42 (current location) is entering.

Listing 6. Areas that a point have entered
SELECT id, name FROM AREAS
WHERE(db2gse.st_contains(GEOFENCE,
      db2gse.st_point(50.78,-1.3, 1003)) = 0)
AND  (db2gse.st_contains(GEOFENCE,
      db2gse.st_point(50.90,-1.42, 1003)) = 1);

Area(s) that a point moving from 50.78,-1.3 to 50.90,-1.42 is entering

ID     NAME
------ ------------------------------------------------------------
     1 Southampton Docks

Note: A spatial index on the GEOFENCE field not exploited for queries using st_contains()=0. This may become create a problem with query speed when there are a large number of areas.

Entering or exiting geofence work in Design Data

The event named Position Update for Entry and Exit drives this use case. As before this event contains the Transport ID, Latitude and Longitude. Figure 15 shows the mappings and field constructors used to derive the Entering and Exiting logic.

Figure 15. Entering and exiting mappings and field construction
Entering and Exiting mappings and field construction

Transport ID, Latitude and Longitude are mapped to the Location Data intermediate object. In addition, a Location Data with Previous intermediate object is used. This is a specialised summary instance intermediate object as defined in the Object tab of the properties, as shown in Figure 16.

Figure 16. Location Data with Previous object properties
Location Data with Previous object properties

A summary instance intermediate object can be used to hold the state within fields from previous events. Two fields are mapped from the latitude and longitude that has been received on the incoming event. Two additional fields take a copy of the previous values of these fields before this mapping takes. The following shows the field construction of one of these fields.

Figure 17. Field construction for Location Data with Previous
Field construction for Location Data with Previous

Note: This field construction is associated with the incoming event and takes place in the Touchpoint portion of Design Data.

Two further intermediate objects are defined: Areas Entered and Areas Exited. These are both similar to the Areas intermediate object already described with the exception of the WHERE clause that they use in the mapped expressions. Listing 7 shows the WHERE clause for the Areas Entered case.

Listing 7. Areas Entered mapped expression WHERE clause
WHERE (db2gse.st_contains(
  AREAS.GEOFENCE, db2gse.st_point(
    $(Location Data with Previous.Previous Latitude),
    $(Location Data with Previous.Previous Longitude), 1003)) = 0)
  AND (db2gse.st_contains(
AREAS.GEOFENCE, db2gse.st_point(
    $(Location Data with Previous.Current Latitude),
    $(Location Data with Previous.Current Longitude), 1003)) = 1)

Notice how the Location Data with Previous intermediate object is used. The two halves of the WHERE clause first select the areas that did not contain the previous location, the select those that now contain the current location. Where both of these are true, the transports location has moved into the returned areas.

A similar WHERE clause is used for the Areas Exited case, as shown in Listing 8, with the only difference being that it returns areas which used to contain the moving location, but no longer do.

Listing 8. Areas Exited mapped expression WHERE clause
WHERE (db2gse.st_contains(
  AREAS.GEOFENCE, db2gse.st_point(
    $(Location Data with Previous.Previous Latitude),
    $(Location Data with Previous.Previous Longitude), 1003)) = 1)
  AND (db2gse.st_contains(
AREAS.GEOFENCE, db2gse.st_point(
    $(Location Data with Previous.Latitude),
    $(Location Data with Previous.Longitude), 1003)) = 0)

Entering or exiting geofence work in the Design widget

A filter is required for each of the entering or exiting cases. These filters simply check to see whether intermediate objects exist for each cases. Figure 18 shows the filter created for the entry into an area.

Figure 18. Filter to determine area entry
Filter to determine area entry

These filters can then be used generate the appropriate alerts, as shown in Figure 19.

Figure 19. Interaction to alert for entry or exit
Interaction to alert for entry or exit

In this case, only alerts are being generated for the two cases of entry or exit from an area. Actions generated by entering or exiting could just have easily been used for further processing as synthetic events. These events could then be related by either the transport id or the area id.

Testing entering or exiting geofence

To test this part of the example scenario, we must first send a position update event to seed the summary instance object with a position for a transport that we'll use to test for entry or exit.

From the scenario project, select the Position Update for Entry and Exit event template in the tester widget, and enter values to seed an existing transport id with a position, as shown in Figure 20.

Figure 20. Seeding a transport for testing entry and exit
Seeding a transport for testing entry and exit

At this point, we shouldn't receive any actions as a result of this event since our Previous Latitude and Previous Longitude intermediate object fields are null. Now if we send a similar event, but with a location that is outside of the geofence that currently contains our transport, we'll see a matching action, as shown in Figure 21.

Figure 21. Update a transport position for testing entry and exit
Update a transport position for testing entry and exit

Sending this event reveals the exited area in the subsequent action, as shown in Figure 22.

Figure 22. Exited area action
Exited area action

If we now send the originally seeded data again as in Fig. 20, we'll see the Areas Entered action, as shown in Figure 23.

Figure 23. Areas Entered action
Areas entered action

Voila!

Proximity to a known point

For purposes of the scenario, the proximity to a known point is the ability to calculate distance to a fixed known point for subsequent use in logic, for example, "Distance to light house in meters < 1,000."

Figure 24. Proximity to a known point
Proximity to a known point

As well as determining if a transport is within a particular distance to a point, it can also be useful to know the distance to a the nearest edge of a particular geofence and to fire an action to visualise or to alert when that happens. For example, the crew of an aeroplane may want to know that they need to be prepared to make communication with the airport runway tower.

Again, this depends on the fact that we keep the places and the areas they define in our database and can query for distance of geo-spatial values. Both DB2 (with spatial extender) and Oracle® provide these as common types of queries for spatial data.

It's possible to check the distance measure from within a filter in Business Events by querying the distance for the incoming transport position to its destination (as determined by our database data) and then creating a filter for that. This gives control of the distance threshold to the business user using the Business Events Design application. It's also possible to simply define a view in the database that returns the identities of those places or transports that your incoming transport movement event is close to, in which case the distance threshold would be fixed in the view definition.

Proximity database queries

The ST_DISTANCE function is used to calculate the distance between two points. In this section the intention, we'll use an example where we'll filter based on the distance between an event location and a fixed place. So first we need to create a places table, as shown in Listing 9.

Listing 9. Places table creation
CREATE TABLE places
  (id SMALLINT NOT NULL,
   name VARCHAR(60),
   point ST_POINT);

Next, we'll insert a place into this table to indicate the place that we want to know event distances from, as shown in Listing 10.

Listing 10. Places insertion
INSERT INTO PLACES
    (id, name, point)
  VALUES
    (1, 'Southampton Docks', DB2GSE.ST_POINT(50.90, -1.42, 1003) );

The following statement returns the distance to the place with the id = 1, in units of meters and to one decimal points worth of accuracy.

Listing 11. Distance query and result
SELECT
CAST( DB2GSE.ST_DISTANCE(
  DB2GSE.ST_POINT(50.78,-1.3,1003), places.point, 'METER')
    AS INTEGER) AS distance
  FROM places
  WHERE id = 1;

DISTANCE
-----------
      18825

This is useful if you're looking for a distance to a given point and is a good example of how to use the distance function.

Proximity work in Design Data

The interaction used in this scenario is triggered by the Position Update for Proximity to Destination event, which contains the normal position update fields and, crucially, a Destination ID, which is the ID of the places table entry, which is the intended destination of the transport position update.

The SQL expression uses the new position information and the destination to form a query to give the distance of that position from the point of the relevant place, as seen in Figure 25.

Figure 25. Proximity mappings and field constructors
Proximity mappings and field constructors

The Distance intermediate object field is defined in Design Data as shown in Figure 26.

Figure 26. Distance intermediate object field
Distance intermediate object field

Proximity work in the Design widget

Different filters can now be used in Design, for particular places of interest and, with further control, over the distances that are of interest. The places table could also include type information that could be used in subsequent filters.

You can define a filter to detect events within a vicinity, as shown in Figure 27.

Figure 27. Vicinity filter example
Vicinity filter example

Testing proximity

We can use the row identifier of the places table to look up the proximity of an incoming event to the location (the Destination ID in the event). To test this, choose the appropriate event template and send a position for a transport and the ID of the destination, as shown in Figure 28.

Figure 28. Choosing the proximity template from the scenario project
Choosing the proximity template from the scenario project

Since this interaction uses the filter Less than 2000 metres to destination, you need to enter a latitude and longitude that places the transport within this distance of its destination, as shown in Figure 29.

Figure 29. Proximity event example
Proximity event example

Firing this event shows you a couple of things in the tester widget: the vicinity filter evaluates to true and you have a Nearing Destination action with details, as you can see in Figure 30.

Figure 30. Action from proximity detection
Action from proximity detection

Proximity to another event

In order to calculate a proximity to another event, you need to be able to store location information from events.

As an extension of the idea of simply retrieving the distance of a transport from its defined destination, you may want to know what other transports or places are near the incoming transport event, or alert transports in the proximity of a place if there is an incident at that place.

You can use a view definition to allow Business Events to query for transports near a place or other transports and thus fire subsequent synthetic events or actions that could then update your visualisation of those transports with a change of status. This is useful for exception processing. For example, if there is an incident at a place, all transports whose destinations are that place and are within a certain distance would want to be alerted so that decisions can be made whether to reroute the transports to another destination.

You can also configure a project to record the history of the transport positions over time by inserting the information from the transport movement event into a history table. The history of the movement of a transport could be used to visualise the route it has taken over time and thus determine how long it would take to do a similar journey in the future and determine how long the transport might take to reach that destination again. A customer of a package on that transport could then be given an accurate idea of exactly when it would be expected to arrive.

Figure 31. Proximity to another event
Proximity to another event

Event location database insertion

The transports table, shown in Listing 12, holds the position of each transport. When an incident occurs, the transports within a given distance can be identified. The table holds the id, name and location of a transport as a point type--in this scenario, latitude and longitude.

Listing 12. Transports table creation
CREATE TABLE transports
(id SMALLINT NOT NULL,
 name VARCHAR(60),
 location ST_POINT);

If a transport position event arrives using an identifier that does not have a record in the transports table, Business Events can be configured so that an INSERT statement is generated to add it to the table, as shown in Listing 13.

Listing 13. Transports table insertion
INSERT INTO transports
    (id, name, location)
  VALUES
    (1,
     'Southampton Harbour Patrol',
     DB2GSE.ST_POINT(50.90,-1.42,1003));

If the transport is already in the table, the current position is used to update the database record, as shown in Listing 14.

Listing 14. Transports table update
UPDATE transports
  SET
    location = DB2GSE.ST_POINT(50.90,-1.42,1003)
  WHERE
    id =1;

Event proximity work in Design Data

Using the JDBC action connector in update or insert mode is also known as upsert. This action connector uses the _Rdbms connector action object, which is inserted from a template.

You then need to define the action object properties as using an RDBMS connection in order to insert or update the database. Configuring the RDBMS connection requires you choose to perform an insert if the WHERE clause finds no rows to update, as shown in Figure 32.

Figure 32. Choosing upsert behaviour
Choosing upsert behaviour

The _RDBMS connector action object contains the following fields:

  • InsertColumns is used to generate the column identifiers from the SQL insert statement; for example, INSERT INTO transports (id, name, location)
  • InsertValues is used to generate the VALUES part of the SQL insert statement; for example, INSERT INTO transports (id,name,location) VALUES (1, 'Southampton Harbour Patrol', 'ship', DB2GSE.ST_POINT(50.60,-1.58,1003) )
  • Update is used to generate the UPDATE SQL clause; for example, UPDATE transports SET name = 'Southern Skylark', location = DB2GSE.ST_POINT(50.31,-1.15,1003))
  • Where is used to generate the WHERE clause used for the UPDATE statement; for example, WHERE id = 1

Table 1 summarizes how the different fields of the _RDBMS connector action object can be used to perform an upsert for our scenario:

Table 1. RDBMS connector field uses
FieldTypeValueDescription
InsertColumnsConstantValueid, name, locationThe columns that will be used in the case of an insert.
InsertValuesJavaScript"("+Location Data.TransportID+", '"+Location Data.Transport Name+"', db2gse.st_point(" + Location Data.Latitude+ "," + Location Data.Longitude + ",1003))"A piece of JavaScript to construct the SQL values clause that matches up with the columns.
UpdateJavaScript"name = '"+Location Data.Transport Name+"', location = db2gse.st_point(" + Location Data.Latitude+ "," + Location Data.Longitude + ",1003)"A piece of JavaScript to form the update clause to follow the set keyword in the case of an update.
WhereJavaScript"id = "+Location Data.TransportIDA piece of JavaScript that is used for the WHERE clause.

Event proximity work in the Design widget

The event must always generate the action to update the transport's location, as shown in Figure 33.

Figure 33. Interaction for storing location
Interaction for storing location

Event proximity testing

For the scenario project, choose the Position update for storage event template, then enter a position update for an existing transport, as shown in Figure 34.

Figure 34. Position update event
Position update event

The resulting action shows the database update,as shown in Figure 35.

Figure 35. Position update action
Position update action

You can also see the effect on the transports table in Listing 15.

Listing 15. Selecting from transports
SELECT
      id,
      name,
      CAST(DB2SE.ST_X(location) AS DECIMAL(10,2)) AS latitude,
      CAST(DB2SE.ST_Y(location) AS DECIMAL(10,2)) AS longitude
FROM transports;

4 Rows returned:

ID   NAME                        LATITUDE          LONGITUDE
--   ----                        --------          ---------
1    Southampton Harbour Patrol  50.80             -1.50
2    Southern Skylark            50.31             -1.15
3    Rolling Thunder             50.80             -1.53
4    Southampton Harbour Patrol2 50.78             -1.30

To create a new record, enter a position for a new transport not already present in the transports table, as shown in Figure 36.

Figure 36. Position event for insert
Position event for insert

Sending this event will create a new row in the transports table as shown in Listing 16.

Listing 16. Selecting from transports with new row
SELECT
      id,
      name,
      CAST(DB2SE.ST_X(location) AS DECIMAL(10,2)) AS latitude,
      CAST(DB2SE.ST_Y(location) AS DECIMAL(10,2)) AS longitude
FROM transports;

5 Rows returned:

ID   NAME                        LATITUDE          LONGITUDE
--   ----                        --------          ---------
1    Southampton Harbour Patrol  50.80             -1.50
2    Southern Skylark            50.31             -1.15
3    Rolling Thunder             50.80             -1.53
4    Southampton Harbour Patrol2 50.78             -1.30
5    Harold Helicopter           50.90             -1.59

Calculating the proximity to an incident

This scenario considers which transports are near an incident. Incidents will have a location, some text and a distance from the source of the incident that can be used to notify nearby transports.

Incident proximity database queries

You can use a very similar query to the one used for distances to places to obtain comparisons with stored incidents or events, as shown in Listing 17.

Listing 17. Transport proximity select
SELECT
    transports.id
  FROM transports
  WHERE DB2GSE.ST_DISTANCE(
    DB2GSE.ST_POINT(50.90,-1.42,1003),
    transports.location,'METER') < 20000;

Note: When using the METER unit in a query, spatial indexes are not used. You might want to replace the query with a coarse grain look-up that uses degrees as a distance measure that will cause the query to use the index. However, for simplicity we have not used this approach in the example project.

An example of an optimised distance query is shown in Listing 18.

Listing 18. Optimised transport proximity select
SELECT
    transports.id
  FROM transports
  WHERE DB2GSE.ST_DISTANCE(
    DB2GSE.ST_POINT(50.90,-1.42,1003),
    transports.location,'METER') < 20000
   AND
    DB2GSE.ST_DISTANCE(
    DB2GSE.ST_POINT(50.90,-1.42,1003),
    transports.location) < 0.5;

Using the scenario project, when an incident is received that is within range of the stored transports and when any other criteria specified are met, the status of a transport is updated in the transport_status table, as shown in Listing 19.

Listing 19. transport_staus table definition
CREATE TABLE transport_status
(id SMALLINT NOT NULL,
 status VARCHAR(50));

Incident proximity work in Design Data

Now we want to record the latest incident that relates to a transport in the status table. Figure 37 shows how this is achieved by calculating the distance of all transports from the incident that are within the Range in meters value, using the mapped expression to identify the transports and the SQL expression to calculate the distance.

Figure 37. Incident proximity mappings and field constructors
Incident proximity mappings and field constructors

First a SQL expression is used to retrieve the transport IDs that are affected by the incident, as shown in Figure 38.

Figure 38. SQL expression for affected transports
SQL expression for affected transports

Using this transport ID the distance is then calculated to the incident, as shown in Figure 39. This step may be required or optional, depending on the application.

Figure 39. SQL expression for distance calculation
SQL expression for distance calculation

Next you need to updated the transport_status table with the RDBMS action connection (select the RDBMS template) by configuring the Store Transport Incident action. The action connection is configured to perform an update, with the option to perform an insert if no rows are found first, implementing an upsert, as shown in Figure 40.

Figure 40. Action connection upsert configuration
Action connection upsert configuration

It's important to tick the Automatically generate multiple actions property of the Store Transport Incident action to ensure that multiple actions are created per matching transport, as shown in Figure 41. This way, multiple inserts or updates are performed to the incidents table.

Figure 41. Generate multiple actions per action object
Generate multiple actions per action object

The Maximum Occurrences property setting should be set to 1 for both the TRANSPORT_STATUS and _Rdbms action objects to ensure that pairs of these objects are created as individual actions.

Incident proximity work in the Design widget

The Proximity to Another Event interaction set in the scenario project responds to an incident event and produces an action that stores the matching incidents for the relevant transports in the transport_status table.

Incident proximity testing

Select the incident event template and create an incident event using the location of an existing place using a range large enough to cause the filter to evaluate for some of the transports in the database, as shown in Figure 42.

Figure 42. Example incident event
Example incident event

Sending the event results in three matching transports and, therefore, three subsequent actions that store the incident information, as shown in Figure 43.

Figure 43. Incident storage actions
Example incident event

Selecting FROM transport_status, as shown in Listing 20, shows the incidents stored for each relevant transport. The id field corresponds to the id of the affected transport and the status text shows the text of the incident, the related incident identifier, and how far that transport is from the incident in metres.

Listing 20. transport_status selection
SELECT * FROM transport_status

3 rows returned:

ID     STATUS
--     ------
1      Docks closed [1: 14215 meters]
3      Docks closed [1: 17317 meters]
4      Docks closed [1: 15978 meters]

Visualisation with ILog JViews Maps

The example visualisation of transports and places uses a modified sample from ILog JViews Maps 8.7, provided for download, called jsf-maps-tiled. Running the web page using an Apache Tomcat server, we were able to render the locations and status of the transport data using the transports_visual view. You can also use WebSphere Application Server for visualisation, given the correct configuration and Java™ Server Faces implementation.

In our example, incident events that are sent to the project where the incident text starts with 1, 2 or 3, indicate that the visualisation should present the incident as critical, major, or minor, respectively.

The resulting Store Transport Incident actions write to the transport_status table. Figure 44 shows the resulting visualization in the transport_visual view.

Figure 44. Visualising incidents
Visualising incidents

Figure 45 shows the incident text above the symbol for Rolling Thunder and a red icon indicating that a critical event has occurred within the threshold range of this transport.

The steps required to set up the example visualisation are described in the next section. Also, the video demo provided for download shows the example incident event being fired and the effect on the visualisation.


Solution walk-through

Refer to the Downloads section for all files relating to the solution and example scenario project.

The following sections describe steps for defining the example DB2 database using spatial extensions, connecting the example Events project, publishing the project and setting-up the example visualisation.

Set up DB2

To set up the example scenario database for DB2, do the following:

  1. Open a DB2 command line and create the spatial database using the comman: db2 -t -f db_create_script.sql. This creates a database called SPATIAL to enable you to work with spatial types.
  2. Stop and restart DB2.
  3. From the DB2 control center, right-click the SPATIAL database.
  4. Select Spatial Extender => Enable.
  5. Select the SPATIAL_8K_TS tablespace. Leave the other options blank.
  6. Run the db_spatial.sql script to set up the tables and example data used in the example scenario project.

Connect the Business Events project to DB2

To connect the Business Events project to DB2, do the following:

  1. Open the LocationAwareBEP_DB2.xml project with Business Events Design Data.
  2. Right-click the Spatial hosted database source in Data Sources and select Properties.
  3. Modify the connection to match your environment and make sure you can see the tables and views that form part of the scenario.

Publish the project

To publish the project, do the following:

  1. With the project open in Design Data, choose File => Save as project to server store, then click Save.
  2. To open the Design widget in Business Space, point your browser to the Business Space URL (for example, http://localhost:9080/BusinessSpace), log in and navigate to the Design widget.
  3. Open the LocationAwareBEP_DB2.xml file as a project in the Design widget.
  4. Select Runtime => Publish to runtime.
  5. Start the Connectors for Business Events so Business Events can write to the SPATIAL database for actions fired from the incident event and transport update event.
  6. You can now use the Tester widget to fire example event data into your project. See the provided video for exact steps and example data.

Set up visualisation

Setting up the visualisation for the example project requires that you be able to run the samples from ILOG JViews maps using the Tomcat application server provided with ILOG JViews. You'll find all files mentioned here in the Downloads section.

  1. Make a copy of the jsf-maps-tiled folder for safe keeping. An example location is C:\Program Files\IBM\ILOG\jviews-maps87\samples.
  2. Copy world.jsp into the \webpages folder under \jsf-maps-tiled.
  3. Copy wbe.idpr and wbe.css into the \data folder under \webpages in \jsf-maps-tiled.
  4. Copy newpalette.jar and newpalette2.jar to the location mentioned in wbe.css; for example, C:\.
  5. Execute the clean.bat and build.bat batch files to rebuild the jsf-maps-tiled.war file.
  6. Run the Tomcat server to reload the sample WAR files. This should pull in your newly built jsf-maps-tiled.war file.
  7. Browse to the jsf-maps-tiled sample and use the controls to zoom into the south of England and find the symbols rendered on the map. Note that in order to refresh the visualisation of the symbols on the map, you'll have to choose an empty map using the drop-down on the web page, click OK, then choose the WBE example again.

Other spatial databases

Other spatially capable database variants can be used with WebSphere Business Events simply by adapting the same queries. The following section describes how the queries can be adapted to work with Oracle Spatial.

Oracle Spatial

The SQL used in the DB2 example can be adapted to work with Oracle Spatial. For example, a point type constructed from latitude and longitude values is represented as shown in Listing 21, where 2001 is a point type geometry as defined by Oracle Spatial.

Listing 21. Oracle spatial point type
SDO_GEOMETRY(
      2001,
      NULL,
      SDO_POINT_TYPE(<longitude value>, <latitude value>, NULL),
      NULL,
      NULL)

Any field in a table using an Oracle spatial type must be added to the user_sdo_geom_metadata table, and the definition of the geometry type must be provided. Listing 22 defines a simple 200 by 200 grid for the location field of the transports table. For mapping values onto a world map, the SRID value would need to be relevant to the type of geometry required for accurately mapping positions.

Listing 22. Oracle spatial metadata entry
INSERT INTO user_sdo_geom_metadata
    (TABLE_NAME,
     COLUMN_NAME,
     DIMINFO,
     SRID)
  VALUES (
  'transports',
  'location',
  SDO_DIM_ARRAY(   -- 200X200 grid
    SDO_DIM_ELEMENT('X', 0, 200, 0.005),
    SDO_DIM_ELEMENT('Y', 0, 200, 0.005)
     ),
  NULL   -- SRID
);

Oracle also requires that an index be created on any spatial type in order to construct queries using it, as shown in Listing 23.

Listing 23. Oracle spatial index
CREATE INDEX transports_idx
   ON transports(location)
   INDEXTYPE IS MDSYS.SPATIAL_INDEX;

Conclusion

In this article, you learned how you can use WebSphere Business Events to enable location-aware business event processing, using real-world examples that implement geofence detection and proximity alerting.


Downloads

DescriptionNameSize
WBE project XML file and DB2 scripts1wbe_project_and_database_setup.zip11KB
Ilog Jviews Maps setup files2jsf-maps-tiled.zip46KB
Video demo3wbe_location_aware_video.zip11132KB

Notes

  1. Contains the WBE project implementation to be connected to DB2 using Design Data. The DB2 scripts can be used to set up the database, including seeded data and example queries.
  2. Contains only the files required to replace the sample files in the jsf-maps-tiled visualisation example in order to visualise the example scenario data from the DB2 set-up scripts, and subsequent updates to it from any actions fired by the WBE project.
  3. Contains an mp4 format video and embedded web player showing an example event and actions with visualisation.

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into WebSphere on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=WebSphere, Information Management
ArticleID=617875
ArticleTitle=Enable location-aware event processing with WebSphere Business Events
publish-date=01192011