﻿ Example: Create a Geocoded Table from a Drawing

# Example: Create a Geocoded Table from a Drawing

A geocoded table has records with a latitude and longitude for each record.   This example starts with a table for a drawing of points where the geom field in the table contains geometry information for each point.   We extract the Y and X locations for each point  from the geom field to create latitude and longitude fields in the table for each record.   What we accomplish in this topic is basically the inverse of the example in the partner topics, Example: Create a Drawing from a Geocoded Table and Example: Create a Drawing from Read-Only Geocoded CSV File.

We open a project that contains a drawing showing cities with airports.   The points have been formatted using Style so each country has a different color.

Geocoded tables provide latitude and longitude fields in degrees.   The simplified procedure in this example depends upon the drawing being in Latitude / Longitude projection.   To verify that it is we open the Component pane to see the coordinate system used by the drawing.   If the drawing is not in Latitude / Longitude, it takes but a second to change the coordinate system to Latitude / Longitude projection.

The Component pane confirms the drawing is in Latitude / Longitude projection.

## Two Ways to Create Latitude and Longitude Fields from Geometry

There are two ways to create a geocoded table from a drawing, depending on how we would like to use that table:

• Create Computed Fields from the Geometry Field - The first approach is to create Latitude and Longitude fields as computed fields in the table, where the values for latitude and longitude are computed using an expression from whatever is in the geometry field.   This has the advantage that any change in the drawing, such as moving points or adding or deleting points, will result in an automatic update to the latitude and longitude field values.  It has the disadvantage that the fields will be read-only, since they are computed from the geometry.

• Use Transform to Copy from the Geometry Field - An alternative approach is to create empty Latitude and Longitude fields and to then use the Transform pane to extract latitude and longitude values from the geometry and to copy them into the Latitude and Longitude fields.   The Latitude and Longitude fields in this case are not automatically computed on-the-fly based on any changes in the geometry field.  They are just numeric fields like any other, and can be edited independently.   However, if we add a new point to the drawing the new record will have NULLs in the Latitude and Longitude fields and if we move a point in the drawing the values in the Latitude and Longitude fields for that record will not automatically be updated.    The table, however, will be like those typically found in classic GIS packages that do not have computed fields.

In this topic we illustrate both approaches.

## Create Computed Fields from the Geometry Field

We open the drawing's table.   The table stores the point geometry in a geom field called Geom.   In addition it has fields for the mfd_id used as an index, the name of the city, the name of the country and the three letter airport code.

This is a typical table for a Manifold drawing, keeping the location information for points in the geom field.   This is fast and very efficient for many points or complex objects but other software may want to see latitude and longitude fields in the table.   We will create those fields as computed fields.

With the focus on the table, we choose Edit - Schema to launch the Schema dialog.

Press the Add command button and choose Field in the drop down menu.

In the Field dialog, enter Latitude as the Field name and choose float64 as the data type for the field.   Press Edit Expression to launch the expression builder to create the expression the computed field will use.

In the Expression dialog we enter the expression

VectorValue(GeomCoordXY([Geom],0),1)

This expression extracts the Y coordinate (latitude is the Y axis, that is, up and down) from the Geom field value.    The expression gets the (X,Y) vector value of the first coordinate in the object in Geom, and then uses the VectorValue function to extract from that the Y value.  The expression will be automatically computed for each Geom in the drawing to produce the Y value to fill the new Latitude field for that object.

The logic behind this expression is discussed in more detail in the Compose Circle template entry in the Transform Templates - Drawings topic.

We press the OK button.

Back in the Field dialog, we check our work and press OK.

A new Longitude field appears in the schema, shown in provisional, bluish color since it has not yet been added to the table. It is a computed field, with the expression used to compute the field shown.  To see the entire expression, we can hover the mouse over the expression and the full expression will appear in a tool tip.

To add the next field we once again press the Add command button and choose Field in the drop down menu.

In the Field dialog, enter Longitude as the Field name and choose float64 as the data type for the field.   Press Edit Expression to launch the expression builder.

In the Expression dialog we enter the expression

VectorValue(GeomCoordXY([Geom],0),0)

This expression extracts the X coordinate (longitude is the X axis, that is, left and right) from the Geom field value.    The expression gets the (X,Y) vector value of the first coordinate in the object in Geom, and then uses the VectorValue function to extract from that the X value.  The expression will be automatically computed for each Geom in the drawing to produce the X value to fill the new Longitude field for that object.

See the Compose Circle template entry in the Transform Templates - Drawings topic for more detail on the expression.

We press OK.

In the Field dialog we check our work, and press OK.

A new Longitude field appears in the schema, shown in provisional, bluish color since it has not yet been added to the table. It is a computed field, with the expression used to compute the field shown.  To see the entire expression, we can hover the mouse over the expression and the full expression will appear in a tool tip.

We press Save Changes to apply our changes to the table and to close the Schema dialog.

Done!  We have added Latitude and Longitude fields to the table that provide the latitude and longitude locations of each point as floating point numbers.

These are read-only fields, shown with a gray background, since they are computed fields that are automatically calculated from the Geom field's geometry.   If we add new points to the drawing, a new record will be added to the table with the Latitude and Longitude fields automatically computed.  If we move a point, thus changing the geometry stored within the Geom field for that point, the Latitude and Longitude fields automatically will be updated.

Other applications can now use the table, for example, as waypoints for GPS devices or in other systems that can work with geocoded tables but not with geom fields.

## Use Transform to Copy from the Geometry Field

Another way to create a geocoded table from a drawing's geometry is to first create Latitude and Longitude fields as empty fields and to then use the Transform pane's Copy template to fill them with an expression.

With the focus on the table, we choose Edit - Schema to launch the Schema dialog.

Press the Add command button and choose Field in the drop down menu.

In the Field dialog, we enter Latitude as the Field name and choose float64 as the data type for the field.  This will not be a computed field, so we do not enter anything into the Expression box.   We press the OK button.

The new Latitude field appears in the Schema dialog in provisional, bluish color.

To add a new Longitude field, we again press the Add command button and choose Field in the drop down menu.

In the Field dialog we enter Longitude as the Field name and choose float64 as the data type for the field.  This, too, will not be a computed field, so we do not enter anything into the Expression box.   We press the OK button.

The new Longitude field appears in the schema, in provisional, bluish color.

We press Save Changes to commit the changes to the schema, adding Latitude and Longitude fields to the table.

The two fields appear as ordinary float64 fields, full of NULLs since they are as yet unpopulated.   We will now fill them with values taken from the Geom field.

We use the Transform pane.

We choose the Latitude field as the target, and Copy as the template.   The Value box opens set to the same, target field by default.  To change the Value box, we click on the field icon in the box.

In the drop down menu we choose Expression.

The Value box switches to showing a expression icon.  We can now enter an expression into the box.  We enter the expression

VectorValue(GeomCoordXY([Geom],0),1)

This expression extracts the Y coordinate (latitude is the Y axis, that is, up and down) from the Geom field value.    The expression gets the (X,Y) vector value of the first coordinate in the object in Geom, and then uses the VectorValue function to extract from that the Y value.

The Copy template will use the given expression to populate the Latitude field for each record.

As soon as we enter the expression, a preview in blue preview color appears in the table.

To apply the template, in the Transform pane we press the Update Field button.

The table now shows the field contents in white background, indicating the template has been applied to populate the field with data.

We will now populate the Longitude field.

In the Transform pane, we now choose the Longitude field as the target, and again we choose Copy as the template.   We switch the Value box to using an expression, and then we enter the expression

VectorValue(GeomCoordXY([Geom],0),0)

This expression extracts the X coordinate (longitude is the X axis, that is, left and right) from the Geom field value.    The expression gets the (X,Y) vector value of the first coordinate in the object in Geom, and then uses the VectorValue function to extract from that the X value.

The Copy template will use the given expression to populate the Longitude field for each record.

As soon as we enter the expression, a preview in blue preview color for the proposed changes to the Longitude field appears in the table.

To apply the template, in the Transform pane we press the Update Field button.

Done!  We have added a Latitude and a Longitude field to the table that provide the latitude and longitude locations of each point as ordinary, floating point numbers.    The fields are fully read/write.

## Notes

What if the drawing was not in Latitude / Longitude?  If the drawing was not in the Latitude / Longitude coordinate system then using the above procedure would create Latitude and Longitude fields where the values were not in degrees of latitude or longitude but instead were in whatever units were used by the projected coordinate system in use (usually meters).   The simple expressions used extract the X and the Y values from the geom value.   If those are in degrees with a Latitude / Longitude projection used for the drawing we are done.  But if other units are used within the geom the result will not at all resemble degree-based latitude and longitude values.

We could deal with that in one of two ways.  A complicated way is to write fearsomely complex expressions that convert on the fly, basically reprojecting the coordinate system values into Latitude / Longitude coordinate system, so we could extract the X or Y value as degrees.  Guys who attend computer conferences wearing T-shirts that feature impenetrable paragraphs of SQL text on their chests like this approach.   A conceptually much simpler way is to first use the Change Coordinate System command to change the coordinate system of the drawing to Latitude / Longitude, do the above procedure, and then use Change Coordinate System  to change the coordinate system of the drawing back to what it was originally.   Manifold is fast - really fast - so changing the coordinate system, reprojecting data in the drawing, happens very quickly even for impressively large data.

Another way to compute X and Y, when areas and lines are present -   In the example  above we used the expressions

VectorValue(GeomCoordXY([Geom],0),0)

and

VectorValue(GeomCoordXY([Geom],0),1)

...to compute X and Y values.  These expressions use the GeomCoordXY function to take the first coordinate of the object, and then the VectorValue function is used to get either the X or Y value of that first coordinate.  This works for point objects, since point objects have only one coordinate.  Taking the first coordinate means we take the only coordinate of interest.

The approach is less accurate for area and line objects, since simply taking the first coordinate of an area or a line will not get the X,Y location of the center of the area or line, which usually is what we have in mind if we will represent an area or line with a single X,Y location.

Another approach, a more general approach that works with areas and lines as well as points, is to first create a centroid for the object using the GeomCenter function, and to then use the VectorValue function to get either the X or Y value of that center:

VectorValue(GeomCenter([Geom], 0) ,0)

and

VectorValue(GeomCenter([Geom], 0) ,1)

The above expressions will generate a table of X and Y values for all objects in a drawing, for the centers of those objects, even if there are areas and lines present.

User Interface Basics

Tables

Data Types

Selection

Transform Pane

Transform Templates - Drawings

Example: Create a Drawing from Read-Only Geocoded CSV File - A detailed example using a mix of dialogs and SQL to create a drawing that shows data from an external, read-only geocoded CSV file that is linked into the project.

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.

SQL Example: Create a Table with a Constraint via a Query -  A simple example using SQL to create a table with a constraint.

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: Create a Drawing from a Geocoded Table - A partner example this topic.  A geocoded table has records with a latitude and longitude for each record.   This example starts with a table containing a list of cities with a latitude and longitude field for the location of each city.   We create a geom from the latitude and longitude fields using a template in the Transform pane and then we create a drawing that shows the cities as points.  This example shows all the infrastructure steps involved.