17Nov
open-data-oracle-apex

Plotting Open Data with Oracle APEX

By: Adrian Png On: November 17, 2016 In: APEX Developer Solutions Comments: 0

The BC DevExchange is an initiative by the Provincial Government of British Columbia to encourage innovation and entrepreneurship in the technology sector. The vision is to provide open access to provincial and municipal data, allowing enterprising companies and individuals to create and commercialize services that leverage on these digital assets. Our Vancouver Oracle Developer Day (VODD) lunchtime demonstration, was to showcase the use of the BC DevExchange’s suite of OpenData and OpenAPI to create a meaningful application using Oracle Application Express (APEX).  We chose to demonstrate Plotting Open Data with Oracle APEX. The three key features that we would provide are:

  1. Search and view historical Open511 data collected by DriveBC.
  2. View current Open511 events.
  3. Allow B.C. residents to report relevant events.

Aside from these critical requirements, our customer”assumes that the web application would be user-friendly, modern and that it would be ready… YESTERDAY!

Easily Ingesting Open Data with Oracle APEX

We started off with the no-code segment by importing the DriveBC historical events, specifically from 2015. It is no surprise that after a year of data collection, these files are enormous. For the purpose of our proof-of-concept, we decided to trim the data down to the last ten thousand events.

Uploading and importing the data into Oracle APEX is a piece of cake. The platform comes with a suite of tools in SQL Workshop that allows you to perform many data manipulation tasks, e.g. creating tables, building queries and import data! The data import utility is a wizard-driven interface that allows you to either upload or copy and paste data from a typical spreadsheet or CSV file.

Oracle APEX SQL Workshop Tools

There are a lot of online articles and training videos about how simple the process is to go from spreadsheet to web application, so I will not delve into the details. Incidentally, Monty Latiolais wrote a step-by-step guide to lead you from Excel spreadsheet to web application.

Visualizing the Data on a Map

DriveBC Historical Events Interactive Report

The Interactive Report (IR) is a feature-rich report region that allows users to view, search and perform computations on a table or view in the database. Reports can also be morphed into meaningful charts. However, some types of data, especially those containing geolocation information, are probably best viewed on a map.

For our demo, we opted to use Leaflet JavaScript library to visualise the events on a map. Leaflet is lightweight, mobile-friendly and can be easily integrated into an APEX application. We chose OpenStreetMap to provide free map tiles.

To do that, we added Page 2 and an empty DIV tag to start. This region is assigned a static ID for convenience. The hosted Leaflet JS and CSS libraries were then added to the page.

Javascript File URLs:

//unpkg.com/leaflet@1.0.1/dist/leaflet.js

Function and Global Variable Declaration:

var map, drivebc_events;

CSS File URLs:

//unpkg.com/leaflet@1.0.1/dist/leaflet.css

Next, a Page Load Dynamic Action (DA) was added to initialise the map. The DA execute a block of JS code to load markers, add map tiles and set the properties of the map:

// Create the map and set the initial location to the City of Vancouver.
map = L.map(this.affectedElements.attr('id')).setView([49.246292, -123.116226], 12);

// Add a feature group to manage markers by event types.
drivebc_events = L.featureGroup();

// Add the map tiles
L.tileLayer(
  'https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png',
  {
    maxZoom: 18,
    minZoom: 8,
    attribution: 'Map data © <a href="http://openstreetmap.org">OpenStreetMap</a> contributors'
 }).addTo(map);

// Add the markers
map.addLayer(drivebc_events);

// AJAX call to load events based on the historical event source
apex.server.process(
  'LOAD_DRIVEBC_EVENTS',
  {
    x01: map.getCenter().lng,
    x02: map.getCenter().lat
  },
  {
    success: function(pData) {
      var events = pData.events;
      for (var i = 0; i < events.length; i++) {
        var event = events[i];
        L.marker([event.latitude, event.longitude], { title: event.event_name })
          .bindPopup('<p>' + (event.description !== undefined ? event.description : 'No advisory message') + '</p>')
          .addTo(drivebc_events);
      }
    }
  }
);

To limit the search based on the centre of the mapview, we first needed to add a new column that is of SDO_GEOMETRY datatype and populate it from the GeoJSON-style point coordinates stored in the column geometry.

alter table eventshist add (geom sdo_geometry);

update eventshist
set geom = sdo_geometry(
  2001
  , 8307
  , SDO_POINT_TYPE(
    to_number(regexp_replace(geometry, 'POINT \((.+?) .+\)', '\1'))
    , to_number(regexp_replace(geometry, 'POINT \(.+ (.+?)\)', '\1') )
    , null
  )
  , null
  , null
);

commit;

To allow querying by spatial operators, the column must first be indexed.

delete from user_sdo_geom_metadata
where table_name = 'EVENTSHIST' and column_name = 'GEOM';

insert into user_sdo_geom_metadata (table_name, column_name, diminfo, srid)
values (
  'eventshist'
  , 'geom'
  , mdsys.sdo_dim_array(
    mdsys.sdo_dim_element('x', -180.000000000, 180.000000000, 0.500000000)
    , mdsys.sdo_dim_element('y', -90.000000000, 90.000000000, 0.500000000)
  )
  , 8307
);

commit;

create index eventshist_spatial_idx on eventshist(geom) indextype is mdsys.spatial_index;

Last, but not least, add an AJAX Callback process named LOAD_DRIVEBC_EVENTS that would provide the results in a JSON format. As noted by Dan McGhan during our demonstration, Oracle APEX 5.0 provides an API out-of-the-box to encode and parse valid JSON. To keep the number of results returned manageable, we added a spatial filter to restrict the events to within 100 kilometers from the centre of the viewable map area.

declare
  type t_events is table of eventshist%rowtype;
  l_events t_events;
  l_center_lng number := apex_application.g_x01;
  l_center_lat number := apex_application.g_x02;
begin
  select * 
  bulk collect into l_events
  from eventshist
  where 1 = 1
    and l_center_lng is not null 
    and l_center_lat is not null 
    and sdo_within_distance(
      geom
      , sdo_geometry(2001, 8307, sdo_point_type(l_center_lng, l_center_lat, null), null, null)
      , 'distance=100 unit=km'
    ) = 'TRUE');
 
  apex_json.open_object; 
  apex_json.open_array('events');
 
  for i in 1..l_events.count loop
    apex_json.open_object;
    apex_json.write('event_id', l_events(i).id);
    apex_json.write('event_name', l_events(i).cause);
    apex_json.write('description', l_events(i).advisorymessage);
    apex_json.write('longitude', l_events(i).geom.sdo_point.x);
    apex_json.write('latitude', l_events(i).geom.sdo_point.y);
    apex_json.close_object;
  end loop;
 
  apex_json.close_all;
end;

At this point, the map will not be visible. One requirement was to specify a height attribute for the DIV containing the map. Another DA was added to run two lines of JS code to extend fill the entire content area on the right. The code would also execute on page load:

// Set the map's width and height
this.affectedElements.height($(window).height() - $('#t_Header').height());

// Invalidating the map will force it to redraw.
map.invalidateSize();

There is however, one more issue to resolve. There was still some padding surrounding the map that had to be removed.

Map Interface Displaying Gaps

Nothing a few lines of CSS could not solve:

#t_Body_content {
  padding-bottom: 0;
}

#t_Body_content .t-Body-contentInner {
  padding: 0;
}

Adding a Live Feed

DriveBC provides an Open511 API for developers to pull live event data. See the online documentation for more details on what methods and their parameters that can be called. For the demo, we used the events method and limited the results to three parameters: status limit and bbox.

NOTE:

  1. Though the REST API could be called directly using JavaScript, the following implementation was done to demonstrate how the APEX_WEB_SERVICE API can be used to interact/integrate with third-party web services.
  2. It also showcases the use of the APEX_JSON API for parsing JSON. It is worthwhile to also note that in Oracle 12c, there are more optimal approaches for querying and extracting data from JSON.

Give the new markers a different coloured marker. Add the code (in bold) to the Function and Global Variable Declaration on page 2:

var map, drivebc_events, live_events;

var pinkMarker = L.icon({
  iconUrl: '#APP_IMAGES#img/map-marker-icon-pink.png',
  iconSize: [32, 32],
  iconAnchor: [16, 32],
  popupAnchor: [0, -28]
});

Then add the following JS code to Page Load DA for initialising the map.

// Create the map and set the initial location to the City of Vancouver.
map = L.map(this.affectedElements.prop('id')).setView([49.246292, -123.116226], 12);

// Add a feature group to manage markers by event types.
drivebc_events = L.featureGroup();
live_events = L.featureGroup();

// Add the map tiles
L.tileLayer(
  'https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png',
  {
    maxZoom: 18,
    minZoom: 8,
    attribution: 'Map data © <a href="http://openstreetmap.org">OpenStreetMap</a> contributors'
  }).addTo(map);

// Add the markers
map.addLayer(drivebc_events);
map.addLayer(live_events);

// AJAX call to load events based on the historical event source
apex.server.process(
  'LOAD_DRIVEBC_EVENTS',
  {
    x01: map.getCenter().lng,
    x02: map.getCenter().lat
  },
  {
    success: function(pData) {
      var events = pData.events;
      for (var i = 0; i < events.length; i++) {
        var event = events[i];
        L.marker([event.latitude, event.longitude], { title: event.event_name })
          .bindPopup('<p>' + (event.description !== undefined ? event.description : 'No advisory message') + '</p>')
          .addTo(drivebc_events);
      }
    }
  }
);

// AJAX call to load live events from DriveBC Open511 API
apex.server.process(
  'LOAD_LIVE_EVENTS',
  {
    x01: 100,
    x02: map.getBounds().getWest(),
    x03: map.getBounds().getSouth(),
    x04: map.getBounds().getEast(),
    x05: map.getBounds().getNorth()
  },
  {
    success: function(pData) {
      var events = pData.events;
      for (var i = 0; i < events.length; i++) {
        var event = events[i];
        L.marker([event.latitude, event.longitude], { icon: pinkMarker, title: event.event_name })
          .bindPopup('<p>' + (event.description !== undefined ? event.description : 'No advisory message') + '</p>')
          .addTo(live_events);
      }
    }
  }
);

Add a new AJAX Callback process named LOAD_LIVE_EVENTS that would make a REST call to the API, parse it and then return the results in an appropriate JSON format:

declare
  l_result clob;
  l_values apex_json.t_values;
  l_limit number := apex_application.g_x01;
  l_xmin number := apex_application.g_x02;
  l_ymin number := apex_application.g_x03;
  l_xmax number := apex_application.g_x04;
  l_ymax number := apex_application.g_x05;
begin
  l_result := apex_web_service.make_rest_request(
    p_url => 'http://api.open511.gov.bc.ca/events'
    , p_http_method => 'GET'
    , p_parm_name => apex_util.string_to_table('status:limit' 
      || case
           when l_xmin is not null and l_ymin is not null and l_xmax is not null
             and l_ymax is not null then ':bbox'
           else null
         end
    )
    , p_parm_value => apex_util.string_to_table('ACTIVE:'
      || l_limit
      || case
           when l_xmin is not null and l_ymin is not null and l_xmax is not null
             and l_ymax is not null then 
               ':' || l_xmin || ',' || l_ymin || ',' || l_xmax || ',' || l_ymax
           else null
         end 
    )
  );
 
  apex_json.parse(
    p_values => l_values
    , p_source => l_result
  );
 
  apex_json.open_object;
  apex_json.open_array('events');
 
  for i in 1..apex_json.get_count(p_path => 'events', p_values => l_values) loop
    apex_json.open_object;
    apex_json.write(
      p_name => 'event_id'
      , p_value => apex_json.get_varchar2(
        p_path => 'events[%d].id'
        , p0 => i
        , p_values => l_values
      )
    );
    apex_json.write(
      p_name => 'event_name'
      , p_value => apex_json.get_varchar2(
        p_path => 'events[%d].headline'
        , p0 => i
        , p_values => l_values
      )
    );
    apex_json.write(
      p_name => 'description'
      , p_value => apex_json.get_varchar2(
        p_path => 'events[%d].description'
        , p0 => i
        , p_values => l_values
      )
    );
    if 'POINT' = upper(apex_json.get_varchar2(
      p_path => 'events[%d].geography.type'
      , p0 => i
      , p_values => l_values
    )) then 
      apex_json.write(
        p_name => 'longitude'
        , p_value => apex_json.get_number(
          p_path => 'events[%d].geography.coordinates[%d]'
          , p0 => i
          , p1 => 1
          , p_values => l_values
        )
      );
      apex_json.write(
        p_name => 'latitude'
        , p_value => apex_json.get_number(
          p_path => 'events[%d].geography.coordinates[%d]'
          , p0 => i
          , p1 => 2
          , p_values => l_values
        )
      );
    else
      apex_json.write(
        p_name => 'longitude'
        , p_value => apex_json.get_number(
          p_path => 'events[%d].geography.coordinates[%d][%d]'
          , p0 => i
          , p1 => 1
          , p2 => 1
          , p_values => l_values
        )
      );
      apex_json.write(
        p_name => 'latitude'
        , p_value => apex_json.get_number(
          p_path => 'events[%d].geography.coordinates[%d][%d]'
          , p0 => i
          , p1 => 1
          , p2 => 2
          , p_values => l_values
        )
      );
    end if;
    apex_json.close_object;
  end loop;
 
  apex_json.close_all;
end;

Crowdsourcing Incident Reports

Adding a form to collect reports submitted by BC residents allows city officials to track more events and make better decisions. To achieve this, some data objects need to be created. Here’s a simple example:

create table citizen_events(
  event_id number not null
  , event_name varchar2(200) not null
  , description varchar2(500)
  , event_date timestamp with local time zone not null
  , geom sdo_geometry
  , constraint citizen_events_pk primary key (event_id)
);

create sequence citizen_events_seq start with 1 increment by 1 order nocycle;

create or replace trigger citizen_reports_biu before insert or update on citizen_events
for each row
begin
  if inserting then 
    if :new.event_id is null then
      :new.event_id := citizen_events_seq.nextval;
    end if;
  end if;
end;
/

Next, create a modal page for entering the relevant fields. Then, add a button so that the user may choose to use the current location rather than the values set by the mouse click on Page 2. A DA is then used to populate the coordinates using the browser geolocation API:

var $fields = this.affectedElements;

// Update the coordinates using the geolocation API 
navigator.geolocation.getCurrentPosition(function(position) {
  apex.item($fields[0].id).setValue(position.coords.latitude);
  apex.item($fields[1].id).setValue(position.coords.longitude);
});

Create the necessary CRUD processes to persist the changes to the database. We will also need a Close Dialog process to help indicate to the parent page that the change is complete and the modal dialog closed.

Back on Page 2, create a page item P2_REPORT_URL with a PL/SQL Expression source that executes the function to return the URL that would open the modal dialog:

apex_page.get_url(
  p_page => 3
  , p_clear_cache => 3
  , p_items => 'P3_LAT,P3_LNG'
  , p_values =>'_LAT_,_LNG_'
)

Create a DA Load citizen report form that is activated by a click event on the map.

var url = apex.item('P2_REPORT_URL').getValue()
  .replace('this', '$(\'#mymap\')')
  .replace('_LAT_', this.browserEvent.originalEvent.latlng.lat)
  .replace('_LNG_', this.browserEvent.originalEvent.latlng.lng);

window.location = url;

Give the new markers a different coloured marker. Add the code to the Function and Global Variable Declaration on page 2:

var map, citizen_events, drivebc_events, live_events;

var redMarker = L.icon({
  iconUrl: '#APP_IMAGES#img/map-marker-icon-red.png',
  iconSize: [32, 32],
  iconAnchor: [16, 32],
  popupAnchor: [0, -28]
});

var pinkMarker = L.icon({
  iconUrl: '#APP_IMAGES#img/map-marker-icon-pink.png',
  iconSize: [32, 32],
  iconAnchor: [16, 32],
  popupAnchor: [0, -28]
});

Update the DA Initialize map:

// Create the map and set the initial location to the City of Vancouver.
map = L.map(this.affectedElements.prop('id')).setView([49.246292, -123.116226], 12);

// Add a feature group to manage markers by event types.
citizen_events = L.featureGroup();
drivebc_events = L.featureGroup();
live_events = L.featureGroup();

// Add the map tiles
L.tileLayer(
  'https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png',
  {
    maxZoom: 18,
    minZoom: 8,
    attribution: 'Map data © <a href="http://openstreetmap.org">OpenStreetMap</a> contributors'
  }
).addTo(map);

// Add the markers
map.addLayer(citizen_events);
map.addLayer(drivebc_events);
map.addLayer(live_events);

// AJAX call to load citizen events
apex.server.process(
  'LOAD_CITIZEN_EVENTS',
  {},
  {
    success: function(pData) {
      var events = pData.events;
      for (var i = 0; i < events.length; i++) {
        var event = events[i];
        L.marker([event.latitude, event.longitude], { icon: redMarker, title: event.event_name })
          .bindPopup('<p>' + (event.description !== undefined ? event.description : 'No advisory message') + '</p>')
          .addTo(citizen_events);
      }
    }
  }
);

// AJAX call to load events based on the historical event source
apex.server.process(
  'LOAD_DRIVEBC_EVENTS',
  {
    x01: map.getCenter().lng,
    x02: map.getCenter().lat
  },
  {
    success: function(pData) {
      var events = pData.events;
      for (var i = 0; i < events.length; i++) {
        var event = events[i];
        L.marker([event.latitude, event.longitude], { title: event.event_name })
          .bindPopup('<p>' + (event.description !== undefined ? event.description : 'No advisory message') + '</p>')
          .addTo(drivebc_events);
      }
    }
  }
);

// AJAX call to load live events from DriveBC Open511 API
apex.server.process(
  'LOAD_LIVE_EVENTS',
  {
    x01: 100,
    x02: map.getBounds().getWest(),
    x03: map.getBounds().getSouth(),
    x04: map.getBounds().getEast(),
    x05: map.getBounds().getNorth()
  },
  {
    success: function(pData) {
      var events = pData.events;
      for (var i = 0; i < events.length; i++) {
        var event = events[i];
        L.marker([event.latitude, event.longitude], { icon: pinkMarker, title: event.event_name })
          .bindPopup('<p>' + (event.description !== undefined ? event.description : 'No advisory message') + '</p>')
          .addTo(live_events);
      }
    }
  }
);

Create an AJAX Callback process named LOAD_CITIZEN_EVENTS to generate the JSON:

declare
  type t_events is table of citizen_events%rowtype;
  l_events t_events;
begin
  select * 
  bulk collect into l_events
  from citizen_events;
 
  apex_json.open_object;
  apex_json.open_array('events');
 
  for i in 1..l_events.count loop
    apex_json.open_object;
    apex_json.write('event_id', l_events(i).event_id);
    apex_json.write('event_name', l_events(i).event_name);
    apex_json.write('description', l_events(i).description);
    apex_json.write('longitude', l_events(i).geom.sdo_point.x);
    apex_json.write('latitude', l_events(i).geom.sdo_point.y);
    apex_json.close_object;
  end loop;
 
  apex_json.close_all;
exception
  when no_data_found then
  apex_json.open_object;
  apex_json.open_array('events');
  apex_json.close_all;
end;

Create a Refresh map DA with the following JS to reload the layer for citizen report markers:

apex.server.process(
  'LOAD_CITIZEN_EVENTS',
  {},
  {
    success: function(pData) {
      var events = pData.events;
      map.removeLayer(citizen_events);
      citizen_events = L.featureGroup();
      for (var i = 0; i < events.length; i++) {
        var event = events[i];
        L.marker([event.latitude, event.longitude], { icon: redMarker, title: event.event_name })
          .bindPopup('<p>' + (event.description !== undefined ? event.description : 'No advisory message') + '</p>')
          .addTo(citizen_events);
      }
      map.addLayer(citizen_events);
    }
  }
);

Filter Map with IR Criteria

Add the button View on Map on page 1. Create the Page Item P2_DRIVEBC_SOURCE on page 2 and set the default value to LOAD_DRIVEBC_EVENTS. This value is overridden with the value LOAD_REPORT_EVENTS when the user clicks the button View on Map.

Update the DA Initialize Map

...
// AJAX call to load events based on the historical event source
apex.server.process(
  apex.item('P2_DRIVEBC_SOURCE').getValue()
  {
    x01: map.getCenter().lng,
    x02: map.getCenter().lat
  }
...

Create the AJAX Callback process named LOAD_REPORT_EVENTS:

declare
  type t_events is table of eventshist%rowtype;
  l_events t_events;
 
  l_region_id number;
  l_report_id number;
  l_report apex_ir.t_report;
  l_page_id number := 1;
  l_query varchar2(32767);
  l_sql varchar2(32767);
begin
  select region_id 
  into l_region_id
  from apex_application_page_regions 
  where application_id = :APP_ID 
  and page_id = l_page_id
  and static_id = 'eventshist_report';
 
  l_report_id := apex_ir.get_last_viewed_report_id(
    p_page_id => l_page_id
    , p_region_id => l_region_id
  );
 
  l_report := apex_ir.get_report(
    p_page_id => l_page_id
    , p_region_id => l_region_id
    , p_report_id => l_report_id
  );
 
  l_query := l_report.sql_query;
 
  for i in 1..l_report.binds.count loop
    l_query := replace(
      l_query
      , ':' || l_report.binds(i).name
      , '''' || l_report.binds(i).value || ''''
    );
  end loop;

  l_sql := q'[
 select * 
 from eventshist
 where event_id in (
   select event_id 
   from (
     #REPORT_SQL#
   )
 )
  ]';
 
 l_sql := replace(
   l_sql
   , '#REPORT_SQL#'
   , l_query
 );
 
 execute immediate l_sql bulk collect into l_events;
 
 apex_json.open_object; 
 apex_json.open_array('events');
 
  for i in 1..l_events.count loop
    apex_json.open_object;
    apex_json.write('event_id', l_events(i).id);
    apex_json.write('event_name', l_events(i).cause);
    apex_json.write('description', l_events(i).advisorymessage);
    apex_json.write('longitude', l_events(i).geom.sdo_point.x);
    apex_json.write('latitude', l_events(i).geom.sdo_point.y);
    apex_json.close_object;
  end loop;
 
  apex_json.close_all;
end;

Update the map when the user moves the map:

if (apex.item('P2_DRIVEBC_SOURCE').getValue().toUpperCase() === 'LOAD_DRIVEBC_EVENTS') {
  apex.server.process(
    'LOAD_DRIVEBC_EVENTS',
    {
      x01: map.getCenter().lng,
      x02: map.getCenter().lat
    },
    {
      success: function(pData) {
        var events = pData.events;
        map.removeLayer(drivebc_events);
        drivebc_events = L.featureGroup();
        for (var i = 0; i < events.length; i++) {
          var event = events[i];
          L.marker([event.latitude, event.longitude], { title: event.event_name })
            .bindPopup('<p>' + (event.description !== undefined ? event.description : 'No advisory message') + '</p>')
            .addTo(drivebc_events);
        }
        map.addLayer(drivebc_events);
      }
    }
  );
}

apex.server.process(
  'LOAD_LIVE_EVENTS',
  {
    x01: 100,
    x02: map.getBounds().getWest(),
    x03: map.getBounds().getSouth(),
    x04: map.getBounds().getEast(),
    x05: map.getBounds().getNorth()
  },
  {
    success: function(pData) {
      var events = pData.events;
      map.removeLayer(live_events);
      live_events = L.featureGroup();
      for (var i = 0; i < events.length; i++) {
        var event = events[i]; 
        L.marker([event.latitude, event.longitude], { icon: pinkMarker, title: event.event_name })
          .bindPopup('<p>' + (event.description !== undefined ? event.description : 'No advisory message') + '</p>')
          .addTo(live_events);
      }
      map.addLayer(live_events);
    }
  }
);

Simultaneously Update Everyone’s Screen

The Map

The final feature to implement was to ensure that everyone who is viewing the map screen gets a real time update when new crowdsourced reports are added. To achieve this, we used Socket.io that provides WebSockets support.

NOTE: There are some software requirements for this part of the project. If you are developing with an OXAR instance, then all the needed software is already available in your server.

To get started, make sure that you have the latest stable version of Node.js installed. Once that’s done, execute the following in a terminal:

> mkdir vodd
> cd vodd
> npm init
# Enter the requested information to initialise the project
> npm install --save socket.io

The WS server application code:

var io = require('socket.io')(3000, {
  path: '/vodd/socket.io'
});

io.on('connection', function(socket) {
  socket.on('refreshMap', function(){
    io.emit('refreshMap');
  });
});

The latest release of OXAR includes pm2 that is an excellent tool for running Node.js-based apps as daemons. To run this WS service, execute the following command whilst remaining in the project root directory:

> pm2 start app.js --name VODD
> pm2 save

We use node4ords for proxying web requests. To proxy the WS, add the following code to the app.js file in the node4ords root directory.

app.use('/vodd', proxy(
  {
    target: 'http://localhost:3000',
    changeOrigin: true,
    ws: true
  }
));

Restart the node4ords service:

> pm2 restart node4ords

For the APEX application, first add two Substitution Strings: G_WS_SERVER and G_WS_SERVICE_PATH. The G_WS_SERVER should point to the URL of the WS server. The G_WS_SERVICE_PATH would be the path used when creating the WS service.

On page 2, add the JS file reference:

&G_WS_SERVER.&G_WS_SERVICE_PATH./socket.io.js

Add the socket.io client-side code to the Function and Global Variable Declaration:

var socket = io('&G_WS_SERVER.', { path: '&G_WS_SERVICE_PATH.' });

socket.on('refreshMap', function() {
  apex.event.trigger('#mymap', 'refreshMap');
});

Reconfigure the DA Refresh map to a custom event using the values in this figure:

Settings for Custom Dynamic Action Refresh Map

In its place, create a new DA Notify changed to listen for a Dialog Closed event. Enter the JS code:

socket.emit('refreshMap');

The Report

To create similar functionality on page 1, we added a new Classic Report with static ID citizen-events-report that list all the citizen-reported events. This allows update and deletion of existing events. When that happens, the report and any maps currently viewed by other users, need to be refreshed.

To do that, add the same reference to the socket.io.js and the following client-side code:

var socket = io('&G_WS_SERVER.', { path: '&G_WS_SERVICE_PATH.' });

socket.on('refreshMap', function() {
  apex.event.trigger('#citizen-events-report', 'refreshMap');
});

For simplicity, note that we are listening to the same WS event refreshMap.

Add a DA Refresh citizen events report to refresh the report region using the following conditions:

Settings for Custom Dynamic Action Refresh Citizen Events Report

Finally, add a DA Notify changed that executes the following JS code:

socket.emit('refreshMap');

Download a copy of the application export file. Please note that installing supporting objects will create the necessary database objects, but after installing the app in your environment, you will need to:

  1. Download and import the data from the BC Data Catalogue.
  2. Create the spatial index on the column EVENTSHIST.GEOM; and
  3. Set up the WebSockets server and web proxy.

That’s it! Hopefully, this would inspire you to look at other types of OpenData and OpenAPI from the BC Dev Exchange, and create some innovative ideas and products!

Share this:
Share

Leave reply:

Your email address will not be published. Required fields are marked *