Example: Create a Table from Locations

In this example, we create a table that contains, as records, all of the Locations components in a project.  Each record contains the Name, Latitude, Longitude, and Scale of a location.  We use simple, point-and-click operations using the Select and Transform panes.   The objective of this example is to show how a variety of Manifold features can be used in combination for exceptionally flexible table editing.

 

The table we create in this topic will be used in a programming example, shown in the Example: VBScript to Create Locations from a Table topic.

 

To fit into this documentation, illustrations show a small Manifold desktop, with only a few panes, docked to the right side.  In real life we use a much larger Manifold desktop, and more panes would be turned on, with panes docked to the left or to the right, or undocked, as we prefer.   Right-click a pane's tab to change where it is docked.  Manifold will remember that new arrangement for our next session.

 

 

 

 

We begin with the server_sights.mxb example project, which we have downloaded from the Examples page on the Manifold web site and have opened in Manifold as seen above.   The project provides a list of Locations that are often cited on Internet as interesting places to visit on Google Maps.  In the illustration above, we have opened the Map component in the project, which contains Google and Bing satellite layers.

 

 

 

 

With the focus on the map we right-click on the Grand Prismatic Spring location and choose View in Active Window.   Immediately, the map window pans and zooms to show the famous spring in Yellowstone National Park, in the US.

 

 

We double-click on the same Location to open it in its own window, seen undocked above.   A Location is simply a string of text in JSON form that gives the center of the view and the scale to use.

 

 

 

 

The Text property of the Location component conveniently is listed in the mfd_meta system table.

 

 

We can undock the mfd_meta table to see it in a larger illustration, as shown above.   If we can extract the latitude, longitude, and scale from the JSON text string, we have a head start on creating the table we want.   The name of the location is already there in a Name field.   Manifold exposes virtually everything in a table that is easy to exploit.   

 

In this case, we will start with a copy of the mfd_meta table to create the table we want.     

 

 

 

 

We highlight the mfd_meta table in the Project pane and then Copy and Paste to make a copy of the table called mfd_meta 2 by default.

 

 

 

 

We change the name to locations table.   We will edit this table to create the table we want.

Eliminate all Records Except Text Property Records

Our first task is to get rid of all records that are not text property records for Location components.

 

 

We show the locations table above in undocked form.

 

With the focus on the locations table window, in the Select pane we choose the Property field and then we double-click the Search template to launch it.

 

 

In the Search template we choose text as the Use option.    We choose not equal (<>) as the Condition option.   

 

In the Value box we enter Text as the word we are searching for.  Other parameters we leave at their default values.

 

Press Preview to see a preview.  We do not have to do a preview before making a selection, but doing a quick preview is often a great way to avoid errors, to see what will happen before we do it.

 

 

The records that will be selected by the template appear in blue preview color.  A blue caption is added at the top right of the window with the name of the template that is being previewed.

 

We can see the selection will be exactly what we want, all of the records except  those that have Text in the Property field.

 

Press Select.    

 

 

The template selects all records that do not have Text as the Property.  

 

We now choose Edit - Delete to delete those records.

 

 

Press OK to confirm the deletion.  

 

 

Our table now only contains Text property records for Locations.

 

Suppose our project contained components, such as Comments, which also have text properties?   In that case, we could have used  contains in the Condition box of the Select pane to select only those records with a Value containing "Center" or "Scale" or some other text found only in Locations.  

Edit the Table's Schema to Delete Fields

We will now edit the table's schema to remove fields and indexes we do not need.   

 

We launch Edit - Schema.

 

 

We do not need the property indexes or the Property field.   We Ctrl-click on each to select it, and then we press the Delete button to delete all of the selected items.

 

 

Press Save Changes to update the schema and to exit the Schema dialog.

 

 

That simplifies the table.   We have also used the Layers pane to hide the mfd_id field, and to resize column widths.

Extract Scale from JSON String

The Value field contains Location data using JSON notation, for example, for the first record:

 

{ "Center": [ -110.82684646430927, 32.152566401820785 ], "Scale": 11817.870895842912 }

 

All of the values within the field are text, not numbers.   We will extract different pieces of text from the JSON string into temporary text fields, and then we will create numeric fields from those text fields.  Ultimately, for each record we want numeric fields that give scale, latitude, and longitude.

 

We begin by extracting the Scale numbers as a new text field called Scale_txt, and then we will create a numeric field from those called Scale.

 

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

 

 

In the Copy template we choose json named value as the Use option.  We enter Scale for the Value, to choose named objects using the name Scale from each JSON string: those are the strings in the "Scale" parts of the JSON string.

 

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

 

To see a preview, press Preview.

 

 

A preview column appears in blue preview color at the right side of the window, with the column caption giving the name of the template that is previewed.  We can drag the preview column closer to the Value column to make comparisons easier.  

 

The preview column shows how the template will extract the Scale JSON named value and put it into a new column.   That is exactly what we want.   We do not have to do a preview before applying a transform, but it is often a wise move to take a moment to check what we are about to do before we do it.

 

Press Transform to apply the template.

 

 

The template automatically creates a new nvarchar field called Scale_txt and populates it with text that is the content of the object named Scale from each JSON string.    We can see the new field is a text nvarchar field by hovering the mouse cursor over the column header, to see the tooltip that gives the data type of the field.    We have again used the Layers pane to resize column widths to provide some extra room in the illustration for the fields we will add.

 

JSON is just text, so what look like numbers in the Scale_txt field are just text strings.   We will use the Expression transform to create a new numeric field called Scale and then CAST (transform data types) the text strings in the Scale_txt field into numbers in the Scale field.

 

 It is an extra step to use the Expression template to convert a field that is text into a field that is a number.   Upcoming Manifold builds will add a Convert type command to the context menu for column heads in tables that will do the conversion "in place".    Until then, we use the Expression template as follows:

 

 The Transform pane is still set to the Copy template.   With the focus on the locations table window, in the Transform pane we press the up button to get back to the main level of the Transform pane, so we can pick a different template.   We choose the mfd_id field, and then we double-click the Expression template to launch it.

 

 

We launched the Expression template using the mfd_id field because the Expression template is designed for expressions that evaluate to the data type of whatever field was chosen as the target when the template was launched.   We want to create a new numeric field, and the only numeric field we have in the table so far is the mfd_id field, so we will use that as a placeholder to launch the Expression template. We do not need to actually use the mfd_id field, since the expression we write can be anything at all, so long as it evaluates to a numeric data type.

 

For the Result destination, we choose New Field and then enter Scale as the name of the new field.   We do that now, even before writing the expression, so that in case we forget to do it later we will not accidentally overwrite the mfd_id field.   We leave the default choice of float64 as the Result type.

 

We press the Edit Expression button to launch the expression builder dialog.

 

 

We enter the expression:

 

CAST([Scale_txt] AS int32)

 

The expression uses the SQL CAST operator to convert the text strings in the Scale_txt field into int32 numbers.   We cast into integers since there is no need for fractional parts in scales in locations.

 

Press OK.

 

 

Back in the Transform pane, we check the expression and we make sure the Result destination is a new field, the Scale field.  We change the Result type to int32.  

 

Press Preview to see a preview.

 

 

In the illustration above, we have dragged the blue preview column to the left to be next to the Scale_txt column, to make comparison easier.    We can see how the text values in the Scale_txt values have been converted into integer values.  In this case, they have been simply truncated, which is more than adequate accuracy for scales as used in locations.  If we wanted more sophisticated rounding, to round up and down, we could modify the expression used slightly to round the result as desired.

 

Press Transform.

 

 

The template automatically creates a new int32 field called Scale and populates it with numbers that are integer numeric equivalents, created by CAST, of the text strings in the Scale_txt field.  We can see the new field is a numeric int32 field by hovering the mouse cursor over the column header, to see the tooltip that gives the data type of the field.

 

We no longer need the Scale_txt field.  We delete it from the table by launching the Schema dialog and deleting it.

Extract Latitudes and Longitudes from JSON String

We next will extract latitudes and longitudes from the Value JSON string, using a procedure similar to that used to extract the Scale numbers.     First we will extract the Center values as a text field, and then we will extract latitude and longitude text strings.   Finally, we will convert those text strings into the Latitude and Longitude numeric fields we want.   As much as possible, we will use the Transform pane's ability to do a transform and to create a new field for the result in the same step.

 

With the focus on the locations table window, in the Transform pane we choose the Value field.  The Transform pane remembers we have recently used the Copy : json named value transform with text fields, so it lists that at the top of the table as a quick pick.  We double-click that template to launch it.

 

 

The Copy template launches with json named value already chosen as the Use option.  We enter Center for the Value, meaning we want to extract the JSON named value named "Center."

 

For the Result destination we enter Center_txt as the name of the new nvarchar text field.

 

Press Preview to see a preview.

 

 

In the illustration above, we have dragged the blue preview column to the left, and then we have dragged the right vertical border of the column to the right to widen it.  That makes it easy to see what the template will create.  

 

Press Transform.

 

 

The template creates a new nvarchar text field called Center_txt and populates it with the text contents of the "Center" JSON object.   In the illustration above we have adjusted column widths to show the full result.

 

We next will extract latitude and longitude values from the Center_txt field.    Before we do that we will clean up the display a bit.   We will use the Layers pane to hide the Name and Value fields, and we will narrow the Center_txt column to provide more space in the illustration for the new fields we will create.

 

 

The Center_txt field contains text extracted from a JSON named object.  The text that was extracted is actually a JSON array, so we can use the json array value option to extract the first, longitude part of the array and then the second, latitude part of the array (the array is in longitude, latitude order, as we can tell by the numbers, since 110 is too big a number to be a latitude).

 

 

The Transform pane is persistent, so it is still loaded with the Copy template aimed at the locations table.   In the pull down menu for Field we choose the Center_txt field.

 

In the pull down menu for Use we choose json array value.  For Value we leave 0 in place, since we want the first element of the array, which in zero-based JSON counting is element number zero.  

 

For the Result destination we enter Longitude_txt as the name of the new, nvarchar field to be created.

 

Press Preview.

 

 

The preview shows how the template will extract the first item in the JSON array to create a new column.  We can see how the values extracted look like longitudes, and not latitudes, since they are outside the range +/- 90.   This is a great example of how using a preview can prevent errors like confusing latitudes with longitudes.

 

Press Transform.

 

 

A new nvarchar text field called Longitude_txt appears, populated with the text values that were in the first part of the JSON text arrays in the Center_txt field.

 

 

The Transform pane is still loaded up with the Field and Use options we want, so all we need do is change the Value to 1 (to extract the second array value) and to change the name of the field we want to create to Latitude_txt.

 

Press Preview.

 

 

This time, the preview shows values that are clearly latitudes, as they fall within the range +/- 90.  

 

Press Transform.

 

 

A new nvarchar text field called Latitude_txt appears, populated with the text values that were in the second part of the JSON text arrays in the Center_txt field.

 

We have extracted the first (longitude) and second (latitude) parts of the Center_txt field.  We no longer need it and can delete it from the table using the Schema dialog.

Convert Latitude and Longitude Text to Numeric Fields

All that remains is to create numeric field versions of the Latitude_txt and Longitude_txt text fields.  We use the same procedure and a similar expression as used to create a numeric Scale field from the text Scale_txt field.

 

 It is an extra step to use the Expression template to convert a field that is text into a field that is a number.   Upcoming Manifold builds will add a Convert type command to the context menu for column heads in tables that will do the conversion "in place".    Until then, we use the Expression template as follows:

 

 The Transform pane is still set to the Copy template.   With the focus on the locations table window, in the Transform pane we press the up button to get back to the main level of the Transform pane, so we can pick a different template.   We choose the mfd_id field, and then we double-click the Expression template to launch it.   

 

The Transform pane remembers the prior use of the Expression template, so we can either double-click it in the recently-used position at the top of the list, or in the usual position in the list of templates.

 

 

We launched the Expression template using the mfd_id field because the Expression template is designed for expressions that evaluate to the data type of whatever field was chosen as the target when the template was launched.   We want to create a new numeric field, and the only numeric fields we have in the table are the mfd_id and Scale fields.  We could use either as a placeholder to launch the Expression template. We will use the mfd_id field again.  We do not need to actually use the mfd_id field, since the expression we write can be anything at all, so long as it evaluates to a numeric data type.

 

For the Result destination, we choose New Field and then enter Longitude as the name of the new field.  We do that now, even before writing the expression, so that in case we forget to do it later we will not accidentally overwrite the mfd_id field.  We leave the default choice of float64 as the Result type.

 

We press the Edit Expression button to launch the expression builder dialog.

 

 

We enter the expression:

 

CAST([Longitude_txt] AS float64)

 

The expression uses the SQL CAST operator to convert the text strings in the Longitude_txt field into float64 numbers.

 

Press OK.

 

 

Back in the Transform pane, we check the expression and we make sure the Result destination is a new field, the Longitude field.  

 

Press Preview to see a preview.  That is not necessary, but is often a wise thing to do when altering tables using expressions, just in case of typos.

 

 

In the illustration above we have dragged the blue preview column next to the Longitude_txt field, to make comparison easier.  The blue preview column is a virtual column that is drawn above all other columns in the table, so when we drag it next to the Longitude_txt column it covers up the Latitude_txt column below.

 

We can see how the text values in the Longitude_txt field will be converted into equivalent float64 numeric values.

 

Press Transform.

 

 

The template automatically creates a new float64 field called Longitude and populates it with numbers that are numeric equivalents, created by CAST, of the text strings in the Longitude_txt field.  We can see the new field is a numeric float64 field by hovering the mouse cursor over the column header, to see the tooltip that gives the data type of the field.

 

We next create a numeric field for latitudes.

 

 

The Transform pane is still loaded up with the prior expression.  We press the Edit Expression button and alter the expression to read:

 

CAST([Latitude_txt] AS float64)

 

We press OK to return to the Transform pane, where we change the Result field name to Latitude.

 

Press Preview.  It may be tempting to simply press Transform and to skip a preview.  We can certainly do that, but given the possibilities of typos, or missing a needed change in one of the parameters, a quick preview is rarely regretted.

 

 

We drag the preview column next to the Latitude_txt column, which is easy to do as it snaps into place.  Clearly, we have set up the template correctly.

 

Press Transform.

 

 

The template automatically creates a new float64 field called Latitude and populates it with numbers that are numeric equivalents, created by CAST, of the text strings in the Latitude_txt field.  We can see the new field is a numeric float64 field by hovering the mouse cursor over the column header, to see the tooltip that gives the data type of the field.

 

At this point, we are done.   We can use the Edit - Schema dialog to delete any temporary fields, like the Longitude_txt and Latitude_txt fields.   We can also use the Layers pane to turn back on the fields we have hidden.

 

 

The table we have created contains a record corresponding to each Location component in the project.   It has Name, Scale, Latitude, and Longitude fields with values for each record extracted from the JSON text string in the mfd_meta table.   If we like, we can use the table to create points, that we can use to show on a map all places for which we have Locations.

 

The table created above is used in the Example: VBScript to Create Locations from a Table topic to show how to use a script to create Locations from a table.

Notes

Why not use a single SQL query? - We could.  The process shown above is a step-by-step process using interactive methods, such as the point-and-click Select and Transform panes.   It could have been accomplished in a single query using appropriately masterful SQL.     We could learn to do that by pressing the Edit Query button in the Transform pane in the steps above, to see what SQL Manifold would write to accomplish the same step.  We could then use those insights to write a combined query which all at once created the final table.  

 

Writing such a query is a great way to automate the job if this is something we must do on a regular basis, starting with a project that has locations in them and then creating a table for all the locations.

See Also

Getting Started

 

User Interface

 

Tables

 

Maps

 

Locations

 

Selection

 

Project Pane

 

Select Pane

 

Transform Pane

 

Example: Locations - Save Locations and use saved Locations to quickly navigate to desired views in windows.

 

Example: Create Many Locations from a Drawing - Given a drawing of points, we quickly create a folder with Locations for all of the points, allowing us to quickly pan and zoom to a local view around each point.

 

Example: VBScript to Create Locations from a Table - Use VBScript to take a table where each record has a name, scale, latitude and longitude and for each record create a Location component in the project.