Denodo GeoJSON Service - User Manual

Overview

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.

The Denodo GeoJSON Service is a web service that can be used to query the views within a Virtual DataPort Server and return GeoJSON objects of the results. The endpoints of the service can be used by applications seeking GeoJSON objects from VDP results. If the view contains a designated field of geometric type, it will be incorporated in the GeoJSON object as a geometry type.

Execution

The Denodo GeoJSON Service distribution consists of:

  • A bin folder containing denodo-geojson-service-<VERSION>.jar

  • A documentation folder containing this user manual.

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

  • A bin folder with the necessary scripts to launch the application.

For running the Denodo GeoJSON Service, you need to execute the denodo_geojson_service.bat/.sh

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

  • default.rowlimit: number of returned entries per request (see Pagination section for more information)

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

  • enable.adminUser: false if access to the GeoJSON service is denied to the VDP admin user.

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

# JNDI data source

spring.datasource.jndi-name=java:comp/env/jdbc/VDPdatabase

# 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

Once the war is deployed in your web container you can use the Denodo GeoJSON Service from a web client, using HTTP Basic Authentication with VDP-valid credentials. URLs are of the form:

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

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

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 GeoJSON Service, follow these steps to enable the CORS support:

  1. Edit the application.properties file which is at the WEB-INF folder. If you have deployed the Denodo GeoJSON Service into the Denodo embedded web container the path to the directory is <DENODO_HOME>/resources/apachetomcat/webapps/denodo-geojson-service-<VERSION>/WEB-INF/classes

  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

Features

The GeoJSON Denodo 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 the pattern:

.../denodo-geojson-service/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 locales database: bv_corp_offices, bv_dist_centers, and bv_retail.

.../denodo-geojson-service/geojson/locales

{

    "views": [

        {

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

                "database_name": "locales",

                "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": "locales",

                "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": "locales",

                "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:

.../denodo-geojson-service/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:

.../denodo-geojson-service/geojson/locales/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"

}

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/locales/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:

.../denodo-geojson-service/geojson/<DBNAME>/viewName/<keyvalue>

Examples:

.../denodo-geojson-service/geojson/locales/views/bv_corp_offices/1

.../denodo-geojson-service/geojson/locales/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:

.../denodo-geojson-service/geojson/locales/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/locales/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/locales/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 allows 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), the null literal, numbers or boolean values.

Denodo GeoJSON service supports the following operations and functions:

Operators

Operator

Description

 Example

=

Equal

/bv_retail?$filter=state = 'WASHINGTON'

<>

Not equal

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

>

Greater than

Less 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'

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/locales/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 identified 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:

.../denodo-geojson-service/geojson/locales/views/bv_denodo_offices?

$orderby=zipcode

.../denodo-geojson-service/geojson/locales/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:

.../denodo-geojson-service/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:

.../denodo-geojson-service/geojson/<DBNAME>/views/<VIEWNAME>?

$count=<POS_INT_VALUE>

Example:

.../denodo-geojson-service/geojson/locales/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:

.../denodo-geojson-service/geojson/<DBNAME>/views/<VIEWNAME>?

$start_index=<POS_INT_VALUE>

Example:

.../denodo-geojson-service/geojson/locales/views/bv_corp_offices?

$star_index=2

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