Importing data from a URL

Buzzy allows data to be imported into a Microapp using an external REST service. Select the Microapp properties, Advanced Tab and then Data Source section. Ensure the URL import radio button is selected. The Data Source Configuration is specified as a JSON document using the “Code” input method. Verify the configuration document works by using the PREVIEW button.

Data Source Configuration JSON

The Objects defined in the document are used to create the request to the external REST service as well as transform the results into the desired format.

These objects include:

• variables - used to minimize editing in the configuration document by allowing the user to specify local variables, for example host.

• source - metadata associated with the REST server, including URL, method and page size.

• params - URL parameters

• auth - authentication information

• transforms - JSONata code to apply transformations

• fields - used to identify destination fields in the Microapp

The document has mandatory objects that must be present even if they are not used: source, auth and transforms.

Generic Example

The following uses an example external REST service: {JSON} Placeholder.

Steps:

1. Create an empty Application (Microapp)

2. Select Properties, Advanced, Data Source and URL.

3. Change the input from form to code

4. Enter the following code:

   {
     "source": {
    "description": "Example import from external REST server",
    "type": "url",
    "config": {
      "url": "https://jsonplaceholder.typicode.com/todos/",
      "method": "GET",
      "paging": {
        "param": "page",
        "check": "$count(features)>0"
      },
      "auth": {
        "user": null,
        "pass": null
      }
    },
    "transforms": []
     },
     "fields": [
    {
      "key": "id",
      "type": "Number",
      "unique": true
    }
     ]
   }

5. click on PREVIEW The result should be the output of the REST service.

6. Click on IMPORT

The result will be the the Microapp fields are created and the records populated.

Results window:

Field Defintions:

Data uploaded:

Precisely Spectrum Example

The following import script loads a Microapp with data from a Precisely Spectrum server:

{
  "variables": {
    "host": "demo.spectrumspatial.com",
    "tableName": "/Buzzy/locate/melb_trees_demo",
    "box": "145.05935668945312, -37.88135717974858, 144.8011779785156, -37.73379707124428",
    "limit": 100
  },
  "secrets": {
    "username": "xxx",
    "password": "xxx"
  },
  "source": {
    "description": "Get data from Precisely Spectrum Server",
    "type": "url",
    "config": {
      "url": "https://{{host}}/rest/Spatial/FeatureService/tables/features.json",
      "method": "GET",
      "paging": {
        "param": "page",
        "check": "$count(features)>0"
      },
      "params": {
        "q": "SELECT * FROM \"{{{tableName}}}\" WHERE MI_Intersects(Obj, MI_Box({{box}},'epsg:4326')) LIMIT {{{limit}}}",
        "pageLength": 1000,
        "page": 1
      },
      "auth": {
        "user": "{{username}}",
        "pass": "{{password}}"
      },
      "headers": {},
      "body": {}
    },
    "transforms": [
      "features.{\"id\":id, \"geometry\":geometry, \"properties\":properties}",
      "$~>|*|$merge([properties])|",
      "$.($each(function($v, $k){$k=\"properties\" or $k=\"MI_Style\"?{}:{$k:$v}}) ~> $merge())"
    ]
  },
  "fields": [
    {
      "key": "id",
      "type": "Number",
      "unique": true
    },
    {
      "key": "geometry",
      "type": "GeoJSON"
    }
  ]
}

The JSON objects specific to the Precisely Spectrum server include:

• variables - The Precisely server, Spectrum tablename, a bounding box in Long/Lat used in a query to filter the original data based on area, and a limit also used in the query.

• secrets - The username and password to authenticate into the Precisely server.

• source - metadata associated with the Precisely server, including URL, method and page parameters. These page parameters include the precisely REST parameter page which is used to control the paging cursor and well as the exit criteria.

• params - The initial URL parameters including the MISQL query, page length (number of features per page) and the initial page number.

• transforms - JSONata code to apply transformations. Note that this transform automaticaly extracts all of the fields found in the properties GeoJSON document.

Oracle Apex Example

The following import script loads a Microapp with data from an Oracle Apex server:

{
  "source": {
    "description": "Postcode import from Oracle/Apex server",
    "type": "url",
    "config": {
      "url": "https://oracleapex.com:8443/ords/scott/pcode/req",
      "method": "GET",
      "paging": {
        "param": "offset",
        "check": "hasMore",
        "increment": 1000,
        "start": 0
      },
      "auth": {
        "user": "xxx",
        "pass": "xxx"
      },
      "params": {
        "limit": 1000,
        "offset": 0
      }
    },
    "transforms": [
      "$[].items.{'postcode':postcode,'geometry':$eval($replace($replace(geometry, \"[-.\", \"[-0.\"),\"[.\",\"[0.\"))}"
    ]
  },
  "fields": [
    {
      "key": "postcode",
      "type": "string",
      "unique": true
    },
    {
      "key": "geometry",
      "type": "GeoJSON"
    }
  ]
}

The JSON objects specific to Oracle Apex include:

• source - Contains the URL of the Apex server as well as a reference to the underlying table. The paging mechanism is unique to Apex and uses a hasMore boolean; that tells us if there is more data to retrieve. The variables offset and limit are also Oracle-specific. limit controls the number of objects to n. offset skips n objects before getting first object. The increment value specifies the count to advance. Increment and limit should be set to the same value. The start value should be set to 0 for Oracle Apex.

• transforms - The Apex server does not output leading 0’s on numbers. This causes a parsing syntax error when transforming the document for Buzzy consumption. To avoid issues at the Prime Meridian, this example checks formissing leading 0’s and adds them into longitude coordinates.

---