Example 8-1 Location Tracking Server Workflow

-- Create a tracking set named sample with one tracker/process
-- queue pair and one location queue.
EXEC sdo_trkr.create_tracking_set('sample', 1, 1);

-- Optional: Show the tracking sets tables that were created
SELECT table_name 
FROM user_tables 
WHERE table_name LIKE ‘SAMPLE%’
ORDER BY table_name;

TABLE_NAME
----------------------
SAMPLE_LOCATION_QT_1    - AQ queue table for location queue
SAMPLE_NOTIFICATIONS    - Auxiliary table to store notification messages
SAMPLE_NOTIFICATION_QT  - AQ queue table for the notification queue 	
SAMPLE_PROC_QT_1        - AQ queue table for the process queue
SAMPLE_TRACKER          - Table, will contain object-region tracking pairs
SAMPLE_TRACKER_LOG      - Table, contains log messages from the server 
SAMPLE_TRACKER_QT_1     - AQ queue table for the tracker queue
SAMPLE_TRACKER_QUEUES   - Table, contains tracking sets queue metadata
SAMPLE_TRACKING_REGIONS - Table, will contain the regions geometry
SAMPLE_TRAJECTORY       - Table, currently unused

-- Start the tracking set
EXEC sdo_trkr.start_tracking_set(‘sample’);

-- Optional: Show the queues used by the tracking set
SELECT name
FROM user_queues
WHERE name LIKE 'SAMPLE%'
ORDER BY name;

NAME
---------------------
SAMPLE_LOCATION_Q_1
SAMPLE_NOTIFICATION_Q
SAMPLE_PROC_Q_1
SAMPLE_TRACKER_Q_1

-- Optional: Show the scheduler jobs used by the tracking set
SELECT job_name, state 
FROM user_scheduler_jobs 
WHERE job_name LIKE 'SAMPLE%'
ORDER BY job_name;

JOB_NAME	    STATE
--------------------------
SAMPLE_LOC_JOB_1   RUNNING
SAMPLE_TRKR_JOB_1  RUNNING


-- Insert a polygon for region 1. This polygon must be geodetic (using SRID 4326) 
-- and two dimensional. The region may also be a multi-polygon.
INSERT INTO SAMPLE_TRACKING_REGIONS VALUES (1,
  MDSYS.SDO_GEOMETRY(SDO_POLYGON2D, 4326, null,
    sdo_elem_info_array(1, 1003, 1),
    sdo_ordinate_array(0,0, 5,0, 5,5, 0,5, 0,0)));
-- Create two objects, object 1 and 2 that are tracked in region 1.
-- Object 1 sends notification messages when it is inside region 1. 
-- Object 2 sends notification messages when it is outside region 1.
EXEC sdo_trkr.send_tracking_msg(
  'SAMPLE', mdsys.tracker_msg(1, 1, 'I'));
EXEC sdo_trkr.send_tracking_msg(
  'SAMPLE', mdsys.tracker_msg(2, 1, 'O'));
-- Optional: Show the object-region pairs used in the tracking set
SELECT object_id, region_id, alert_when  FROM sample_tracker;

OBJECT_ID  REGION_ID  ALERT_WHEN
---------- ---------- -----------
     1	         1        I
     2	         1        O

-- Send 2 location messages.
–- Object 1 moves to (1, 1).
–- Object 2 moves to (8, 8).
EXEC sdo_trkr.send_location_msgs('SAMPLE',
  mdsys.location_msg_arr(
    mdsys.location_msg(1, '01-AUG-16 01.01.46.000000 PM', 1, 1),
    mdsys.location_msg(2, '01-AUG-16 01.02.46.000000 PM', 8, 8)));

-- Optional: Show that 2 notification message were generated
SELECT a.name, b.ready
FROM user_queues a, v$aq b 
WHERE a.name='SAMPLE_NOTIFICATION_Q' AND a.qid=b.qid
ORDER BY a.name;

NAME                   READY
---------------------- ------
SAMPLE_NOTIFICATION_Q    2

-- Dequeue the notification messages into the notifications table
DECLARE
  message       mdsys.notification_msg;
BEGIN
  LOOP   
    sdo_trkr.get_notification_msg(
      tracking_set_name => 'SAMPLE', 
      message => message, 
      deq_wait =>2);	-- wait at most 2 seconds for a message

    IF (message IS NULL) THEN
      EXIT;
    END IF;

    INSERT INTO sample_notifications (
                  object_id, region_id, time, x, y, state) 
      (SELECT message.object_id, message.region_id, 
              message.time, message.x, message.y, message.state);
  END LOOP;
END;
-- Query the object id, region id, (x, y) coordinate and the objects
-- relationship to the region sorted by the time that was sent with
-- the objects location message.
SELECT object_id, region_id, x, y, state 
FROM sample_notifications 
ORDER BY time; 
OBJECT_ID  REGION_ID	X   Y   STATE
---------- ---------- --- --- -------
  1	      1	        1   1   INSIDE
  2	      1	        8   8   OUTSIDE
-- Optional: Disable the tracking server's object-region pairs
EXEC sdo_trkr.send_tracking_msg('SAMPLE', 
  mdsys.tracker_msg(1, 1, 'D'));
EXEC sdo_trkr.send_tracking_msg('SAMPLE', 
  mdsys.tracker_msg(2, 1, 'D'));
-- Stop the tracking set. This stops the tracking sets
-- queues and its scheduler jobs. Running stop_tracking_set 
-- does not delete the tables and queues used by the tracking
-- server so start_tracking_set can be rerun and all of the 
-- object and region data is still available.
-- This must be done before dropping a tracking set
EXEC sdo_trkr.stop_tracking_set('sample');

-- Drop the tracking set. This completely deletes the tracking
-- sets queues and tables. Once completed all traces of the tracking
-- set are removed except for the log table which is left intact for
-- debugging purposes. If another tracking set of the same name is
-- created the log table is truncated.
EXEC sdo_trkr.drop_tracking_set('sample');