Example: Create a Table and Add a Record

In this example, we create a new table, create some fields in that table and then enter values for those fields for the first record.   Since this example is intended as a basic introduction we illustrate each step in complete and total detail.  Doing this example in real life is very fast.   The table we create is a table required in real life, as seen in the Example: Connect to Manifold from QGIS topic.   

 

The QGIS/OGR ensemble of software requires a special table within an ODBC source to be able to connect to that ODBC data source.  To enable QGIS/OGR to connect to a Manifold project via ODBC we must create that special table in the project.   In this example we will create that table in this example, and we will add a field to it to satisfy the following conditions required by QGIS/OGR:

 

 

Our task in this example is to create the table, create the fields and then put the information that OGR requires into those fields.    Doing this provides a real world example of how to create a table, adding fields to that table and then putting information into fields.

 

We begin with the Manifold project where the new, GEOMETRY_COLUMNS table is to be created.   The GEOMETRY_COLUMNS table will tell OGR how to understand what is in the project.     

 

 

 

 

The project has a drawing, called regions, and the table for the drawing, called regions_table, contains the fields seen below.

 

 

 

 

The new table that we create will contain fields that will give the name of the regions_table, so OGR will know that is the table to use, and that give the name of the field within regions_table that contains geometry information in a form that OGR understands, the GeomWKB field.    

 

None of that matters to the routine nuts and bolts of creating a table within Manifold but it helps to know why we are doing this as a real-life task and not just as an artificial example picked out of thin air.

Create a Table

To create a table we choose File - Create - New Table.    We could also have right-clicked into the Project pane and chosen Create - New Table from the context menu.

 

 

 

 

That launches the New Table dialog.

 

 

We enter the name desired for this table, GEOMETRY_COLUMNS.

 

 

We then click Create Table.  

 

Some people like to create a table first and then add fields to it.  Other people prefer to click the Edit Schema... button in the New Table dialog and create all of the fields at the same time they create the table.  

 

In this example we will first create the table and then add fields.  At the very end of this example we will show how to create a table and fields at the same time.

Add Three New Fields to the Table

For now, we will continue in a way that shows how to add fields to a table from within an open table window.  

 

The new GEOMETRY_COLUMNS table appears in the Project pane.   We double-click it to open it in a window.

 

 

 

 

The table has been created with no fields except the default mfd_id field.    We choose Edit - Schema to launch the Schema dialog.  

 

 

We click the Add button.

 

 

In the drop down menu we choose Field.

 

 

In the Field dialog we enter F_TABLE_NAME as the Name for the new field, and from the very long list of available data types we choose varchar as the Type.   

 

The initial default data type in the Type field is int32, for 32-bit integer numbers.   The Field dialog remembers the last used Type and presents that as the default until a different Type has been picked.  That makes it easy to repeatedly add new fields of the same data type.   In this case, our choice of varchar as the Type will be remembered for the next field we may add.

 

Press the OK button.

 

The new field appears in the schema, using provisional, bluish background color to indicate it has not yet been saved as a change to the table's schema.

 

To add another field, we click the Add button again.  

 

 

In the drop down menu we again choose Field.

 

 

In the Field dialog we enter F_GEOMETRY_COLUMN as the Name for the new field.  Our prior choice of varchar as the Type has been remembered from the prior field we created.  

 

Press the OK button.

 

The new field appears in the schema, using provisional, bluish background color to indicate it has not yet been saved as a change to the table's schema.

 

 

To add a third new field, we click the Add button again.  

 

 

In the drop down menu we choose Field.

 

 

In the Field dialog we enter GEOMETRY_TYPE as the Name for the new field.  As before, our prior choice of varchar as the Type has been remembered.  

 

Press the OK button.

 

The new field appears in the schema, using provisional, bluish background color to indicate it has not yet been saved as a change to the table's schema.

 

 

We review our work.  At this point we can make any adjustments, or we can Close the dialog without saving changes to the table's schema.   If we like what we see, we press the Save Changes button to alter the table's schema by adding the three new fields and to close the dialog.

Entering Values for the Fields

When we close the Schema dialog the table updates to display the new fields.    

 

 

 

 

We double-click into the cell for the F_TABLE_NAME field for what will be the first record.

 

 

 

 

That opens the cell for editing as discussed in topics like Editing Tables and the Example: Editing Records in a Table topic.

 

 

 

 

We enter the value required by OGR for the first field, regions_table, and then we press Enter to close editing of that cell.

 

 

 

 

Manifold temporarily displays the content of the cell in provisional, blue preview color since we have not yet committed edits to that record.    The Info pane automatically pops open as well, to show the pending record.   We can continue editing either in the table window or in the Info pane.   We will continue this example by editing in the table window.

 

We double-click into the next cell to open that for editing.

 

 

 

 

In the F_GEOMETRY_COLUMN cell we enter the value GeomWKB, which tells OGR what field to use for geometry.   We press Enter to close editing of the cell.

 

 

 

 

Manifold also displays the content of that cell in provisional, blue preview color, both in the table window and also in the Info pane.    To edit the final cell we could double-click into it or we can show another technique some users prefer:  we press the right-arrow key on the keyboard to move the current cell cursor, that is, the dotted box outlining the current cell, one cell to the right.

 

 

 

 

We then press Enter to open the current cell for editing.   

 

Why do that instead of double-clicking into the cell with the mouse?   When we are entering a series of edits from one cell to another it is quicker to not move our hands from the keyboard than it is to reach for the mouse to make a double-click.   Moving from one cell to the next with a right-arrow, pressing Enter, making the desired edits and then pressing Enter again can be faster and easier for those with good keyboarding skills.

 

 

 

 

We enter the word GEOMETRY as the value for the cell and press Enter.

 

 

 

 

Manifold now shows all three cells in blue preview color.    If we wanted to, we could abandon the edits at this point.   To commit the edits and to create the first row in the table we press Ctrl-Enter, or, if we prefer, we can press the Add Record button in the Info pane.

 

 

 

 

A Ctrl-Enter commits the edits and creates the first row in the table.  The Info pane no longer has a context record that has been picked, so it switches to the Component tab to show general information about the table.  We can choose another pane, such as the Project pane, by clicking the pane's tab.

 

The GEOMETRY_COLUMNS table is a guide for OGR: only one row is required in the table so we are done.

 

Our task is now accomplished: the table has been created, three fields with the required names have been created and their values have been populated with the values required by OGR.   

Creating Fields at the Same Time as the Table

Earlier in this example we mentioned that we could use the New Table dialog to create all of the fields required in the table at the same time we created the table.  Here is how.

 

Starting with the original project, before the new GEOMETRY_COLUMNS table has been created, we begin by clicking File - Create - New Table.

 

 

 

That opens the New Table dialog.

 

 

As before, we enter GEOMETRY_COLUMNS as the name of the new table.   However, now we Edit Schema... to launch the Schema dialog.    To add three new fields to the schema, we repeat exactly the same workflow in the Schema dialog we used earlier in this topic.

 

 

In the Schema dialog we click the Add button.

 

 

In the drop down menu we choose Field.

 

 

In the Field dialog we enter F_TABLE_NAME as the Name for the new field, and from the very long list of available data types we choose varchar as the Type.   

 

The initial default data type in the Type field is int32, for 32-bit integer numbers.   The Field dialog remembers the last used Type and presents that as the default until a different Type has been picked.  That makes it easy to repeatedly add new fields of the same data type.   In this case, our choice of varchar as the Type will be remembered for the next field we may add.

 

Press the OK button.

 

The new field appears in the schema, using provisional, bluish background color to indicate it has not yet been saved as a change to the table's schema.

 

To add another field, we click the Add button again.  

 

 

In the drop down menu we again choose Field.

 

 

In the Field dialog we enter F_GEOMETRY_COLUMN as the Name for the new field.  Our prior choice of varchar as the Type has been remembered from the prior field we created.  

 

Press the OK button.

 

The new field appears in the schema, using provisional, bluish background color to indicate it has not yet been saved as a change to the table's schema.

 

 

To add a third new field, we click the Add button again.  

 

 

In the drop down menu we choose Field.

 

 

In the Field dialog we enter GEOMETRY_TYPE as the Name for the new field.  As before, our prior choice of varchar as the Type has been remembered.  

 

Press the OK button.

 

The new field appears in the schema, using provisional, bluish background color to indicate it has not yet been saved as a change to the table's schema.

 

If we like what we see, we press the Save Changes button to alter the table's schema by adding the three new fields and to close the dialog.

 

 

Back in the New Table dialog we press Create Table to create a new table with the specified schema.

 

 

 

 

In the project pane we see that a new GEOMETRY_COLUMNS table has been created.    We can double-click it to open it in a window.

 

 

 

 

The new table has been created with three new fields as we commanded.    The table is as yet blank with no rows added to it.   To enter values for the new fields to create the first row we follow the same procedure as given earlier in this example.

 

Notes

Two procedures to create fields: Why choose one over the other? - That is entirely a matter of personal taste and the circumstances of workflow.   Some people like to create fields at the same time as the table, so they can see the whole schema as they are creating the table.  Others prefer to create a table and then add fields.   In both cases, we launch Edit - Schema to add fields.

See Also

User Interface Basics

 

Tables

 

Data Types

 

Editing Tables

 

Edit - Schema

 

Example: Editing Records in a Table - How to edit the contents of an existing record using mouse and keyboard.

 

Example: Adding Records to a Table - How to add a new record to a table using mouse and keyboard.

 

Example: Add a Field to a Table and Fill It - we add a field to a table and simultaneously for all records fill the field with a specified value.   We do that in two examples, first a very simple one and second, a more elaborate example that also shows restriction to a selection as well as use of the Transform pane.

 

Example: Add a Computed Field to a Table - In this example we add a computed field to a table, illustrating how the computed field automatically changes when changes are made in the fields it uses for computation.   We also show how computed fields can use geometry, automatically updating centroids when areas are changed.  Last, we show how geometry can be created using computed fields, to create effective radius circles for antennas based on the power of the antenna.

 

Example: Create a Table with a Constraint - Create a simple table that includes a simple constraint upon one of its fields.  

 

Adding an Index to a Table - A basic topic on adding an index using the built-in capabilities of the mfd_id field.

 

Example: Add a Spatial Index to a Table - A typical use of an index is to provide a spatial index on a geom field in a table, so the geom data can be visualized in a drawing. This topic provides the step by step procedure for adding a spatial index.

 

Example: Add a UUID-based Index to a Table - Create a new computed field that is filled with UUID values on creation and then create an index on that field.   This technique creates an indexed field that has guaranteed unique values for all records and thus the indexed field and record values may be used in other projects.

 

Example: Connect to Manifold from QGIS - Step by step procedure to connect from QGIS 2.8.9 to a Manifold .map file using Manifold's ODBC driver.