Example: Create a Table and Add a Record

 

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 that is used 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.     

 

 

eg_create_populate_table01_01.png

 

 

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

 

 

eg_create_populate_table01_02.png

 

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

 

 

eg_create_populate_table01_03.png

 

 

That launches the New Table dialog.

 

eg_create_populate_table01_04.png

 

We enter the name desired for this table, GEOMETRY_COLUMNS.

 

eg_create_populate_table01_05.png

 

We then click Create Table.  If we wanted to, we could have used the New Table dialog to also create all the new fields for the table by clicking the Edit Schema... button before clicking Create Table.  We will show how to do that at the very end of this example.   

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

 

 

eg_create_populate_table01_06.png

 

 

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

 

We choose Edit - Schema to launch the Schema dialog.

 

 

eg_create_populate_table01_07.png

 

 

The table has been created with no fields except the default mfd_id field.   We click the <new field> entry to add a new field to the table.

 

 

eg_create_populate_table01_08.png

 

Boxes to allow us to enter a new field appear in the dialog.  

 

We enter F_TABLE_NAME into the Field box as the name for the first field, and from the very long list of available data types we choose varchar as the Type.   We press the Add button.

 

 

eg_create_populate_table01_09.png

 

 

The new field appears in the schema.  The new field boxes persist, ready for us to add any additional new fields if we desire.

 

 

eg_create_populate_table01_10.png

 

 

We enter F_GEOMETRY_COLUMN  into the Field box as the name for the next field, and again we choose varchar as the Type.   We press the Add button.

 

 

eg_create_populate_table01_11.png

 

 

The new field appears in the schema.  The new field boxes persist, ready for us to add the final field we will add.

 

 

eg_create_populate_table01_12.png

 

 

We enter GEOMETRY_TYPE as the name for the last field, and again we choose varchar as the Type.   We press the Add button

 

 

eg_create_populate_table01_13.png

 

 

All the new fields desired have now been added to the schema.   We press the OK button to close the Schema dialog and to update the table.

 

Entering Values for the Fields

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

 

 

eg_create_populate_table01_14.png

 

 

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

 

 

eg_create_populate_table01_15.png

 

 

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

 

 

eg_create_populate_table01_16.png

 

 

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

 

 

eg_create_populate_table01_17.png

 

 

Manifold temporarily displays the content of the cell in blue preview color since we have not yet committed edits to that record.  We double-click into the next cell to open that for editing.

 

 

eg_create_populate_table01_18.png

 

 

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.

 

 

eg_create_populate_table01_19.png

 

 

Manifold also displays the content of that cell in blue preview color.    To edit the final cell we could double-click into it or we can show another technique many 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.

 

 

eg_create_populate_table01_20.png

 

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 is faster and easier for those with good keyboarding skills.

 

 

eg_create_populate_table01_21.png

 

 

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

 

 

eg_create_populate_table01_22.png

 

 

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.

 

 

eg_create_populate_table01_23.png

 

 

A Ctrl-Enter commits the edits and creates the first row in the table.  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.

 

 

eg_create_populate_table01_03.png

 

That opens the New Table dialog.

 

eg_create_populate_table01_05a.png

 

As before, we enter GEOMETRY_COLUMNS as the name of the new table.   However, now we Edit Schema... to launch the standard Schema dialog.

 

eg_create_populate_table01_24.png

 

In the Schema dialog we click on the <new field> entry.   That causes controls to open up at the bottom of the dialog which allow us to add a new field.

 

eg_create_populate_table01_25.png

 

We enter F_TABLE_NAME as the name of the new field, we choose varchar as the data type and then we press the Add button.

 

eg_create_populate_table01_26.png

 

The new F_TABLE_NAME field is created.   The controls for adding a new field stay open at the bottom of the dialog so we can create another new field.   We enter F_GEOMETRY_COLUMN as the name of the new field, we again choose varchar as the type and we press the Add button.

 

eg_create_populate_table01_27.png

 

The new F_GEOMETRY_COLUMN field is created.   To create the last of our three fields we enter GEOMETRY_TYPE as the name of the new field, we choose varchar as the type and we press the Add button.

 

eg_create_populate_table01_28.png

 

The final GEOMETRY_TYPE field is added.   We can now press OK.

 

eg_create_populate_table01_05b.png

 

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

 

 

eg_create_populate_table01_29.png

 

 

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.

 

 

eg_create_populate_table01_30.png

 

 

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 to 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 panel.

 

Example: Add a Computed Field to a Table - In this example we add a field to a table.  We first set the values for a field dynamically with a computed field using the Add Computed Field option in the New Field dialog.   We then illustrate what happens when we fill a field statically with values using the Add Field option.  Last, we show what the Add Component choice does in the New Field dialog.

 

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.