Denodo Geo Service - User Manual

Download original document


Overview

Denodo offers, through the Denodo Geo Service, a web service which makes geospatial data accessible in structured form.

The Denodo Geo Service is a web service that can be used to query the views within a Virtual DataPort Server and return GeoJSON objects or ArcGIS Features of the results. Therefore, the endpoints of the service can be used by applications seeking GeoJSON objects from VDP results or by the ArcGIS family of products making use of ArcGIS Features from VDP results via a REST web service. The output format is JSON in both scenarios and if the view contains a designated field of geometric type, it will be incorporated in the JSON object as a geometry type.

Execution

The Denodo Geo Service distribution consists of:

  • A bin folder containing:
  • denodo-geo-service-<VERSION>.jar
  • the necessary scripts to launch the application

  • A config folder containing the application.properties and the log4j2.xml

For running the Denodo Geo Service, you need to execute the denodo_geo_service.[bat|.sh]

The Denodo Geo Service has some default properties that can be reset:

  • cors.allowed-origins: List of origins to allow CORS access to

  • enable.adminUser: false if access to the Geo service is denied to the VDP admin user.
  • source.crs: specify the coordinate reference system (CRS) in which the Denodo geometric data that we are going to consume is found. This property only applies to ArcGIS Layer service endpoints.

For example, if the CRS of your data is “EPSG:4326” with (longitude, latitude) axis order, you should set:

source.crs=EPSG:4326:XY

Note that the :XY suffix was added in order to force (longitude, latitude) axis order  to avoid ambiguity when specifying spatial reference systems in some scenarios. If you do not add the :XY suffix it means that the axis order will be determined by the system default option (not “latitud first”). More information is available in the Denodo XtraFuncs documentation.

  • disable.geoJSONService: true to disable the GeoJSON service. Default value is false.

  • disable.arcGISService: true to disable the ArcGIS Layer service. Default value is false.

  • enable.client.ip.filtering: true to enable the IP filtering to allow the connection to the ArcGIS Layer service only from a specified IP (or a list of IPs). Only applies to the ArcGIS Layer service endpoints. It allows you to control from which IP or IPs (specified in the ‘allowed.client.ips’ property) the connection to your service is allowed.

  • allowed.client.ips: the list of allowed client IPs separated by semicolons. Only applies to the ArcGIS Layer service endpoints. The default value, and the recommended option, is localhost (allowed.client.ips=127.0.0.1;0:0:0:0:0:0:0:1).

  • disable.authentication.IPFilteringMode: true to disable the authentication in the ArcGIS Layer service when the IP filter is enabled. Only applies to the ArcGIS Layer service endpoints. Default value is false. If you disable the authentication you should specify the data source configuration in order to establish the connection:

  • spring.datasource.disableAuthenticationIPFilteringMode.driver-class-name: the class name of the Denodo JDBC driver
  • spring.datasource.disableAuthenticationIPFilteringMode.url: the driver URL format that will establish the connection
  • spring.datasource.disableAuthenticationIPFilteringMode.username: the user name
  • spring.datasource.disableAuthenticationIPFilteringMode.password: the password.  It could be encrypted or clear. See the How to encrypt passwords section for a detailed explanation.

Please be aware that, especially when you disable authentication leaving only IP filtering, the allowed IP addresses must correspond to sites that you trust.

The configuration file BOOT-INF/classes/application.properties has the values for the properties explained above and can be edited as desired:

# Number of rows to return by default

default.rowlimit=10

# Enable CORS by specifying a list of urls or * for all urls

cors.allowed-origins=*

# Enable or disable the use of ‘admin’ user when connecting to VDP

enable.adminUser=false

# The Coordinate Reference System in which Denodo spatial data is represented

source.crs=EPSG:4326:XY

# Disable the GeoJSON service

disable.geoJSONService=false

# Disable the ArcGIS Layer service

disable.arcGISService=false

# Enable the IP filtering. Only applies to the ArcGIS Layer service

enable.client.ip.filtering=true

# Allowed client IPs separated by semicolons. Only applies to the ArcGIS Layer service

allowed.client.ips=127.0.0.1;0:0:0:0:0:0:0:1

# Disable authentication with the IP filter enabled.

disable.authentication.IPFilteringMode=true

# Data source configuration to establish a connection when ‘disable.authentication.IPFilteringMode’ is true

spring.datasource.disableAuthenticationIPFilteringMode.driver-class-name=com.denodo.vdp.jdbc.Driver

spring.datasource.disableAuthenticationIPFilteringMode.url=jdbc:vdb://localhost:9999/admin?userAgent=denodo-geo-service

spring.datasource.disableAuthenticationIPFilteringMode.username=admin

spring.datasource.disableAuthenticationIPFilteringMode.password=ENC(CvzPHh++EXXOP5Dx1WVzpw==)

Once the application is deployed you can use the Denodo Geo Service from a web client, using HTTP Basic Authentication with VDP-valid credentials. In the case of the ArcGIS Layer service, if you enable filtering by IP you can disable authentication and in this scenario the IP from which the request is made must be one of those allowed in the configuration.

URLs are of the form:

Denodo GeoJSON service

http://<host>:8999/geojson/<DBNAME>

Denodo ArcGIS Layer service 

http://<host>:8999/arcgis/rest/services/<DBNAME>/views/<VIEWNAME>/FeatureServer

You can also configure the port of the service in the file application.properties.

How to encrypt passwords

The Geo Service expects encrypted passwords in the application.properties file, to appear surrounded by ENC(...). You can compute these values using the Jasypt CLI tools, and use the DENODO_GEO_ENCRYPTION_PASSWORD environment variable, or Java system property, to communicate the encryption password to the Denodo Geo Service.

This way, you can use encrypted passwords in the application.properties file:

...

spring.datasource.disableAuthenticationIPFilteringMode.password=ENC(CvzPHh++EXXOP5Dx1WVzpw==)

...

 

These are the steps for encrypting passwords:

  1. Download Jasypt CLI tools.

  1. Choose an encryption password, e.g., mypassword.

  1. Go to jasypt/bin.

  1. Run encrypt.bat with the input parameter and password parameter:

  • input parameter - this is the string you want to encrypt.

  • password parameter - this is the password that Jasypt is going to use to encrypt and decrypt the input parameter.

Your command should look like this:


        

Take note of the output. Example output: CvzPHh++EXXOP5Dx1WVzpw==.

  1. Open your application.properties file, replace the password you want to encrypt with  the output from Step 4: ENC(CvzPHh++EXXOP5Dx1WVzpw==).

Before Jasypt:

spring.datasource.disableAuthenticationIPFilteringMode.password=admin

After Jasypt

spring.datasource.disableAuthenticationIPFilteringMode.password=ENC(CvzPHh++EXXOP5Dx1WVzpw==)

  1. Add an environment variable or Java system property, DENODO_GEO_ENCRYPTION_PASSWORD, with a value of mypassword, but use your real encryption password.

Enabling cross-origin resource sharing (CORS)

Cross-origin resource sharing (CORS) is a mechanism whereby a browser and server interact to determine whether to allow a web page to perform HTTP requests to a domain other than the domain from which the web page is originated. I.e. so the browser can determine if it is safe that a page served from the domain http://foo.com can send requests to a server in the domain http://bar.com.

Once deployed the Denodo Geo Service, follow these steps to enable the CORS support:

  1. Edit the application.properties file which is at the config folder.

  1. Change the following parameter’s value:

cors.allowed-origins=*

    * represents that all origins are allowed.

You can specify a list of urls separated   by comma to override this default value. For example:

cors.allowed-origins=http://foo.com,http://bar.com

Denodo GeoJSON service

GeoJSON is a geospatial data interchange format based on JavaScript Object Notation (JSON). It defines several types of JSON objects and the manner in which they are combined to represent data about geographic features, their properties, and their spatial extents. GeoJSON uses a geographic coordinate reference system, World Geodetic System 1984, and units of decimal degrees.

GeoJSON supports the following geometry types:

 

  • Point
  • LineString
  • Polygon
  • MultiPoint
  • MultiLineString
  • MultiPolygon

Geometric objects with additional properties are Feature objects. Sets of features are contained by FeatureCollection objects.

Features

The Denodo GeoJSON Service provides the following functionality:

  • Read-only access to Denodo databases

  • Query options

  • $select
  • $filter
  • $orderby
  • $groupby
  • $start_index
  • $count

  • Pagination

  • HTTP Authentication
  • Basic

Querying data: the basics

There are three types of GeoJSON service endpoints:

  1. Database Endpoints

  1. View Endpoints

  1. View Count Endpoints

Database endpoints

You can receive a JSON object containing information from a VDP database by specifying a virtual database name after the root of the service with the following pattern:

.../geojson/<DBNAME>

The GeoJSON service returns a GeoJSON object that lists metadata of all the views accessible in the virtual database by the GeoJSON service. This metadata includes the folder the view is in, the name of the database it is in, the endpoint that can be appended to the URL to access the view data, the description of the view as set in the Virtual DataPort server metadata, and the view name.

Below there is an example of the returned GeoJSON object with the accessible views of a database, test is the name of this database: bv_corp_offices, bv_dist_centers, and bv_retail.

.../geojson/test

{

    "views": [

        {

             "folder": "/02 - base views",

                "database_name": "test",

                "end_point": "/views/bv_corp_offices",

                "description": "Base view created over a remote data  

             source containing corporate office location info.",

                "view_name": "bv_corp_offices"

        },

        {

                "folder": "/02 - base views",

                "database_name": "test",

                "end_point": "/views/bv_dist_centers",

                "description": "Base view created over a remote data

            source containing distribution center location info.",

                "view_name": "bv_dist_centers"

        },

        {

                "folder": "/02 - base views",

                "database_name": "test",

                "end_point": "/views/bv_retail",

                "description": "Base view created over a remote data  

                source containing retail seller location info.",

                "view_name": "bv_retail"

        }

  ]

}

View endpoints

You will receive a GeoJSON FeatureCollection object containing data from a VDP view by specifying the view name after the root of the service and database with the following the pattern:

.../geojson/<DBNAME>/views/<VIEWNAME>

Each tuple of the results will be added as an individual Feature of the GeoJSON FeatureCollection returned and each field in the tuple will be added as an individual property of that Feature. Fields of the view suffixed with “wkb” or “wkt” (case insensitive), denoting well-known binary and well-known text respectively, will be automatically processed as geometric types in the GeoJSON output if no geometry field is denoted using the $geometry parameter described below.

For example, if a view called bv_corp_offices view has a field named “location_wkt

and we access the following URL:

.../geojson/test/views/bv_corp_offices

The response would be rendered as:

{

"features": [

        {

            "geometry": {

                "coordinates": [

                        -122.159285,

                        37.448861

                   ],

                   "type": "Point"

                },

                "type": "Feature",

                "properties": {

                   "zipcode": "94301",

                   "country": "United States",

                   "city": "Palo Alto",

                   "street": "525 University Avenue #31",

                   "name": "Denodo HQ",

                   "id": "1",

                   "state": "California"

                }

        },

        {

        }

],

"type": "FeatureCollection"

}

Denoting a geometry field: $geometry

The $geometry parameter allows you to specify the name of the column containing the spatial data.

For example, continuing with the example in the View endpoints section, if the bv_corp_offices view has a field named “location” (without  using the suffixes “wkb” or “wkt” in its name) containing geometric data and we access the following URL:

.../geojson/test/views/bv_corp_offices?$geometry=location

The response would be rendered as in the previous example where the field name was “location_wkt” instead of “location”:

{

"features": [

        {

            "geometry": {

                "coordinates": [

                        -122.159285,

                        37.448861

                   ],

                   "type": "Point"

                },

                "type": "Feature",

                "properties": {

                   "zipcode": "94301",

                   "country": "United States",

                   "city": "Palo Alto",

                   "street": "525 University Avenue #31",

                   "name": "Denodo HQ",

                   "id": "1",

                   "state": "California"

                }

        },

        {

        }

],

"type": "FeatureCollection"

}

Note that if there is no field referenced by the $geometry parameter or if the view does not have any columns whose name ends with “wkt” or “wkb” then the value for the geometry will be set as null and all the columns become part of the properties.

VDP view count endpoints

The number of rows returned by a view can be checked by adding a /$count to the view endpoint. For example, a request to the following URL:

.../geojson/test/views/bv_corp_offices/$count

will return:

{

    "count": 9

}

reflecting the number of results in the view.

Obtaining items by primary key

Each item could be identified using its primary key property:

.../geojson/<DBNAME>/viewName/<keyvalue>

Examples:

.../geojson/test/views/bv_corp_offices/1

.../geojson/test/views/bv_dist_centers/NE

The primary key can be a compound key, and in that case you must include all values separated by commas:

.../geojson/test/views/bv_retail/1,STo3

Note: When there is not a defined primary key, this option is unavailable.

Note: URL encoded characters should be used where necessary. For example, %26 for $.

Accessing individual properties with $select

Properties of an item can be accessed individually using the $select query parameter:

.../geojson/<DBNAME>/views/<VIEWNAME>?$select=<PROPERTYNAME>

Note: For the sake of simplicity we are removing the server, port, and GeoJSON service root from the example URLs.

Note: A ? needs to be added to the end of the URL in order to start adding query parameters.

Example:

.../geojson/test/views/bv_corp_offices?$select=state

Response:

{

    "features": [

         {

                "geometry": null,

                "type": "Feature",

                "properties": {

                        "state": "California"

                }

         },

         {

                

         }

        ],

        "type": "FeatureCollection"

}

Multiple properties can be accessed by separating them with a comma. Example:

.../geojson/test/views/bv_corp_offices?$select=name,location_wkt

Response:

{

   "features": [

        {

                "geometry": {

                        "coordinates": [

                                -122.159285,

                                37.448861

                        ],

                        "type": "Point"

                },

                "type": "Feature",

                "properties": {

                        "name": "Denodo HQ"

                }

        },

        {

                

        }

        ],

        "type": "FeatureCollection"

}

Advanced querying

GeoJSON defines some query options that allow refining the requests: $filter, $select, $orderby.

Selection: $filter

A URI with a $filter system query option identifies a subset of the entries from the collection that satisfy the $filter predicate expression.

Expressions can reference properties and literals. The latter can be strings (enclosed in single quotes), numbers, boolean or datetime values.

In order to specify a datetime value, you have to provide it using datetime functions TO_LOCALDATE, TO_TIME, TO_TIMESTAMP, TO_TIMESTAMPTZ as you can see in the example below:

.../geojson/test/views/bv_corp_offices?$filter=updated_date > TO_TIMESTAMP('yyyyMMdd HH:mm', '20220621 15:00')

Denodo GeoJSON service supports the following operations and functions:

Operator

Description

 Example

=

Equal

/bv_retail?$filter=state = 'WASHINGTON'

<>

Not equal

/bv_retail?$filter=state <> 'WASHINGTON'

>

Greater than

/bv_retail?$filter=store_id > 5

>=

Greater than or equal

/bv_retail?$filter=store_id >= 5

<

Less than

/bv_retail?$filter=store_id < 5

<=

Less than or equal

/bv_retail?$filter=store_id <= 5

and

Logical and

/bv_retail?$filter=store_id > 5 and store_i < 10

or

Logical or

/bv_retail?$filter=store_id < 5 or state = 'UTAH'

is null

Column value is NULL

/bv_retail?$filter=state is null

is not null

Column value is not NULL

/bv_retail?$filter=state is not null

like

Search for a specified pattern in a column. Available wildcard characters:

% (percentage). Represents a segment of text of any length, including an empty text. Remember to use it as an URL encoded character (%25)

_ (underscore). Represents any character (only one character).

/bv_retail?$filter=state like 'W%25'

between

a between b AND c: true if a is greater than or equal to b and less than or equal to c

/bv_retail?$filter=store_id between 5 and 10

Projection: $select

The $select system query option returns only the properties explicitly requested. $select expressions can be a comma-separated lists of properties or the star operator (*), which will retrieve all the properties.

Example:

.../geojson/test/views/bv_corp_offices?$select=zipcode,city,state

Response:

{

"features": [

        {

                "geometry": null,

                "type": "Feature",

                "properties": {

                        "zipcode": "94301",

                        "city": "Palo Alto",

                        "state": "California"

                }

        },

     ...

Ordering results: $orderby

The $orderby query string option specifies the order in which items are returned:

.../geojson/<DBNAME>/views/<VIEWNAME>?$orderby=attribute [asc|desc]

To order the collection the resource path must identify a collection of entries, otherwise this option is unavailable.

The keywords asc and desc determine the direction of the sort (ascending or descending,  respectively). If asc or desc are not specified items are returned in ascending order. Null values come before non-null values when sorting in ascending order and vice versa.

You can also sort by multiple attributes:

.../views/<VIEWNAME>?$orderby=attr1 [asc|desc],attr2 [asc|desc]

Example:

.../geojson/test/views/bv_denodo_offices?$orderby=zipcode

.../geojson/test/views/bv_denodo_offices?$orderby=city%20asc,zipcode%20desc

Grouping results: $groupby

The $groupby option will let you perform a SQL group by on the view. It can be used as shown below:

.../geojson/<DBNAME>/viewname?$select=field1,field2&$groupby=field1,field2

Pagination

Whenever the Denodo Geojson Service has to return a collection of entries which size exceeds that configured at the default.rowlimit property of its configuration file, it will split the response into pages (returning only the first n entries).

You can manage the pagination with the parameters $count and $start_index detailed below. The value of $start_index has priority over the value of default.rowlimit.

Specifying maximum number of results: $count

The $count system query option selects the n first entries of the collection, with n

being a non-negative integer:

.../geojson/<DBNAME>/views/<VIEWNAME>?$count=<POS_INT_VALUE>

Example:

.../geojson/test/views/bv_corp_offices?$count=1

Response:

{

"features": [

    {

        "geometry": {

                "coordinates": [

                        -122.159285,

                        37.448861

                ],

                "type": "Point"

        },

        "type": "Feature",

        "properties": {

                "zipcode": "94301",

                "country": "United States",

                "city": "Palo Alto",

                "street": "525 University Avenue #31",

                "name": "Denodo HQ",

                "id": "1",

                "state": "California"

        }

   }

],

"type": "FeatureCollection"

}

Specifying start of the query $start_index

The $start_index allow to skip the first n rows of the result set, with n being a nonnegative Integer:

.../geojson/<DBNAME>/views/<VIEWNAME>?$start_index=<POS_INT_VALUE>

Example:

.../geojson/test/views/bv_corp_offices?$star_index=2

This sample returns the query, starting the result in the third tuple.

Denodo ArcGIS Layer service

ArcGIS is a family of software products in the field of Geographic Information Systems (GIS) developed and maintained by Esri. ArcGIS products support GeoServices specification. This specification provides a standard way for web clients to communicate with geographic information system servers through Representational State Transfer (REST) technology. The Denodo ArcGIS endpoint implements part of this specification providing a Feature Service resource that allows you to make available a Denodo Virtual DataPort GIS resource (in view format) to ArcGIS.

The Denodo ArcGIS Layer service supports the following geometry types:

  • Point
  • MultiPoint
  • LineString
  • MultiLineString
  • Polygon
  • MultiPolygon

Features

The Denodo ArcGIS Layer Service provides the following functionality:

  • Read-only access to Denodo databases

  • Query options

  • where
  • objectIds
  • geometry and geometryType
  • outFields
  • returnGeometry
  • outSR
  • returnCountOnly
  • orderByFields
  • resultOffset
  • resultRecordCount

  • HTTP Authentication
  • Basic

Prerequisites and considerations

Before using the Denodo ArcGIS web service you should take into account some requirements:

  • The spatial VDP custom functions, included in the Denodo XtraFuncs for VDP library, are necessary in order to accomplish the operations performed by the service.

  • Geometric fields should be identified in Denodo suffixed with “wkb” or “wkt” (case insensitive), denoting well-known binary and well-known text respectively. These fields will be automatically processed as geometric types in the Denodo ArcGIS Layer service.

  • An int primary key in the Denodo view that you are going to process is mandatory. This primary key will be used as ObjectID by ArcGIS. The ObjectID is a unique, not null integer field used to uniquely identify rows in ArcGIS and it is limited to 32-bit values; this is why the primary key field type should be int.

  • ArcGIS online does not support HTTP services, therefore you can configure the server to enable HTTPS connections. After getting an SSL certificate you have to set up the Denodo Geo Service, which is a Spring Boot application, to accept requests over HTTPS by using that certificate. To achieve this aim you should add the SSL configuration in the application.properties file:

# Enable SSL support

server.ssl.enabled=true

# The format used for the keystore.

server.ssl.key-store-type=

# The path to the keystore containing the certificate

server.ssl.key-store=

# The password used to generate the certificate

server.ssl.key-store-password=

# The alias mapped to the certificate

server.ssl.key-alias=

Querying data

The Denodo ArcGIS Layer service has two types of endpoints:

  1. Feature Service endpoint

  1. Layer endpoint

Note: For the sake of simplicity we are removing the server and port from the example URLs.

Feature Service

The Feature Service resource provides basic information about the service.

.../arcgis/rest/services/<DBNAME>/views/<VIEWNAME>/FeatureServer

Below there is an example of the returned JSON object for the view denodo_office that is in a database called geo_db:

.../arcgis/rest/services/geo_db/views/denodo_office/FeatureServer

{

  "fullExtent": {

    "ymin": -33.8658285,

    "xmin": -122.3966159,

    "ymax": 51.5190307,

    "xmax": 151.210018,

    "spatialReference": {

      "wkid": 4326

    }

  },

  "tables": [],

  "initialExtent": {

    "ymin": -33.8658285,

    "xmin": -122.3966159,

    "ymax": 51.5190307,

    "xmax": 151.210018,

    "spatialReference": {

      "wkid": 4326

    }

  },

  "maxRecordCount": 100,

  "layers": [

    {

      "subLayerIds": null,

      "maxScale": 0,

      "name": "Layer_0",

      "defaultVisibility": true,

      "id": 0,

      "minScale": 0,

      "parentLayerId": -1,

      "geometryType": "esriGeometryPoint"

    }

  ],

  "spatialReference": {

    "wkid": 4326

  }

}

Layer endpoint

Adding the layer id after the Feature Service endpoint you will get a JSON object representing a single feature layer or a nonspatial table in a feature service. For tables, it provides basic information about the table such as its ID, name, fields or types. For feature layers, in addition to the table information, it provides information such as its geometry type, min and max scales, and spatial reference.

Note that the Denodo ArcGIS Layer service offers only one view per endpoint and each view will be performed as a feature layer or a nonspatial table. Therefore, the layer id will be “0”:

.../arcgis/rest/services/<DBNAME>/views/<VIEWNAME>/FeatureServer/0

{

  "extent": {

    "ymin": -33.8658285,

    "xmin": -122.3966159,

    "ymax": 51.5190307,

    "xmax": 151.210018,

    "spatialReference": {

      "wkid": 4326

    }

  },

  "capabilities": "Query",

  "maxRecordCount": 100,

  "defaultVisibility": true,

  "type": "Feature Layer",

  "uniqueIdField": {

    "name": "id"

  },

  "currentVersion": 10.51,

  "objectIdField": "id",

  "timeInfo": {},

  "name": "Not set",

  "drawingInfo": {

    "renderer": {

      "symbol": {

        "outline": {

          "color": [

            190,

            190,

            190,

            105

          ],

          "width": 0.5,

          "style": "esriSLSSolid",

          "type": "esriSLS"

        },

        "color": [

          45,

          172,

          128,

          161

        ],

        "size": 7.5,

        "style": "esriSMSCircle",

        "type": "esriSMS"

      },

      "type": "simple"

    },

    "labelingInfo": null

  },

  "displayField": "id",

  "id": 0,

  "fields": [

    {

      "sqlType": "sqlTypeInteger",

      "editable": false,

      "domain": null,

      "name": "id",

      "alias": "id",

      "type": "esriFieldTypeOID"

    },

    {

      "sqlType": "sqlTypeOther",

      "defaultValue": null,

      "editable": false,

      "domain": null,

      "name": "area",

      "alias": "area",

      "type": "esriFieldTypeString"

    },

    {

      "sqlType": "sqlTypeOther",

      "defaultValue": null,

      "editable": false,

      "domain": null,

      "name": "country",

      "alias": "country",

      "type": "esriFieldTypeString"

    },

    {

      "sqlType": "sqlTypeOther",

      "defaultValue": null,

      "editable": false,

      "domain": null,

      "name": "address",

      "alias": "address",

      "type": "esriFieldTypeString"

    },

    {

             "sqlType": "sqlTypeOther",

      "defaultValue": null,

      "editable": false,

      "domain": null,

      "name": "phone",

      "alias": "phone",

      "type": "esriFieldTypeString"

    },

    {

      "sqlType": "sqlTypeOther",

      "defaultValue": null,

      "editable": false,

      "domain": null,

      "name": "id",

      "alias": "id",

      "type": "esriFieldTypeInteger"

    },

    {

      "sqlType": "sqlTypeOther",

      "defaultValue": null,

      "editable": false,

      "domain": null,

      "name": "updated_date",

      "alias": "updated_date",

      "type": "esriFieldTypeDate"

    },

    {

      "sqlType": "sqlTypeOther",

      "defaultValue": null,

      "editable": false,

      "domain": null,

      "name": "email",

      "alias": "email",

      "type": "esriFieldTypeString"

    }

  ],

  "geometryType": "esriGeometryPoint"

  "advancedQueryCapabilities": {

    "supportsOrderBy": true,

    "supportsPagination": true

  }

}

Query option

On a feature service Layer resource you can perform the Query operation.

.../arcgis/rest/services/<DBNAME>/views/<VIEWNAME>/FeatureServer/0/query

The result of this operation is a feature set where the Denodo view data is available.

{

  "features": [

    {

      "geometry": {

        "x": 80.21147240000002,

        "y": 13.0149902

      },

      "attributes": {

        "area": "DENODO APAC",

        "country": "India",

        "address": "06th Floor, Kochar Jade, Plot no SP 22 Thiru Vi Ka Industrial Estate, Guindy Chennai, Tamil Nadu 600032",

        "phone": "(+91) 44 4687 6100",

        "id": 2,

        "updated_date": 1656583355537,

        "email": "info.apac@denodo.com"

      }

    },

    {

      "geometry": {

        "x": -8.4386809,

        "y": 43.32680400000001

      },

      "attributes": {

        "area": "DENODO IBERIA & LATIN AMERICA",

        "country": "Spain",

        "address": "Polígono de Pocomaco, Parcela D22. Centro Mans, Av. Quinta, 15190 A Coruña",

        "phone": "(+34) 981 10 02 00",

        "id": 10,

        "updated_date": 1656583355537,

        "email": "info.iberia@denodo.com & info.la@denodo.com"

      }

    },

    ...

  ],

  "exceededTransferLimit": false,

  "objectIdFieldName": "id",

  "spatialReference": {

    "wkid": 4326

  },

  "fields": [

    {

      "sqlType": "sqlTypeInteger",

      "editable": false,

      "domain": null,

      "name": "id",

      "alias": "id",

      "type": "esriFieldTypeOID"

    },

    {

      "sqlType": "sqlTypeOther",

      "defaultValue": null,

      "editable": false,

      "domain": null,

      "name": "area",

      "alias": "area",

      "type": "esriFieldTypeString"

    },

    {

      "sqlType": "sqlTypeOther",

      "defaultValue": null,

      "editable": false,

      "domain": null,

      "name": "country",

      "alias": "country",

      "type": "esriFieldTypeString"

    },

    {

      "sqlType": "sqlTypeOther",

      "defaultValue": null,

      "editable": false,

      "domain": null,

      "name": "address",

      "alias": "address",

      "type": "esriFieldTypeString"

    },

    {

      "sqlType": "sqlTypeOther",

      "defaultValue": null,

      "editable": false,

      "domain": null,

      "name": "phone",

      "alias": "phone",

      "type": "esriFieldTypeString"

    },

    {

      "sqlType": "sqlTypeOther",

      "defaultValue": null,

      "editable": false,

      "domain": null,

      "name": "id",

      "alias": "id",

      "type": "esriFieldTypeInteger"

    },

    {

      "sqlType": "sqlTypeOther",

      "defaultValue": null,

      "editable": false,

      "domain": null,

      "name": "updated_date",

      "alias": "updated_date",

      "type": "esriFieldTypeDate"

    },

    {

      "sqlType": "sqlTypeOther",

      "defaultValue": null,

      "editable": false,

      "domain": null,

      "name": "email",

      "alias": "email",

      "type": "esriFieldTypeString"

    }

  ],

  "uniqueIdField": {

    "name": "id"

  },

  "geometryType": "esriGeometryPoint"

}

Query parameters

Using the Query operation you can provide arguments as query parameters.

Note: A ? needs to be added to the end of the URL in order to start adding query parameters.

Parameter: where

A request with a where query option identifies a subset of the entries from the collection that satisfy the where predicate expression.

Expressions can reference properties and literals. The latter can be strings (enclosed in single quotes), numbers, boolean or datetime values.

In order to specify a datetime value, you have to provide it using datetime functions TO_LOCALDATE, TO_TIME, TO_TIMESTAMP, TO_TIMESTAMPTZ as you can see in the example below:

.../denodo_office/FeatureServer/0/query?where=updated_date > TO_TIMESTAMP('yyyyMMdd HH:mm', '20220621 15:00')

Denodo ArcGIS Layer service supports the following operations and functions:

Operator

Description

 Example

=

Equal

/denodo_office/FeatureServer/0/query?where=country = 'Spain'

<>

Not equal

/denodo_office/FeatureServer/0/query?where=country <> 'Spain'

>

Greater than

/denodo_office/FeatureServer/0/query?where=id > 5

>=

Greater than or equal

/denodo_office/FeatureServer/0/query?where=id >= 5

<

Less than

/denodo_office/FeatureServer/0/query?where=id < 5

<=

Less than or equal

/denodo_office/FeatureServer/0/query?where=id <= 5

and

Logical and

/denodo_office/FeatureServer/0/query?where=id > 5 and id < 10

or

Logical or

/denodo_office/FeatureServer/0/query?where=id < 5 or country = 'Italy'

is null

Column value is NULL

/denodo_office/FeatureServer/0/query?where=phone is null

is not null

Column value is not NULL

/denodo_office/FeatureServer/0/query?where=phone is not null

like

Search for a specified pattern in a column. Available wildcard characters:

% (percentage). Represents a segment of text of any length, including an empty text. Remember to use it as an URL encoded character (%25)

_ (underscore). Represents any character (only one character).

/denodo_office/FeatureServer/0/query?where=country like 'J%25'

between

a between b AND c: true if a is greater than or equal to b and less than or equal to c

/denodo_office/FeatureServer/0/query?where=id between 5 and 10

Parameter: objectIds

This query parameter allows you to set the object IDs of this layer or table to be queried. The object ID corresponds to the primary key of the Denodo view, which must be of type int (see the Prerequisites and considerations section).

.../denodo_office/FeatureServer/0/query?objectIds=id_value1,id_value2

Example:

.../denodo_office/FeatureServer/0/query?objectIds=3,5

Response:

{

  "features": [

    {

      "geometry": {

        "x": 11.5652201,

        "y": 48.1430195

      },

      "attributes": {

        "area": "DENODO EMEA",

        "country": "Germany",

        "address": "Karlstraße 10 Munich, 80333",

        "phone": "(+49) (0) 89 599 904 50",

        "id": 5,

        "updated_date": 1656582862376,

        "email": "info.emea@denodo.com"

      }

    },

    {

      "geometry": {

        "x": 151.210018,

        "y": -33.8658285

      },

      "attributes": {

        "area": "DENODO APAC",

        "country": "Australia",

        "address": "Level 45, World Square 680 George Street Sydney NSW 2000",

        "phone": "(+61) 424661795",

        "id": 3,

        "updated_date": 1656582862376,

        "email": "info.apac@denodo.com"

      }

    }

  ],

  ...

}

Parameter: geometry and geometryType

These two parameters are used by the Denodo ArcGIS Layer service to apply filters. This allows the viewport filtering when the visualized geometry type is esriGeometryPoint. The geometry parameter specifies the geometry to apply as the spatial filter, while the geometryType specifies the geometry type of the geometry defined by the geometry parameter. In our scenario, the geometryType should be esriGeometryEnvelope which is the default geometry type.

.../denodo_office/FeatureServer/0/query?geometry={"xmin":<X_MIN_VALUE>,"ymin":<Y_MIN_VALUE>,"xmax":<X_MAX_VALUE>,"ymax":<Y_MAX_VALUE>}&geometryType=esriGeometryEnvelope

Example:

.../denodo_office/FeatureServer/0/query?geometry={"xmin":-5009377.085698988,"ymin":0.000002983957529067993,"xmax":-0.000002983957529067993,"ymax":5009377.085698988}&geometryType=esriGeometryEnvelope

Note: the geometry parameter value should be specified in URL-encoded format.

Parameter: outFields

With this parameter you can establish the list of fields to be included in the returned result set. This list is a comma-delimited list of field names. You can also specify the wildcard "*" as the value of this parameter. In this case, the query results include all the field values.

.../denodo_office/FeatureServer/0/query?outFields=field1,field2

Example:

.../denodo_office/FeatureServer/0/query?outFields=area,country

Response:

{

  "features": [

    {

      "geometry": {

        "x": 139.7626199,

        "y": 35.6864055

      },

      "attributes": {

        "area": "DENODO APAC",

        "country": "Japan"

      }

    },

    {

      "geometry": {

        "x": -3.6929996,

        "y": 40.4182719

      },

      "attributes": {

        "area": "DENODO IBERIA & LATIN AMERICA",

        "country": "Spain"

      }

    },

     ...

  ]

  ...

}

Parameter: returnGeometry

The returnGeometry parameter specifies if the result includes the geometry associated with each feature returned (when it is set to true) or not (when it is set to false). The default is true.

.../denodo_office/FeatureServer/0/query?returnGeometry=<BOOLEAN>

Example:

.../denodo_office/FeatureServer/0/query?returnGeometry=false

Response:

{

  "features": [

    {

      "attributes": {

        "area": "DENODO EMEA",

        "country": "Germany",

        "address": "Karlstraße 10 Munich, 80333",

        "phone": "(+49) (0) 89 599 904 50",

        "id": 5,

        "updated_date": 1656582205129,

        "email": "info.emea@denodo.com"

      }

    },

    {

      "attributes": {

        "area": "DENODO NORTH AMERICA",

        "country": "USA",

        "address": "8000 Towers Crescent Drive, 13th Floor Vienna, VA 22182",

        "phone": null,

        "id": 16,

        "updated_date": 1656582205129,

        "email": "info.us@denodo.com"

      }

            },

     

     ...

            

        ],

        "exceededTransferLimit": false,

        "objectIdFieldName": "id",

        "spatialReference": {

            "wkid": 4326

        },

        ...

}

Parameter: outSR

The outSR parameter lets you define the spatial reference of the returned geometry specified as a well-known ID.

.../denodo_office/FeatureServer/0/query?outSR=<WKID>

Example:

.../denodo_office/FeatureServer/0/query?outSR=102100

Parameter: returnCountOnly

The returnCountOnly parameter determines, with a boolean value, if the response only includes the count (number of features/records) that would be returned by a query when it is set to true or the feature set when it is false. The default is false.

.../denodo_office/FeatureServer/0/query?returnCountOnly=true

Response:

{

  "count": 16

}

Parameter: orderByFields

The orderByFields parameter allows you to specify one or more field names on which the features/records need to be ordered. Use the keywords ASC or DESC for ascending or descending, respectively, following every field to control the ordering. ASC (ascending order) is the default value.

.../denodo_office/FeatureServer/0/query?orderByFields=attribute [ASC|DESC]

You can also sort by multiple attributes:

/denodo_office/FeatureServer/0/query?orderByFields=attr1 [ASC|DESC],attr2 [ASC|DESC]

Example:

.../denodo_office/FeatureServer/0/query?orderByFields=country DESC,area ASC

Pagination

Whenever the Denodo ArcGIS Layer service has to return a collection of entries whose size exceeds that configured at the default.rowlimit property of the configuration file, it will split the response into pages (returning only the first n entries).

You can manage the pagination with the parameters resultOffset and resultRecordCount detailed below.  Note that the value of resultRecordCount has priority over the value of default.rowlimit.

Specifying maximum number of results: resultRecordCount

This option can be used for fetching query results up to the resultRecordCount specified.

.../denodo_office/FeatureServer/0/query?resultRecordCount=<INT_VALUE>

Example:

.../denodo_office/FeatureServer/0/query?resultRecordCount=1

Response:

"features": [

  {

    "geometry": {

      "x": 139.7626199,

      "y": 35.6864055

    },

    "attributes": {

      "area": "DENODO APAC",

      "country": "Japan",

      "address": "1F SPACES Otemachi Building, 1-6-1 Otemachi, Chiyoda-ku Tokyo 100-0004",

      "phone": "(+81) 03 6841 4229",

      "id": 4,

      "updated_date": 1656587442010,

      "email": "info.apac@denodo.com"

    }

  }

]

Specifying start of the query: resultOffset

This option can be used for fetching query results by skipping the specified number of records and starting from the next record.

.../denodo_office/FeatureServer/0/query?resultOffset=<INT_VALUE>

Example:

.../denodo_office/FeatureServer/0/query?resultOffset=2

This sample returns the query, starting the result in the third tuple.

While paging, in order to know if there are more records than shown in the current response, the exceededTransferLimit property is included in the JSON response. When exceededTransferLimit is true, it indicates there are more query results and you can continue to page through the results. When exceededTransferLimit is false, it indicates that you have reached the end of the query results.

When you do not use the resultOffset and resultRecordCount parameters, the exceededTransferLimit property may also be included in the query results. In this case, the property will be true only if the number of records exceeds the maximum number configured at the default.rowlimit property of the configuration file. The  default.rowlimit value can also be seen in the maxRecordCount property of the Denodo ArcGIS Layer service response.

Adding the Denodo ArcGIS Layer service in ArcGIS Online

The Denodo ArcGIS Layer service can be added as a feature service in an ArcGIS Online Map to visualize the geographically-referenced data that the web service publishes from Denodo. Note that due to the lack of an authentication system when you add external Feature Services (Feature Services that are not hosted on an ArcGIS system) in ArCGIS Online, you should add the Denodo ArcGIS Layer service with the authentication disabled. Therefore, we recommend using the IP filtering feature to allow the connection to the ArcGIS Layer service only from a specified IP (or a list of IPs) that should correspond to sites that you trust.

After these considerations, in order to publish the Denodo ArcGIS Layer service, you should select the Map tab and add the REST endpoint URL of the service selecting the Add layer from URL option.

Once you click on the Add to map button, the spatial data included in the Denodo view referenced in the URL will be displayed on the map:

Furthermore, you can add custom parameters in the Web service window, before adding the feature service. However, you can also add them directly to the endpoint URL and this enables you to set parameters for Denodo views with mandatory input parameters.

.../denodo_office/FeatureServer?where=<INPUT_PARAMETER_CONDITION>

Example:

.../denodo_office/FeatureServer?where=input01=10