Integrating Denodo with ArcGIS Online via GeoJSON

Applies to: Denodo 8.0
Last modified on: 17 Nov 2020
Tags: ArcGIS External clients GeoJSON Web Services

Download document

You can translate the document:

Overview

ArcGIS Online is a cloud-based mapping and analysis solution, part of the Esri Geospatial Cloud. This web application allows you to make maps, analyze data, and to share and collaborate. The integration of the Denodo Platform with ArcGIS Online allows this web service to consume Denodo data and use it in its functionalities.

Exposing geospatial data

GeoJSON

GeoJSON is an open-standard geospatial data interchange format, based on JavaScript Object Notation (JSON), that represents simple geographic features and their nonspatial attributes.

GeoJSON in Denodo

Although Denodo Virtual DataPort does not include native support for spatial data types, it is possible to deal with spatial data from any system that follows the Simple Features standard using custom functions. For more information, see the Generic Spatial Data Types Support document in the Denodo Knowledge Base. Note that, in addition to offering the possibility of developing your own custom functions, Denodo offers, among the DenodoConnect Enterprise components, the Denodo Xtrafuncs for VDP library that extends the set of available functions by default adding several custom date, encryption, spatial and string functions.

In addition, Denodo also offers the Denodo GeoJSON Service, a web service that can be used to query the views within a Denodo Virtual DataPort Server and return results in the form of GeoJSON objects that can be consumed by other applications. If you have defined a field with geometric data in a Denodo view, its name can be suffixed with  “wkb” or “wkt” (case insensitive), denoting a formal binary representation of the geometry (Well-Known Binary) and a formal textual representation of the geometry (Well-Known Text) respectively. A field following this naming convention will automatically be processed as a geometric type in the GeoJSON output.

GeoJSON in ArcGIS Online

ArcGIS Online allows you to use GeoJSON files in your ArcGIS portal as an item or as a layer in Map Viewer. However, it is not possible to add as a layer a service whose response uses the GeoJSON format. This limitation means that ArcGIS Online is unable to consume the GeoJSON data provided by the Denodo GeoJSON Service.

Under these conditions, the consumption of GeoJSON is limited to the static scope whereas if you were able to use a layer linked to a service, you would be able to set a refresh interval to keep your maps in sync with the latest data provided by the service.

This article will explain a software setup that allows you to overcome this limitation.

GeoServices specification

The GeoServices specification provides a standard way for Web clients to communicate with geographic information system (GIS) servers through Representational State Transfer (REST) technology. The ArcGIS family of products offers support for this REST-based application programming interface.

ArcGIS Online: consuming geospatial data from the Denodo Platform

The Denodo Platform can use GeoJSON in order to publish geospatial data, as explained in the GeoJSON in Denodo section. On the other hand, ArcGIS Online does not support consuming services that return the response in GeoJSON format (see the GeoJSON in ArcGIS Online section for more information) but it provides support for the GeoServices specification (see the GeoServices specification section). In this scenario, you will be able to consume Denodo Platform data from ArcGIS Online by translating it into the GeoServices specification supported by ArcGIS products. For this purpose, you can use Koop.

Koop is a Node.js web-server for on-the-fly transformation of geospatial data. This means that using Koop, the GeoJSON returned by the Denodo GeoJSON Service can be reshaped according to the GeoServices specification (also known as ArcGIS Feature Server API), and in this way enable ArcGIS Online to consume geospatial data exposed by Denodo. Note that, following the underlying principle of Koop, the data remains in its native format and location but it can be transformed and served to make it consumable by ArcGIS Online.

Developing a Koop Denodo provider

To develop a Koop provider, you can use the Koop CLI tool which provides a quick way to build and launch Koop applications. Following the installation instructions, in addition to installing the tool, you can create a new application:

koop new app denodo-app

The next step involves the development of a new Koop provider to transform the GeoJSON data from the Denodo GeoJSON Service to GeoServices specification. To achieve this aim, there is an example project available that you can checkout and use as a template.

In the Koop provider file structure there are two mandatory files:

  • src/index.js. Its purpose is to tell Koop how to load and use the provider. You have to specify these keys:

  • type: Identifies the plugin type. It should have the value 'provider'.
  • name: A URL safe string that identifies the provider. It is used as part of the routes to provider data.
  • Model: The Model class or use the builtin require function to include the Model class module defined in a separate file.
  • version: Version number of the provider. It can reference a value specified in the package.json file.

const denodo = {

  type: 'provider',

  name: 'denodo',

  Model: require('./model'),

  version: require('../package.json').version

}

module.exports = denodo

  • src/model.js. Its primary job is to fetch data from the remote source, the Denodo Platform, and return GeoJSON to Koop for further processing. This file is referenced in the registration object (Model key in the src/index.js file) and it must implement one public function called `getData`.

Considerations

In order to fetch data from the Denodo Platform we are going to use the Denodo GeoJSON Service. For this purpose, keeping in mind that any query parameters that are part of the Koop request can be accessed in the getData function to use it for building the request to the remote API, we are going to add the Denodo GeoJSON view URL (<denodo_geojson_view_url>) to the Koop request, as the id parameter, with the slashes “/” replaced by pipes “|”:

http://<host>:<port>/denodo/<denodo_geojson_view_url>/FeatureServer

Koop request example:

http://geo.example.net:8080/denodo/denodo.example.net:8999|geojson|geojson_test|views|denodo_office/FeatureServer

Furthermore, the Denodo GeoJSON Service defines the $filter system query option that allows to identify a subset of the entries from the collection that satisfy the $filter predicate expression. Keeping this in mind, we can attach the where query parameter from the Koop request to the remote URL thus delegating the filtering process. This way, we can build a pass-through provider that reduces the amount of data coming over the wire and also the amount of post-processing for Koop.

Example

Below you can see an example of the model.js file. It can be used directly in your Koop Denodo provider. Note that it loads some properties (denodoUser, denodoPassword and denodoLimit) defined in a configuration file in the denodo-app application (config/default.json). You can also establish a value for these properties in that file.

// You can specify configuration properties in the denodo-app application (config/default.json) and load them in the provider

const config = require('config')

const request = require('request').defaults({

  headers: {

        'Authorization': 'Basic ' + new Buffer(`${config.denodoUser}` + ':' + `${config.denodoPassword}`).toString('base64')

  }

})

function Model (koop) {}

// Public function to return data from the

// Return: GeoJSON FeatureCollection

// URL path parameter:

// req.params.id -> <denodo_geojson_view_url> with the slashes / replaced by pipes |

Model.prototype.getData = function (req, callback) {

  // Convert denodo.server:port|geojson|database|views|viewname

  let url = req.params.id.replace(/\|/g,'/');

 

  // Get 'where' param from request object in order to delegate the filter

  const { query: { where } } = req;

  let whereExpression = typeof where  !== "undefined" && where != "" ? `$filter=${where}&` : ``;

  whereExpression = encodeURI(whereExpression);

  // You can configure the number of rows returned by a Denodo view by adding a /$count to the view endpoint. You can also omit this value.

  let limit = config.denodoLimit

  if( limit === undefined || limit === null ){

        limit = 15

  }

 

  let myrequest = `http://${url}?${whereExpression}$count=${limit}`;

 

  // Call the Denodo GeoJSON Service

  request(myrequest, (err, res, body) => {

        if (err) return callback(err)

        const geojson = translate(JSON.parse(body));

        // Optional: cache data for 10 seconds at a time by setting the ttl or "Time to Live"

        geojson.ttl = 10

        if(geojson.metadata === undefined || geojson.metadata === null) {

            geojson.metadata = {};

        }

        geojson.metadata.title = "Koop GeoJSON"

        geojson.metadata.description = `Data from ${url}`;

        // The object should have a boolean flag for any Geoservices operation that has already been executed on the GeoJSON.

        // where: Boolean, -> true if a sql-like where filter has already been applied to the data

        geojson.filtersApplied = { where: true };

        // hand off the data to Koop

        callback(null, geojson)

  })

}

function translate (input) {

  // GeoJSON can just be the geometry

  if( input.type === undefined || input.type === null || input.type != "FeatureCollection" ) {

        let geometry = 'Point';

        switch(input.type) {

            case 'LineString':

                geometry = 'Polyline';

                break;

            case 'MultiPolygon':

                geometry = 'Polygon';

                break;

            default:

                geometry = input.type;

        }

        return {

            type: 'FeatureCollection',

            features: [ formatFeature(input) ],

            metadata: {

                geometryType: geometry

            }

        }

  } else {

        // Or it's a feature collection

        return input;

  }

}

function formatFeature (geometry) {

 

  const feature = {

        type: 'Feature',

        properties: {

            "type": geometry.type

        },

        geometry: geometry

  }

  return feature

}

module.exports = Model

For more information about developing a Koop provider, see the Koop providers specification.

Adding a Koop Denodo provider

Finally, you have to copy the Koop Denodo provider to the Koop Denodo application (denodo-app) and register it in the application.

Plugin registration must be done in the src/plugins.js file:

const providerDenodo = require('../provider-denodo-geojson');

const plugins = [{

  instance: providerDenodo

}];

Now you can change your working directory to the one created for the Koop Denodo application and start it:

node ./src/index.js &

Once your Koop Denodo application, with the Koop Denodo provider, is running, you can access the ArcGIS Web Service:

http://geo.example.net:8080/denodo/denodo.example.net:8999|geojson|geojson_test|views|denodo_office/FeatureServer

Note that HTTP support will be discontinued in ArcGIS Online, and references to HTTP URLS will no longer work. Therefore, you must enable HTTPS on your server in order to use the ArcGIS Web Service in ArcGIS Online.

Adding Koop Denodo provider data in ArcGIS Online

After developing and running your Koop Denodo provider (see the Developing a Koop Denodo provider and Adding a Koop Denodo provider sections), you can access Denodo data. As an example, we use a Denodo view with information about Denodo offices.

Let's see the columns in this view that are most relevant for our explanation:

In this sample view you can find, among other information regarding each Denodo office, a field called “point_wkt” that contains geospatial data. It is a formal textual representation of the geographic point where the office is located. As the field name is suffixed with “wkt”, the Denodo GeoJSON service will automatically process it as a geometric type in the GeoJSON output.

Now you can add this Denodo data, using the Koop Denodo provider, in an ArcGIS Online Map following these steps:

  1. Open ArcGIS.com. 
  2. Click on Map and then select Add > Add Layer from Web.

  1. You must select “An ArcGIS Server Web Service” as the type of data you are referencing and add your ArcGIS Web Service URL:

In this example, data from the denodo_office view available in the Denodo Platform will be added in the ArcGIS Online Map.

  1. Select ADD LAYER to see your data in the map.

  1. By clicking on the points you can see the information available for this geospatial data.

  1. You can save the layer in order to have it as a layer in your Content clicking on More Options.

  1. Now you can add this layer to a map with the Add > Search for Layer option:

Setting a refresh interval

A refresh interval keeps your map in sync with the latest data while the map is open. You can enable a refresh interval in a layer clicking on More Options (under the layer name in the Contents section of a Map Viewer) and selecting Refresh Interval.

Filtering layers in ArcGIS Online

Filters are available in the layer clicking on the Filter option that appears under the layer name. A Filter layer pane will appear and you can create your filters that will be performed by Denodo Platform.

In this example, the filter will show the points corresponding to the offices that are in the "DENODO NORTH AMERICA" area. 

Since the Koop Denodo provider is a pass-through provider, the where query parameter from the Koop request will be added in the  $filter system query option of the Denodo GeoJSON service. This causes the condition to be delegated to Denodo Virtual DataPort:

select * from "geojson_test"."denodo_office" where ("area" = 'DENODO NORTH AMERICA') LIMIT 100

In this example, the LIMIT clause is added to the query due to the configuration set in the Koop Denodo provider using the denodoLimit property (see the Developing a Koop Denodo provider section).

Creating a dashboard in ArcGIS online

  1. Using the Save button first and then the Share button, you can save the map and create a dashboard with it.

After click on the Share button you should select CREATE WEB APP:

In the Create a New Web App pane you must select the ArcGIS Dashboards tab and complete the information:

  1. A dashboard will be created with a map that contains the Denodo offices layer.

Questions

Ask a question
You must sign in to ask a question. If you do not have an account, you can register here

Featured content

DENODO TRAINING

Ready for more? Great! We offer a comprehensive set of training courses, taught by our technical instructors in small, private groups for getting a full, in-depth guided training in the usage of the Denodo Platform. Check out our training courses.

Training