Transform - Text: Copy

The Copy template appears in the template list when a variable length text field, of type nvarchar or varchar, has been picked in the Transform pane.  The template extracts different parts of standard text formats, such as parts of JSON, GML, GeoJSON, WKT or URL strings.

 

Copy

Copy into the Result destination the text value or quantity extracted or computed from the source field text.

 

  • geojson coordinate system - extract a coordinate system from a GeoJSON string.
  • geojson geometry - Convert GeoJSON text representation of geometry  into Manifold or WKB binary geometry.
  • gml coordinate system - extract a coordinate system from a GML string.
  • gml geometry - Convert GML text representation of geometry  into Manifold or WKB binary geometry.
  • json array value - extract the specified JSON array from a JSON string.
  • json named value - extract the specified JSON named object from a JSON string.
  • number of characters - Copy the number of characters into a specified numeric Result destination.
  • text - Copy the text from the source field  into a specified Result destination using the specified text type.
  • url extra parts - extract extra parts from a URL string. 
  • url host - extract the host from a URL string.  
  • url password - extract the password from a URL string. 
  • url path - extract the path from a URL string. 
  • url port - extract the port from a URL string.  
  • url scheme - extract the scheme from a URL string. 
  • url user - extract the user from a URL string.  
  • wkt geometry - Convert WKT text representation of geometry  into Manifold or WKB binary geometry.

 

 

Launch the template by choosing a text field and then double-clicking the Copy template.  When the template launches we can specify options.

 

 

Copy : geojson coordinate system

Extract a coordinate system from a GeoJSON string.

 

Using the table below, which contains a GeoJSON field that contains a GeoJSON specification for the object in that record, we extract the coordinate system from within the GeoJSON string and we write it to a new field that we create called Coord System.   We have used the Layers pane to hide the mfd_id field, for a simpler illustration.

 

 

With the focus on the table, in the Transform pane, we choose the GeoJSON field.  We double-click the Copy template to launch it.

 

 

In the Copy template we choose geojson coordinate system as the Use option.  

 

For the Result destination, we choose New Field, and we enter Coord System as the name of the new field.  We leave the default nvarchar type as our choice.  

 

Press Transform.

 

In the first table below we see the result in cases where the JSON string was created using the Compose : geojson transform operation with an EPSG value for the System parameter.  

 

 

The template automatically creates a new nvarchar field called Coord System and populates it with a text string giving the coordinate system embedded within the JSON text for each record in the GeoJSON field.

 

As we can see above, the template correctly extracts the EPSG coordinate system that was specified in the GeoJSON strings.

 

In this next screen we use a different example, showing a case where the Compose : geojson transform was utilized not with an EPSG code to specify the coordinate system, but with a lengthy textual coordinate system specification.

 

 

The template again correctly extracts the coordinate system that was specified, resulting in much longer text values in the Coord System field.

 

Copy : geojson geometry

Convert GeoJSON geometry into a Manifold geom: Given a GeoJSON string extract the geometry specified in the string and write a geom containing that object into the Result destination. 

 

When we receive GIS data in the form of GeoJSON geometry text strings, we can convert those to more efficient Manifold or WKB binary forms.   We can also simultaneously create a new drawing to visualize that binary geometry.

 

Using the table below, which contains a GeoJSON field that contains a GeoJSON specification for the object in that record, we extract the geometry from within the GeoJSON string and we write it to a new, geom Manifold binary geometry field that we create called Geom.   We have used the Layers pane to hide the mfd_id field, for a simpler illustration.

 

 

With the focus on the table, in the Transform pane, we choose the GeoJSON field.  We double-click the Copy template to launch it.

 

 

In the Copy template we choose geojson geometry as the Use option.  

 

For the Result destination, we choose New Field, and we enter Geom as the name of the new field.  We leave the default geom type as our choice.  If we preferred, we could save the geometry as wkb type, to use Well Known Binary (WKB) geometry.

 

For the name of the New Drawing the template will automatically create to visualize the new geometry field, we enter Objects Drawing.

 

Press Transform.

 

 

The template automatically creates a new geom field called Geom and populates it with the geometry from each GeoJSON value in Manifold binary geometry form, using the geom data type.

 

 

Opening the new Objects Drawing table, we see the objects represented by that geometry.  Note that the line in the center of the view is composed entirely of straight line segments.   The usual version of the Objects Drawing that is used for examples in this documentation has a curved segment in that line.  The curved segment has been replaced by a straight line segment due to the limitations of GeoJSON format.

 

 

 GeoJSON text format cannot store curvilinear segments.   Saving curved segments from Manifold or WKB binary geometry  directly into GeoJSON format will simply replace curvilinear segments with single, straight line segments between the start and end coordinates of the former curvilinear segment, to produce shapes that usually are highly unlike the original shapes using curvilinear segments.  

 

To avoid that effect, before converting Manifold or WKB binary geometry into GeoJSON, first run Clean : convert curves to lines with a reasonable number of segments in the Curve limit parameter, to approximate curvilinear segments with many straight line segments.  When converting the result to GeoJSON, the many straight line segments will approximate the shape of the original curvilinear segments.

 

This is the inverse operation of Compose : geojson, which takes Manifold binary geometry and writes a GeoJSON text representation.

 

Copy : gml coordinate system

Extract a coordinate system from a GML string.

 

Using the table below, which contains a GML field that contains a GML specification for the object in that record, we extract the coordinate system from within the GML string and we write it to a new field that we create called Coord System.   We have used the Layers pane to hide the mfd_id field, for a simpler illustration.

 

 

With the focus on the table, in the Transform pane, we choose the GML field.  We double-click the Copy template to launch it.

 

 

In the Copy template we choose gml coordinate system as the Use option.  

 

For the Result destination, we choose New Field, and we enter Coord System as the name of the new field.  We leave the default nvarchar type as our choice.  

 

Press Transform.

 

In the first table below we see the result in cases where the GML string was created using the Compose : gml transform operation with an EPSG value for the System parameter.  

 

 

The template automatically creates a new nvarchar field called Coord System and populates it with a text string giving the coordinate system embedded within the GML text for each record in the GML field.

 

As we can see above, the template correctly extracts the EPSG coordinate system that was specified in the GML strings.

 

In this next screen we use a different example, showing a case where the Compose : gml transform was utilized not with an EPSG code to specify the coordinate system, but with a lengthy textual coordinate system specification.

 

 

The template again correctly extracts the coordinate system that was specified, resulting in much longer text values in the Coord System field.

 

Copy : gml geometry

Convert GML text geometry into Manifold binary geometry: Given a GML string extract the geometry specified in the string and write a geom containing that object into the Result destination. 

 

When we receive GIS data in the form of GML geometry text strings, we can convert those to more efficient Manifold or WKB binary forms.   We can also simultaneously create a new drawing to visualize that binary geometry.

 

Using the table below, which contains a GML field that contains a GML specification for the object in that record, we extract the geometry from within the GML string and we write it to a new, Manifold binary geometry field that we create called Geom.   We have used the Layers pane to hide the mfd_id field, for a simpler illustration.

 

 

With the focus on the table, in the Transform pane, we choose the GML field.  We double-click the Copy template to launch it.

 

 

In the Copy template we choose gml geometry as the Use option.  

 

For the Result destination, we choose New Field, and we enter Geom as the name of the new field.  We leave the default geom type as our choice.  If we preferred, we could save the geometry as wkb type, to use Well Known Binary (WKB) geometry.

 

For the name of the New Drawing the template will automatically create to visualize the new geometry field, we enter Objects Drawing.

 

Press Transform.

 

 

The template automatically creates a new geom field called Geom and populates it with the geometry from each GML value in Manifold binary geometry form, using the geom data type.

 

 

Opening the new Objects Drawing table, we see the objects represented by that geometry.  Note that unlike GeoJSON text geometry, GML text geometry can handle curvilinear segments.

 

This is the inverse operation of Compose : gml, which takes Manifold binary geometry and writes a GML text representation.

 

Copy : json array value

Extract the specified item from a JSON array.  Given a text field that contains a JSON array, and using the specified Value number as an index into the array, extract the item in the array located at that index position and save to the specified Result destination.  Since JSON arrays can contain a variety of data types, including objects such as arrays within arrays, the Result type should match the data type being extracted from the array, with text types used to capture JSON objects such as arrays within arrays.  Manifold scans the array to offer the correct data type by default.

 

We start with a table that has an nvarchar text field called Array, which contains various JSON arrays of text elements.  In this example, all of the arrays have three elements except for two of the arrays, which have four elements.   We have used the Layers pane to hide the mfd_id field, for a simpler illustration.

 

We will extract the second element from each array and place it into a new field called Item.

 

 

With the focus on the table, in the Transform pane, the Array field is chosen by default (since it is the only field - we could have chosen it if there were other fields).   We double-click the Copy template to launch it.

 

 

In the Copy template we choose json array value as the Use option.  We enter 1 for the Value, to choose the second item from each array.  JSON arrays use zero-based counting, so the first item would use a Value of 0, the second a Value of 1 and so on.

 

For the Result destination, we choose New Field, and we enter Item as the name of the new field.  Manifold has helpfully offered  nvarchar text data type as the default for the new field.

 

Press Transform.

 

 

The template automatically creates a new nvarchar field called Item and populates it with text that is the second element of each array in the Array field.

 

What happens if we ask for an array element that does not exist?

 

 

Suppose we had run the transform as set up above, using a Value of 3, meaning the fourth element of each array?    We would get the following result:

 

 

All Item fields for arrays that have only three elements will be NULL, since those arrays do not have a fourth element.   Only the two arrays that have four elements in them will have non-NULL values for Item.

 

Copy : json named value

Extract the specified JSON named object from a JSON string.

 

Given a text field that contains a JSON string, and using the specified text Value number as a name, the operation scans the JSON string to find the specified name and, if it exists, saves that named object into the specified Result destination.   Since JSON named objects can contain a variety of data types, the Result type should match the data type being extracted from the JSON string.  Manifold scans the named objects in the string to offer the correct data type by default.

 

We start with a table that has an nvarchar text field called JSON_Info, which contains various JSON named objects containing text.

 

We will extract text from the named objects called Site from each JSON string and save that text into a new field called Named Item.

 

 

With the focus on the table, in the Transform pane, the JSON_Info field is chosen by default (since it is the only field - we could have chosen it if there were other fields).   We double-click the Copy template to launch it.

 

 

In the Copy template we choose json named value as the Use option.  We enter Site for the Value, to choose named objects using the name Site from each JSON string.

 

For the Result destination, we choose New Field, and we enter Named Item as the name of the new field.  Manifold has helpfully offered  nvarchar text data type as the default for the new field.

 

Press Transform.

 

 

The template automatically creates a new nvarchar field called Named Item and populates it with text that is the content of the object named Site from each JSON string.

 

Consider another example, extracting true or false text values from named objects in the same JSON strings table:

 

 

The NonCoin and Photo named objects provide information about archaeological sites, for example, whether there are photos of the site and whether items other than coins were found at the site.   The JSON string for the first record above is:

 

{ "Site": "Krinkberg", "NonCoin": true, "Photo": false }

 

With the focus on the table, in the Transform pane, the JSON_Info field is chosen by default.   We double-click the Copy template to launch it.

 

 

In the Copy template we choose json named value as the Use option.  We enter NonCoin for the Value, to choose named objects using the name NonCoin from each JSON string.

 

For the Result destination, we choose New Field, and we enter NonCoin as the name of the new field, to remind us where those values came from.  Manifold has helpfully offered  nvarchar text data type as the default for the new field.

 

Press Transform.

 

 

The template adds a new nvarchar field called NonCoin to the table, and populates it with text values taken from the NonCoin named object in the source JSON string.

 

We can extract values for the Photo named object with a quick re-run of the template.

 

 

We enter Photo for the Value.  For the Result destination, we enter Photo as the name of the new field.

 

Press Transform.

 

 

The template adds a new nvarchar field called Photo to the table, and populates it with text values taken from the Photo named object in the source JSON string.

 

See the Example: Construct JSON String using Select and Transform topic for an example creating the table illustrated above.

All Results are Text

JSON strings store everything as text, so when we extract named values that happen to be numbers, or which look like Boolean true/false values, those values will be extracted as text as well.  

 

For example, consider the table below.  The table has a field called CoordSys that contains JSON strings which specify various coordinate systems.   

 

 

The JSON strings are long, the string for the first record being:

 

{ "Name": "Argentina Campo Inchauspe Faja 3", "System": "Transverse Mercator", "CenterLat": -90, "CenterLon": -66, "FalseEasting": 3500000, "Axes": "XYH", "Base": "Campo Inchauspe (Argentina)", "MajorAxis": 6378388, "Eccentricity": 0.08199188997902977, "Transform": "Molodensky-Badekas", "CenterX": -148, "CenterY": 136, "CenterZ": 90, "Unit": "Meter", "UnitScale": 1, "UnitShort": "m" }

 

Some of the named entities have numeric values and some have string values, but all are represented as plain text within the text JSON string.

 

With the focus on the table, in the Transform pane, the CoordSys field is chosen by default.   We double-click the Copy template to launch it.

 

 

In the Copy template we choose json named value as the Use option.  We enter FalseEasting for the Value, to choose the named objects using the name FalseEasting from each JSON string.

 

For the Result destination, we choose New Field, and we enter False Easting as the name of the new field.  Note that the Result type offers a choice of either nvarchar or varchar.

 

Press Transform.

 

 

The template automatically creates a new nvarchar field called False Easting and populates it with text that is the content of the object named FalseEasting from each JSON string.   There is no FalseEasting object in the JSON string for the third record so the result is NULL.

 

We can extract eccentricity or other values as well:

 

 

We re-run the transform using Eccentricity for the Value.

 

For the Result destination, we choose New Field, and we enter Eccentricity as the name of the new field.  

 

Press Transform.

 

 

The template automatically creates a new nvarchar field called Eccentricity and populates it with text that is the content of the object named Eccentricity from each JSON string.    All of the records have Eccentricity objects in their JSON strings, so none of the results are NULL.

 

Copy : number of characters

Given a text field, count the number of characters in the field and save the result into the specified numeric Result destination.

 

We begin with a drawing with a single nvarchar text field, called Name.  We will count the number of characters in each Name and save the result into a new Length field.

 

 

With the focus on the table, in the Transform pane we choose the Name field, and then we double-click the Copy template to launch it.

 

 

In the Copy template we choose number of characters as the Use option.

 

For the Result destination, we choose New Field, and we enter Length as the name of the new field.  We change the Result type from the default float64 type to int32, an integer numeric value, since we know we will not have any fractional numbers of characters in a string.

 

Press Transform.

 

 

The template automatically creates a new int32 field called Length and populates it with the number of characters in each record's Name field.

 

Copy : text

Copy the text from the source field into the specified Result destination using the specified text type.

 

We begin with a drawing with a single nvarchar text field, called Name.  We will copy the contents of the Name field into a new text field.

 

 

With the focus on the table, in the Transform pane we choose the Name field, and then we double-click the Copy template to launch it.

 

 

In the Copy template we choose text as the Use option.

 

For the Result destination, we choose New Field, and we enter Label as the name of the new field.  We leave the default nvarchar as the text data type for the new field.

 

Press Transform.

 

 

The template automatically creates a new nvarchar field called Label and populates it with a copy of the text from the Name field.

 

Copy :

url extra parts

url host

url password

url path

url port

url scheme

url user

url extra parts - extract extra parts from a URL string. 

url host - extract the host from a URL string.  

url password - extract the password from a URL string. 

url path - extract the path from a URL string. 

url port - extract the port from a URL string.  

url scheme - extract the scheme from a URL string. 

url user - extract the user from a URL string.  

 

Given a URL string, the various url operations extract the given part of the URL string and save it to the specified Result destination.  A text data type is used for the Result destination except for url port, which uses a numeric data type.   Each operation extracts the desired portion of a URL string, extracting the scheme, host, port, user, password, path, or extra URL text.

 

We start with a table with an nvarchar field called URL that contains URL strings.  The table also has an nvarchar text field called Password, and an int32 numeric field called Port.

 

 

With the focus on the table, in the Transform pane we choose the URL field, and then we double-click the Copy template to launch it.

 

 

In the Copy template we choose url password as the Use option.

 

For the Result destination, we choose the existing Password field in the pull down menu.  

 

Press Transform.

 

 

The passwords embedded in the URL strings are extracted and copied into the Password field.  We see that George and Alice both should choose more secure passwords.

 

We can use the Copy template to extract the port number.

 

 

In the Copy template we choose url port as the Use option.

 

For the Result destination, we choose the existing Port field in the pull down menu.  

 

 The url port operation requires a numeric field as a Result destination.  If the Port field was not a numeric field, such as an int32 or float64 or some other numeric data type, it would not appear in the pull down menu for the Result box.    If we do not see a field we expect to see in the pull down menu, it could be that the field is the wrong data type (such as a text nvarchar type) to receive the result.

 

Press Transform.

 

 

The port number is extracted from the URLs and copied into the Port field.

 

Next, we will extract the URL scheme and save that into a new field we will create.

 

 

In the Copy template we choose url scheme as the Use option.

 

For the Result destination, we choose New Field, and we enter Scheme as the name of the new field.  We leave the default nvarchar as the text data type for the new field.

 

Press Transform.

 

 

The template automatically creates a new nvarchar field called Scheme and populates it with URL schemes extracted from the URL strings

 

The transforms in this collection use the StringUrlScheme, StringUrlHost, StringUrlPort, StringUrlUser, StringUrlPassword, StringUrlPath, and StringUrlExtra  SQL functions.

 

Copy : wkt geometry

Convert WKT text geometry into Manifold binary geometry: Given a WKT string extract the geometry specified in the string and write a geom containing that object into the Result destination.   WKT is short for "Well Known Text" format.

 

When we receive GIS data in the form of WKT geometry text strings, we can convert those to more efficient Manifold or WKB binary forms.   We can also simultaneously create a new drawing to visualize that binary geometry.

 

Using the table below, which contains a WKT field that contains a WKT specification for the object in that record, we extract the geometry from within the WKT string and we write it to a new, Manifold binary geometry field that we create called Geom.   We have used the Layers pane to hide the mfd_id field, for a simpler illustration.

 

 

With the focus on the table, in the Transform pane, we choose the WKT field.  We double-click the Copy template to launch it.

 

 

In the Copy template we choose wkt geometry as the Use option.  

 

For the Result destination, we choose New Field, and we enter Geom as the name of the new field.  We leave the default geom type as our choice.  If we preferred, we could save the geometry as wkb type, to use Well Known Binary (WKB) geometry.

 

For the name of the New Drawing the template will automatically create to visualize the new geometry field, we enter Objects Drawing.

 

Press Transform.

 

 

The template automatically creates a new geom field called Geom and populates it with the geometry from each WKT value in Manifold binary geometry form, using the geom data type.

 

 

Opening the new Objects Drawing table, we see the objects represented by that geometry.  Note that unlike GeoJSON text geometry, WKT text geometry can handle curvilinear segments.

 

This is the inverse operation of Compose : wkt, which takes Manifold binary geometry and writes a WKT text representation.

 

 

See Also

Transform Pane

 

Transform Reference

 

Transform - Expression

 

Transform - Text

 

Transform - Text: Case

 

Transform - Text: Compose

 

Transform - Text: Concatenate

 

Transform - Text: Encrypt

 

Transform - Text: Expression

 

Transform - Text: Pad

 

Transform - Text: Reduce

 

Transform - Text: Replace

 

Transform - Text: Reverse

 

Transform - Text: Trim