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