Advanced API features

>>> import requests
>>> data = { "query": { "columns": [ { "name":"id", "data_type": "bigserial", "is_nullable": "NO" },{ "name":"name", "data_type": "varchar", "character_maximum_length": "50" },{ "name":"geom", "data_type": "geometry(point)" } ], "constraints": [ { "constraint_type": "PRIMARY KEY", "constraint_parameter": "id" } ] } }
>>> requests.put(oep_url+'/api/v0/schema/sandbox/tables/example_table/', json=data, headers={'Authorization': 'Token %s'%your_token} )
<Response [201]>
>>> data = {"query": [{"id": i, "name": "John Doe"+str(i), "geom":"SRID=32140;POINT(0 %d)"%i} for i in range(10)]}
>>> requests.post(oep_url+'/api/v0/schema/sandbox/tables/example_table/rows/new', json=data, headers={'Authorization': 'Token %s'%your_token} )
<Response [201]>

The basic REST-API as described in How to work with the API - An example freatures functionalities for simple CRUD-tasks.

This may be sufficient for data manipulation, but the underlying Database Management System features a much richer environment for data select scripts.

You can issue a POST-request to the URL advanced/v0/search. The actual query described as a JSON string inside this request. This page will describe the general make-up of this JSON structure.

Syntax Specification

Select with from

A query object MUST contain

It MAY contain the folowing directives. If not present, they will be replaced by the stated defaults:

  • distrinct: A boolean specifying wheter a SELECT DISTINCT (see https://www.postgresql.org/docs/9.5/static/sql-select.html#SQL-DISTINCT ). (Possible values: true | false, default: false)

  • fields: List of Expressions (If not present, will be interpreted as *), that MAY contain the following additional fields:
    • as: A string

  • where: A list of Expressions that return a truth value (default: [])

  • group_by: List of Expressions (default: [])

  • having: A list of Expressions that return a truth value (default: [])

  • select: A list of dictionaries that MUST contain:
  • order_by: List of Expressions (default: []), that MAY contain the following additional fields:
    • ordering: asc | desc (default: asc)

  • limit: Integer

  • offset: Integer

Expressions

An expression object MUST contain:
  • type: A string as specified below

The depending on the type the dictionary may have a a different structure:
  • column: A column expression MUST contain the following fields:
    • column: Name of the column

  • grouping: A grouping expression MUST contain the following fields:
  • operator: An operator expression MUST contain the following fields:
    • operator: A string consisting of one of the following operators:
      • Unary operators: NOT

      • Binary operators: EQUALS | = GREATER | > | LOWER | < | NOTEQUAL | <> | != | NOTGREATER | <= | NOTLOWER | >=

      • n-ary operators: AND | OR

    • operands: A list of Expressions

  • function: A function expression MUST contain the following fields:
    • function: The name of the function. All functions implemented in sqlalchemy and geoalchemy are available.

    • operands: A list of Expressions

  • value: A constant value

From items

A from object MUST contain:
  • type: A string as specified below

The depending on the type the dictionary may have a a different structure:
  • table: A table item MUST contain the following fields:
    • table: Name of the table

A table item MAY contain the following fields:
  • schema: Name of the schema

  • only: true | false (default: false)

  • select: A select item MUST contain the following fields:
  • join: A join item MUST contain the following fields:

    A join item MAY contain the following fields: * is_outer: true | false (default: false) * is_full: true | false (default: false) * on: An Expression that returns a truth value

Each from item MAY contain the following fields regardless of its type:
  • alias: An alias for this item

Condition items

Condition can come in two different fashions:

  1. A single Expression

  2. A list of Expression

Compound selects

It is also possible to query compound selects (i.e. UNION, EXCEPT, INTERSECT) via the API

  • keyword: Specifies wheter the respective command should be added

    to the query. Possible Values: union, except, intersect

  • selects: A list of sub-queries used in the compound of one of two

    types, identified by their type: * type: Possible values: grouping | select Depending on this type this impies an additional mandatory field: * grouping: A list of Select object

    that are grouped together

Examples

For starters we will issue a simple request to check which data is available. In order to do so, we use the following query:

{
  "fields":[
    "id",
    "name"
  ],
  "from":{
    'type': 'table',
    'table': 'example_table',
    'schema':"sandbox"
  }
}
>>> import requests
>>> data = { "query": {"fields": ["id", "name"], "from":{'type': 'table', 'table': 'example_table', 'schema':"sandbox"}}}
>>> response = requests.post(oep_url+'/api/v0/advanced/search', json=data )
>>> response.status_code
200
>>> response.json().get('data')
[[0, 'John Doe0'], [1, 'John Doe1'], [2, 'John Doe2'], [3, 'John Doe3'], [4, 'John Doe4'], [5, 'John Doe5'], [6, 'John Doe6'], [7, 'John Doe7'], [8, 'John Doe8'], [9, 'John Doe9']]

In order to get all entries with an id less than 3, we could extend above query by a where clause:

'where': {
  'operands': [
    {
      'type': 'column',
      'column':'id'
    },
    3
  ],
  'operator': '<',
  'type': 'operator'
}
>>> import requests
>>> data = { "query": {"fields": ["id", "name"], "from":{'type': 'table', 'table': 'example_table', 'schema':"sandbox"}, 'where': {'operands': [{'type': 'column', 'column':'id'}, 3], 'operator': '<', 'type': 'operator'} }}
>>> response = requests.post(oep_url+'/api/v0/advanced/search', json=data)
>>> response.status_code
200
>>> response.json().get('data')
[[0, 'John Doe0'], [1, 'John Doe1'], [2, 'John Doe2']]

You can add several conditons as a list. Those will be interpreted as a conjunction:

>>> import requests
>>> data = { "query": {"fields": ["id", "name"], "from":{'type': 'table', 'table': 'example_table', 'schema':"sandbox"}, 'where': [{'operands': [{'type': 'column', 'column':'id'}, 3], 'operator': '<', 'type': 'operator'}, {'operands': [{'type': 'column', 'column':'id'}, 1], 'operator': '>', 'type': 'operator'} ] }}
>>> response = requests.post(oep_url+'/api/v0/advanced/search', json=data)
>>> response.status_code
200
>>> response.json().get('data')
[[2, 'John Doe2']]

Functions

You can also alter all functions that are implemented in sqlalchemy and geoalchemy2 to alter the results of your query. In the following example we simply add two to every id:

>>> import requests
>>> data = { "query": {"fields": ['id', {'type': 'function', 'function': '+', 'operands':[{'type': 'column', 'column': 'id'}, 2]}], "from":{'type': 'table', 'table': 'example_table', 'schema':"sandbox"}}}
>>> response = requests.post(oep_url+'/api/v0/advanced/search', json=data)
>>> response.status_code
200
>>> response.json().get('data')
[[0, 2], [1, 3], [2, 4], [3, 5], [4, 6], [5, 7], [6, 8], [7, 9], [8, 10], [9, 11]]

Functions are especially usefull if you want to return geodata in a specific format. In the following we obtain the WKT representation of our data:

>>> import requests
>>> data = { "query": {"fields": ['id', {'type': 'function', 'function': 'ST_AsText', 'operands':[{'type': 'column', 'column': 'geom'}]}], "from":{'type': 'table', 'table': 'example_table', 'schema':"sandbox"}}}
>>> response = requests.post(oep_url+'/api/v0/advanced/search', json=data)
>>> response.status_code
200
>>> data = response.json().get('data')
>>> data[0]
[0, 'POINT(0 0)']
>>> all(geom == 'POINT(0 %d)'%pid for pid, geom in data)
True

… or the geoJSON representation …

>>> import requests
>>> data = { "query": {"fields": ['id', {'type': 'function', 'function': 'ST_AsGeoJSON', 'operands':[{'type': 'column', 'column': 'geom'}, 4236]}], "from":{'type': 'table', 'table': 'example_table', 'schema':"sandbox"}}}
>>> response = requests.post(oep_url+'/api/v0/advanced/search', json=data)
>>> response.status_code
200
>>> data = response.json().get('data')
>>> data[0]
[0, '{"type":"Point","coordinates":[0,0]}']
>>> all(pid == json.loads(geom)['coordinates'][1] for pid, geom in data)
True

Joins

Joins can be queried by using the corresponding from-item:

{
 "from":{
  'type': 'join',
  'left': {
   'type': 'table',
   'table': 'example_table',
   'schema':"sandbox",
   "alias":"a"
   },
  'right': {
   'type': 'table',
   'table': 'example_table',
   'schema':"sandbox",
   "alias":"b"
   },
  'on': {
   'operands': [
    {'type': 'column', 'column':'id', 'table': 'a'},
    {'type': 'column', 'column':'id', 'table': 'b'}
    ],
   'operator': '<',
   'type': 'operator'
   }
  }
 }
>>> import requests
>>> data = { "query": {"from":{'type': 'join','left': {'type': 'table', 'table': 'example_table', 'schema':"sandbox", "alias":"a"},'right': {'type': 'table', 'table': 'example_table', 'schema':"sandbox", "alias":"b"},'on': {'operands': [{'type': 'column', 'column':'id', 'table': 'a', 'schema':'sandbox',}, {'type': 'column', 'column':'id', 'table': 'b', 'schema':'sandbox',}], 'operator': '<', 'type': 'operator'}}}}
>>> response = requests.post(oep_url+'/api/v0/advanced/search', json=data)
>>> response.status_code
200
>>> response.json().get('data')
[[0, 'John Doe0', '01010000208C7D000000000000000000000000000000000000', 1, 'John Doe1', '01010000208C7D00000000000000000000000000000000F03F'], [0, 'John Doe0', '01010000208C7D000000000000000000000000000000000000', 2, 'John Doe2', '01010000208C7D000000000000000000000000000000000040'], [0, 'John Doe0', '01010000208C7D000000000000000000000000000000000000', 3, 'John Doe3', '01010000208C7D000000000000000000000000000000000840'], [0, 'John Doe0', '01010000208C7D000000000000000000000000000000000000', 4, 'John Doe4', '01010000208C7D000000000000000000000000000000001040'], [0, 'John Doe0', '01010000208C7D000000000000000000000000000000000000', 5, 'John Doe5', '01010000208C7D000000000000000000000000000000001440'], [0, 'John Doe0', '01010000208C7D000000000000000000000000000000000000', 6, 'John Doe6', '01010000208C7D000000000000000000000000000000001840'], [0, 'John Doe0', '01010000208C7D000000000000000000000000000000000000', 7, 'John Doe7', '01010000208C7D000000000000000000000000000000001C40'], [0, 'John Doe0', '01010000208C7D000000000000000000000000000000000000', 8, 'John Doe8', '01010000208C7D000000000000000000000000000000002040'], [0, 'John Doe0', '01010000208C7D000000000000000000000000000000000000', 9, 'John Doe9', '01010000208C7D000000000000000000000000000000002240'], [1, 'John Doe1', '01010000208C7D00000000000000000000000000000000F03F', 2, 'John Doe2', '01010000208C7D000000000000000000000000000000000040'], [1, 'John Doe1', '01010000208C7D00000000000000000000000000000000F03F', 3, 'John Doe3', '01010000208C7D000000000000000000000000000000000840'], [1, 'John Doe1', '01010000208C7D00000000000000000000000000000000F03F', 4, 'John Doe4', '01010000208C7D000000000000000000000000000000001040'], [1, 'John Doe1', '01010000208C7D00000000000000000000000000000000F03F', 5, 'John Doe5', '01010000208C7D000000000000000000000000000000001440'], [1, 'John Doe1', '01010000208C7D00000000000000000000000000000000F03F', 6, 'John Doe6', '01010000208C7D000000000000000000000000000000001840'], [1, 'John Doe1', '01010000208C7D00000000000000000000000000000000F03F', 7, 'John Doe7', '01010000208C7D000000000000000000000000000000001C40'], [1, 'John Doe1', '01010000208C7D00000000000000000000000000000000F03F', 8, 'John Doe8', '01010000208C7D000000000000000000000000000000002040'], [1, 'John Doe1', '01010000208C7D00000000000000000000000000000000F03F', 9, 'John Doe9', '01010000208C7D000000000000000000000000000000002240'], [2, 'John Doe2', '01010000208C7D000000000000000000000000000000000040', 3, 'John Doe3', '01010000208C7D000000000000000000000000000000000840'], [2, 'John Doe2', '01010000208C7D000000000000000000000000000000000040', 4, 'John Doe4', '01010000208C7D000000000000000000000000000000001040'], [2, 'John Doe2', '01010000208C7D000000000000000000000000000000000040', 5, 'John Doe5', '01010000208C7D000000000000000000000000000000001440'], [2, 'John Doe2', '01010000208C7D000000000000000000000000000000000040', 6, 'John Doe6', '01010000208C7D000000000000000000000000000000001840'], [2, 'John Doe2', '01010000208C7D000000000000000000000000000000000040', 7, 'John Doe7', '01010000208C7D000000000000000000000000000000001C40'], [2, 'John Doe2', '01010000208C7D000000000000000000000000000000000040', 8, 'John Doe8', '01010000208C7D000000000000000000000000000000002040'], [2, 'John Doe2', '01010000208C7D000000000000000000000000000000000040', 9, 'John Doe9', '01010000208C7D000000000000000000000000000000002240'], [3, 'John Doe3', '01010000208C7D000000000000000000000000000000000840', 4, 'John Doe4', '01010000208C7D000000000000000000000000000000001040'], [3, 'John Doe3', '01010000208C7D000000000000000000000000000000000840', 5, 'John Doe5', '01010000208C7D000000000000000000000000000000001440'], [3, 'John Doe3', '01010000208C7D000000000000000000000000000000000840', 6, 'John Doe6', '01010000208C7D000000000000000000000000000000001840'], [3, 'John Doe3', '01010000208C7D000000000000000000000000000000000840', 7, 'John Doe7', '01010000208C7D000000000000000000000000000000001C40'], [3, 'John Doe3', '01010000208C7D000000000000000000000000000000000840', 8, 'John Doe8', '01010000208C7D000000000000000000000000000000002040'], [3, 'John Doe3', '01010000208C7D000000000000000000000000000000000840', 9, 'John Doe9', '01010000208C7D000000000000000000000000000000002240'], [4, 'John Doe4', '01010000208C7D000000000000000000000000000000001040', 5, 'John Doe5', '01010000208C7D000000000000000000000000000000001440'], [4, 'John Doe4', '01010000208C7D000000000000000000000000000000001040', 6, 'John Doe6', '01010000208C7D000000000000000000000000000000001840'], [4, 'John Doe4', '01010000208C7D000000000000000000000000000000001040', 7, 'John Doe7', '01010000208C7D000000000000000000000000000000001C40'], [4, 'John Doe4', '01010000208C7D000000000000000000000000000000001040', 8, 'John Doe8', '01010000208C7D000000000000000000000000000000002040'], [4, 'John Doe4', '01010000208C7D000000000000000000000000000000001040', 9, 'John Doe9', '01010000208C7D000000000000000000000000000000002240'], [5, 'John Doe5', '01010000208C7D000000000000000000000000000000001440', 6, 'John Doe6', '01010000208C7D000000000000000000000000000000001840'], [5, 'John Doe5', '01010000208C7D000000000000000000000000000000001440', 7, 'John Doe7', '01010000208C7D000000000000000000000000000000001C40'], [5, 'John Doe5', '01010000208C7D000000000000000000000000000000001440', 8, 'John Doe8', '01010000208C7D000000000000000000000000000000002040'], [5, 'John Doe5', '01010000208C7D000000000000000000000000000000001440', 9, 'John Doe9', '01010000208C7D000000000000000000000000000000002240'], [6, 'John Doe6', '01010000208C7D000000000000000000000000000000001840', 7, 'John Doe7', '01010000208C7D000000000000000000000000000000001C40'], [6, 'John Doe6', '01010000208C7D000000000000000000000000000000001840', 8, 'John Doe8', '01010000208C7D000000000000000000000000000000002040'], [6, 'John Doe6', '01010000208C7D000000000000000000000000000000001840', 9, 'John Doe9', '01010000208C7D000000000000000000000000000000002240'], [7, 'John Doe7', '01010000208C7D000000000000000000000000000000001C40', 8, 'John Doe8', '01010000208C7D000000000000000000000000000000002040'], [7, 'John Doe7', '01010000208C7D000000000000000000000000000000001C40', 9, 'John Doe9', '01010000208C7D000000000000000000000000000000002240'], [8, 'John Doe8', '01010000208C7D000000000000000000000000000000002040', 9, 'John Doe9', '01010000208C7D000000000000000000000000000000002240']]