Data Mapping


A Data Mapping describes how values from a source record become values on a target record. It is a list of connections from source attributes to target attributes, with optional functions in between that change a value on its way across.

The same mapping shape is used everywhere we move data between schemas: file and source imports (CSV, GeoJSON, JSON, KML, XLSX, WFS and others), Data Transforms run over existing Entities, alternative Data Schemas, Entity Type sources, and the DataLab merge and Entity Type mapping actions. In Operator it is drawn in the schema editor, with the source schema on the left, the target schema on the right and functions in the middle.

This article will cover the structure of a Data Mapping, how we run one, and the settings of each mapping function. You can read about saving a mapping as a reusable record in Data Transforms, and about the schemas on either side in Entity Type: Data Schema.

Data model

Below is the data model for a Data Mapping. A mapping without functions is a plain list of connections between the source and target schemas.

interface IDataMapping {
    // Connections that decide where each target value comes from.
    Connections: IConnection[];
    // Functions referenced by the connections.
    // See the mapping functions section below.
    Functions?: IFunction[];
    // Set to true to also copy every top-level source attribute that no connection reads, under its own key.
    // Source attributes named "Bruce" or "geometry" are never copied this way.
    // Default is false.
    IsCopyUnmappedAttributes?: boolean;
}

interface IConnection {
    // Where the value comes from.
    Left: IConnectionEnd;
    // Where the value goes.
    Right: IConnectionEnd;
    // Marks this connection as part of the key used to find existing records.
    // See the foreign keys section below.
    // Default is false.
    IsForeignKey?: boolean;
}

interface IConnectionEnd {
    /**
     * left = an attribute of the source schema. Only valid on the "Left" end.
     * right = an attribute of the target schema. Only valid on the "Right" end.
     * function = a function from the "Functions" list.
     */
    Panel: "left" | "right" | "function";
    // For "left" and "right", the attribute path.
    // Attribute path segments are separated by a forward slash, each segment wrapped in double quotes.
    // Eg: "\"address\"/\"city\"".
    // For "function", the ID of the function.
    Path: string;
    // For "left", always "out".
    // For "right", always "in".
    // For "function", the name of the function input (on a "Right" end) or output (on a "Left" end).
    Key: string;
}

Operator also saves fields for its own editor on the mapping and on each function, such as "Position", "Title", "Notes", "Outputs" and "LogicFlowDiagrams". We do not read them when running the mapping.

How a mapping runs

We run the mapping once per source record and produce one target record. Only connections whose "Right" end is in the "right" panel write a value. A function only runs when its output leads, directly or through other functions, to a target attribute.

  • Connections are resolved in order of target path depth, so a connection to a whole structure is written before connections to attributes inside it. You can copy a structure across and then replace one of its children.
  • A source path can address an item in a list with a numeric segment. Eg: the "0" segment in "location/coordinates/0".
  • When a source attribute is not found and its key contains an underscore, we try the same key with spaces instead. Eg: "Asset_ID" also finds "Asset ID".
  • When a value comes out empty (null), the target attribute is left out rather than set to null.
  • Date time values produced by a function are written as ISO 8601 strings in UTC, except under "Bruce".
  • KML, WFS and Home Assistant imports convert a text value into a number when the target attribute is a "Double" or "Integer". Other uses write the value as it arrives. To turn text into a number elsewhere, pass it through a Units conversion function with a "Factor" of 1.

Write attribute paths in the quoted form Operator uses, as shown above. Unquoted paths are read the same way, but "IsCopyUnmappedAttributes" only recognises a source attribute as mapped when its path is quoted, so an unquoted one is copied across a second time under its own key.

Reserved target attributes

A few target paths under "Bruce" are treated differently:

  • "Bruce/VectorGeometry": a text value is read as Nextspace geometry JSON, or as WKT when it is not JSON.
  • "Bruce/Location": when the value is a geometry (from the source "Bruce/VectorGeometry", a function that outputs geometry, or WKT text), we write the average of its points as the location.
  • "Bruce/Boundaries": when the value is a geometry, we write the minimum and maximum latitude, longitude and altitude of its points.

A mapping cannot decide the identity or audit trail of the record it writes, so these values are dropped from its output:

  • Imports drop "Bruce/InternalID", "Bruce/EntityType.ID", "Bruce/Created", "Bruce/Updated" and "Bruce/CreatedBy.User.ID". An import can set "Bruce/ID" to give a new Entity its ID.
  • Data Transforms, alternative schemas and other uses drop all of the above and "Bruce/ID" as well.

Foreign keys

Set "IsForeignKey" on the connections that identify a record. When an import runs in an update mode, we look for existing Entities in the target Entity Type whose values equal the mapped values at every foreign key target path, and update all that match. The foreign key values themselves are not written, so the matched Entities keep their original values.

If no connection is a foreign key, an update matches on the Entity ID instead: the mapped "Bruce/ID", or a top-level "ID" attribute. Without either, no existing Entity is matched.

Example mapping

This mapping copies "code" across as a foreign key, joins "first_name" and "last_name" into "full_name", and copies every other source attribute as it is.

{
    "IsCopyUnmappedAttributes": true,
    "Functions": [
        {
            "ID": "join-name",
            "Function": "Concatenate",
            "Inputs": ["A", "B"],
            "Settings": {
                "Delimiter": " "
            }
        }
    ],
    "Connections": [
        {
            "Left": { "Panel": "left", "Path": "\"code\"", "Key": "out" },
            "Right": { "Panel": "right", "Path": "\"code\"", "Key": "in" },
            "IsForeignKey": true
        },
        {
            "Left": { "Panel": "left", "Path": "\"first_name\"", "Key": "out" },
            "Right": { "Panel": "function", "Path": "join-name", "Key": "A" }
        },
        {
            "Left": { "Panel": "left", "Path": "\"last_name\"", "Key": "out" },
            "Right": { "Panel": "function", "Path": "join-name", "Key": "B" }
        },
        {
            "Left": { "Panel": "function", "Path": "join-name", "Key": "Result" },
            "Right": { "Panel": "right", "Path": "\"full_name\"", "Key": "in" }
        }
    ]
}

Mapping functions

A function takes one or more input values, changes them, and offers one or more outputs. A connection with a "function" panel on its "Right" end feeds a function input, and one with a "function" panel on its "Left" end reads a function output. Functions can feed other functions, so you can chain them.

Settings go in the function's "Settings" object. The exceptions are "Inputs", which sits at the top level on functions that let you name their inputs, and the Calculate function's "Formula".

interface IFunction {
    // Unique within this Data Mapping.
    // Connections reference the function by this value.
    ID: string;
    // The kind of function.
    // See the sections below for each one.
    Function: "Concatenate" | "StringSplit" | "StringReplace" | "RegexMatch" | "FallbackSelection" | "Calc" |
              "UnitsConversion" | "DateTimeConversion" | "DaysToDate" | "DictionaryMapping" | "EntityLookup" |
              "Geocode" | "ReverseGeocode" | "SwapLatLong" | "CRSConversion" | "GeometryMeasurement";
    // Input names, on functions that take a variable number of inputs.
    Inputs?: string[];
    // Settings specific to the function.
    Settings?: any;
}

Where a section below says "any other output name", the function returns the same value whatever output name the connection uses. Operator uses the name given in brackets.

Function: Concatenate

Joins several input values into one text value. Empty and missing values are skipped, so no delimiter is left at the ends. Read the joined value from the "Result" output.

interface IConcatenateFunction {
    ID: string;
    Function: "Concatenate";
    // Input names, in the order their values are joined.
    // Default is ["A", "B"].
    Inputs?: string[];
    Settings?: {
        // Text placed between values.
        // Set "" to join without a delimiter.
        // Default is " ".
        Delimiter?: string;
    };
}

Function: Split

Splits the "Input" value into parts. Each part is trimmed and offered as an output named by its index, starting at "0". The "Result" output gives all parts as a list.

A missing index gives an empty string. If the split fails, the "0" output gives the original value. A value that is not text is passed through unchanged on "0".

interface IStringSplitFunction {
    ID: string;
    Function: "StringSplit";
    Settings?: {
        // Java regular expression to split on.
        // Plain text such as "," works for most delimiters.
        // Default is " ".
        splitRegex?: string;
    };
}

Function: Replace

Runs a find and replace on the "Input" value, and returns the result on any other output name ("Output"). An empty input is treated as an empty string, so you can use this to fill blanks. A value that is not text is passed through unchanged.

interface IStringReplaceFunction {
    ID: string;
    Function: "StringReplace";
    Settings: {
        // Java regular expression to find.
        // Required, the function cannot run without it.
        replaceRegex: string;
        // Text to put in place of each match.
        // Supports group references such as "$1".
        // Default is "", which removes the match.
        replaceValue?: string;
    };
}

Function: Regex match

Tests whether the "Input" value contains a match for a regular expression. The "Match" output gives true when it does, and any other output name ("NotMatch") gives the opposite. Values that are not text are tested as text, and an empty input is tested as an empty string.

We check the expression when the mapping is loaded. A missing or invalid expression stops the mapping from running.

interface IRegexMatchFunction {
    ID: string;
    Function: "RegexMatch";
    Settings: {
        // Java regular expression, matched anywhere in the value.
        // Required.
        matchRegex: string;
    };
}

Function: Fallback selection

Returns the first input, in order, whose value is not null or an empty string. The "Source" output gives the name of the input that was used, and any other output name ("Result") gives its value.

interface IFallbackSelectionFunction {
    ID: string;
    Function: "FallbackSelection";
    // Input names, in the order they are checked.
    // Default is ["A", "B"].
    Inputs?: string[];
}

Function: Calculate

Evaluates a JavaScript expression with the Nashorn engine and returns the result on the "Result" output. Each input is referenced in the formula by its name with a "$" prefix, so the input "A" is "$A".

Each placeholder is replaced with the text of the value before the formula runs, without quotes. Put quotes around a placeholder in the formula when the value is text. A missing value is replaced with the text "<NULL>", which usually makes the formula invalid. An invalid formula gives no value.

Keep "Formula" at the top level of the function as Operator does. If the function has a "Settings" object, we read the formula from that object instead.

interface ICalcFunction {
    ID: string;
    Function: "Calc";
    // Input names. Each is available in the formula with a "$" prefix.
    // Default is ["A", "B"].
    Inputs?: string[];
    // JavaScript expression to evaluate.
    // Eg: "$A * 1000", "Math.round($A / $B)" or "'$A'.toUpperCase()".
    Formula: string;
}

Function: Units conversion

Multiplies the "Input" value by a factor and returns the result as a number on any other output name ("Output"). Numbers written as text are parsed first. A value that cannot be read as a number is passed through unchanged.

Operator also saves the chosen units as "MeasureOf", "FROM" and "TO" so its editor can show them. Only "Factor" is used when the mapping runs.

interface IUnitsConversionFunction {
    ID: string;
    Function: "UnitsConversion";
    Settings: {
        // Number to multiply the input by.
        // Eg: 0.3048 to turn feet into metres.
        Factor: number;
    };
}

Function: Date time conversion

Reads the "Input" value as a date time in a format you specify, and returns it on any other output name ("Output"). The result is stored as an ISO 8601 string in UTC. If your values are already ISO 8601, you can map them directly without this function.

When a value cannot be read, the function returns the original value unless you set a "defaultValue". This keeps the data rather than losing it.

interface IDateTimeConversionFunction {
    ID: string;
    Function: "DateTimeConversion";
    Settings: {
        // Joda-Time pattern the input is written in.
        // Eg: "dd/MM/yyyy HH:mm:ss".
        // Required, the function cannot run without it.
        format: string;
        // Time zone of the input values, as an offset from UTC.
        // Eg: "+12", "+10:30" or "-05".
        // Set "UTC", or leave this out, when the values are already in UTC.
        timezoneCode?: string;
        // Java regular expression to replace in the input before it is read.
        // Eg: "Date: " to read "Date: 05/12/2024".
        replaceRegex?: string;
        // Text to put in place of each "replaceRegex" match.
        // Default is "".
        replaceValue?: string;
        // Value to return when the input cannot be read.
        defaultValue?: any;
        // Set to true to return the original input when it cannot be read.
        // Default is true when "defaultValue" is not set, otherwise false.
        defaultToOriginal?: boolean;
    };
}

Function: Days to date

Returns how many days, hours or minutes lie between the "Input" date time and now, as a whole number on any other output name ("Output"). Both times are rounded down to the unit before they are compared.

The input can be an ISO 8601 string, a "dd/MM/yyyy" string with an optional " HH:mm:ss" time, or a number of milliseconds since 1970. Anything else gives no value.

interface IDaysToDateFunction {
    ID: string;
    Function: "DaysToDate";
    Settings?: {
        // Set to true to count the time until the date, which is positive when the date is in the future.
        // Set to false to count the time since the date, which is positive when the date is in the past.
        // Default is true.
        isBefore?: boolean;
        /**
         * Size of one unit, in minutes.
         * 1440 = days.
         * 60 = hours.
         * 1 = minutes.
         * Default is 1440.
         */
        granularity?: number;
    };
}

Function: Dictionary mapping

Looks up the "Input" value in an Entity Type you use as a dictionary, and returns the value of another attribute of the matching Entity. Eg: an Entity Type of codes and descriptions, where the code comes in and the description goes out.

The outputs are "Match" (or "Mapped"), true when an entry was found, "NotMatch" (or "NotMapped"), true when none was, and any other output name ("Output") for the found value. Text, number and date time inputs are looked up as text. Other inputs find nothing.

With the "Equals" operator, auto-population adds an Entity to the dictionary for each value that is not found, with both attributes set to the input value, and the function returns the input value. Auto-population does nothing with the other operators, and Operator turns it off when you pick one.

interface IDictionaryMappingFunction {
    ID: string;
    Function: "DictionaryMapping";
    Settings: {
        // The Entity Type holding the dictionary entries.
        "EntityType.ID": string;
        // Attribute of the dictionary Entities that the input is compared with.
        // Attribute path segments are separated by a forward slash. Eg: 'address/city'.
        "SourceAttribute.Path": string;
        // Attribute of the dictionary Entities whose value is returned.
        // Attribute path segments are separated by a forward slash. Eg: 'address/city'.
        "TargetAttribute.Path": string;
        /**
         * How the input is compared with "SourceAttribute.Path".
         * Equals = the values are the same.
         * Contains = the dictionary value contains the input.
         * ContainedBy = the input contains the dictionary value.
         * ContainsBiDir = either contains the other.
         * StartsWith = the dictionary value starts with the input.
         * EndsWith = the dictionary value ends with the input.
         * Default is "Equals".
         * With any operator except Equals, when several dictionary values match, we use the longest.
         */
        Operator?: string;
        // Set to true to add missing values to the dictionary.
        // Default is false.
        AutopopulationEnabled?: boolean;
        // Java regular expression to replace in the input before the lookup.
        // Eg: "[^a-zA-Z0-9]" to remove punctuation and spaces.
        ValueReplaceRegex?: string;
        // Text to put in place of each "ValueReplaceRegex" match.
        // Default is "".
        ValueReplaceValue?: string;
    };
}

Function: Entity lookup

Finds an Entity by one or more input values and returns attributes of it. Each input is compared with an attribute of the Entities through its own criterion, and the first Entity that meets all of them is used. An input with no value is left out of the search. When every input is empty, no search is made.

The outputs are "Match" (or "Matched"), "NotMatch" (or "NotMatched"), and one output per entry of "OutputAttributes", named by its index from "0".

Auto-population creates an Entity for each combination of inputs that finds nothing, with each input written to its criterion's attribute, and returns the outputs from the new Entity. It needs "EntityType.ID" to be set.

interface IEntityLookupFunction {
    ID: string;
    Function: "EntityLookup";
    // Input names. Each needs a matching entry in "InputCriteria".
    Inputs: string[];
    Settings: {
        // The Entity Type to search.
        "EntityType.ID": string;
        // One criterion per input, keyed by the input name.
        // A function with no criteria never searches.
        InputCriteria: {
            [inputName: string]: {
                // Attribute of the Entities that the input is compared with.
                // Attribute path segments are separated by a forward slash. Eg: 'address/city'.
                TargetAttributePath: string;
                // Filter operator used for the comparison.
                // Operator offers "Equals", "Contains", "ContainedBy", "ContainsBiDir", "StartsWith" and "EndsWith".
                // Default is "Equals".
                Operator?: string;
            };
        };
        // Attributes to return from the found Entity, in output order.
        // Default is ["Bruce/ID"].
        OutputAttributes?: string[];
        // Set to true to create Entities for lookups that find nothing.
        // Default is false.
        AutoPopulate?: boolean;
    };
}

Function: Geocode

Turns the "Address" input into a position with the Google Geocoding API. The "Longitude" output gives the longitude, and any other output name ("Latitude") gives the latitude. An address that cannot be found gives no value.

The function uses the account's Google API key. You can read about it in Google. It has no settings.

interface IGeocodeFunction {
    ID: string;
    Function: "Geocode";
}

Function: Reverse geocode

Turns the "Latitude" and "Longitude" inputs into an address with the Google Geocoding API, using the account's Google API key. Both inputs can be numbers or numbers written as text. It has no settings.

The outputs are "Address" (the full formatted address), "Street Number", "Street Name", "Suburb", "City", "State", "Country" and "Postcode". "State Code", "Country Code" and "Continent" are also available, although Operator does not draw them.

interface IReverseGeocodeFunction {
    ID: string;
    Function: "ReverseGeocode";
}

Function: Swap lat/long

Swaps the first two numbers of every coordinate in the "Input" value, for data that lists latitude before longitude. It returns the result on any other output name ("Output"). It accepts WKT or other coordinate text, and Nextspace geometry, where it swaps each "Point", "LineString" and "LinearRing". Altitude stays in place. It has no settings.

interface ISwapLatLongFunction {
    ID: string;
    Function: "SwapLatLong";
}

Function: CRS conversion

Converts coordinates from one coordinate reference system to another, and returns the result on any other output name ("Output"). It takes either a whole geometry on the "Input" input, as WKT or Nextspace geometry, or single numbers on the "X", "Y" and optional "Z" inputs. When "Input" has a value, "X", "Y" and "Z" are ignored.

From "X" and "Y" the result is a Nextspace geometry with one "Point". Within a whole geometry, we convert "Polygon", "LinearRing", "MultiGeometry" and "MultiSurface" coordinates. Operator lists this function as under maintenance.

interface ICRSConversionFunction {
    ID: string;
    Function: "CRSConversion";
    Settings: {
        // EPSG code of the input coordinates. Eg: "EPSG:2193".
        // Required, the function gives no value without it.
        FromCRS: string;
        // EPSG code to convert to. Eg: "EPSG:4326".
        // Required, the function gives no value without it.
        ToCRS: string;
        // Origin to add to "X", "Y" and "Z" before converting, for coordinates relative to a local origin.
        // Given in the "ToCRS" system, then converted into the "FromCRS" system.
        SetGeolocationOrigin?: {
            Longitude: number;
            Latitude: number;
            Altitude: number;
        };
    };
}

Function: Geometry measurement

Measures the area and perimeter of the polygons in the "Input" geometry, given as WKT or Nextspace geometry. The "Area" output gives the area and any other output name ("Perimeter") gives the perimeter.

Holes (rings facing "in") are taken off the area but count towards the perimeter. All parts of a "MultiGeometry" are added together. Points and lines measure as 0.

interface IGeometryMeasurementFunction {
    ID: string;
    Function: "GeometryMeasurement";
    Settings?: {
        /**
         * Units of the outputs.
         * m = square metres and metres.
         * km = square kilometres and kilometres.
         * ft = square feet and feet.
         * mi = square miles and miles.
         * yd = square yards and yards.
         * ha = hectares and metres.
         * Default is "m".
         */
        Units?: "m" | "km" | "ft" | "mi" | "yd" | "ha";
        /**
         * How the coordinates are read.
         * auto = as longitude and latitude when every coordinate is within -180 to 180 and -90 to 90, otherwise as metres.
         * wgs84 = as longitude and latitude.
         * projected = as metres on a flat plane.
         * Default is "auto".
         */
        InputCoordinateSystem?: "auto" | "wgs84" | "projected";
    };
}

API utilities

You can ask us to analyse sample data and suggest a source schema, a target schema and a mapping between them. Operator makes this request when you set up a data source.

Send either "DataSamples", or both "SchemaSource" and "SchemaTarget" to get a mapping between two schemas you already have.

Analyse source data

Request body
interface IRequest {
    // Source records to analyse.
    // Required unless both "SchemaSource" and "SchemaTarget" are given.
    "DataSamples"?: any[];
    // Source schema to use instead of one built from the samples.
    // See the Entity Type Data Schema page for the structure.
    "SchemaSource"?: IDataSchema;
    // Target schema to suggest a mapping into.
    // When the source schema is built from samples, its attributes are merged into this one.
    // If left out, we build one from the source schema, renaming top-level attributes that clash with internal ones.
    "SchemaTarget"?: IDataSchema;
    // Existing mapping to extend rather than replace.
    "DataMapping"?: IDataMapping;
}
Response
interface IResponse {
    // Number of samples that were analysed.
    "CountSampled": number;
    // Source and target schemas.
    "SchemaSource"?: IDataSchema;
    "SchemaTarget"?: IDataSchema;
    // Suggested mapping between them.
    "DataMapping"?: IDataMapping;
    // Problems found while suggesting the mapping.
    "Error"?: string[];
}