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.

 

See a similar example as a YouTube video on the Manifold Sales YouTube Channel.

 

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.

 

eg_geocode_tbl_from_dwg01_00.png

 

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 panel of the Contents 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.

 

eg_geocode_tbl_from_dwg01_01.png

The Component panel 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:

 

 

 

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.

 

eg_geocode_tbl_from_dwg01_02.png

 

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

 

eg_geocode_tbl_from_dwg01_03.png

 

We click the <new field> command.

 

eg_geocode_tbl_from_dwg01_04.png

 

We enter Latitude as the Field name and choose float64 as the data type for the field.  

 

In the Expression 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 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 Add button to create the computed field.

 

eg_geocode_tbl_from_dwg01_05.png

 

The controls to add a new field are still active.  We can proceed immediately to add another field.

 

eg_geocode_tbl_from_dwg01_06.png

 

We enter Longitude as the Field name and choose float64 as the data type for the field.  

 

In the Expression box 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 the Add button to create the computed field.

 

eg_geocode_tbl_from_dwg01_07.png

 

A new Longitude field appears in the schema.   We press OK to apply our changes to the table.

 

eg_geocode_tbl_from_dwg01_08.png

 

Done!  We have added Latitude and Longitude fields to the table that provide the latitude and longitude locations of each point as ordinary, 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 panel's Copy template to fill them with an expression.  

 

eg_geocode_tbl_from_dwg01_09.png

 

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

 

eg_geocode_tbl_from_dwg01_10.png

 

We press the <new field> command.   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 Add button to create the field.

 

eg_geocode_tbl_from_dwg01_11.png

 

The <new field> command is still active, so we can create a second field.   We enter Longitude 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 Add button to create the field.

 

eg_geocode_tbl_from_dwg01_12.png

 

We press OK to add Latitude and Longitude fields to the table.

 

eg_geocode_tbl_from_dwg01_13.png

 

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 panel in the Contents pane.

 

eg_geocode_tbl_from_dwg01_14.png

 

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.

 

eg_geocode_tbl_from_dwg01_15.png

 

In the Value box we can choose a field from the list in the drop-down menu, we can enter a fixed value, or we can enter an expression.   We first choose Expression in the box and then 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.

 

eg_geocode_tbl_from_dwg01_16.png

 

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

 

eg_geocode_tbl_from_dwg01_17.png

 

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

 

eg_geocode_tbl_from_dwg01_18.png

 

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

 

eg_geocode_tbl_from_dwg01_19.png

 

In the Transform panel, we now choose the Longitude field as the target, and again we choose Copy as the template.   In the Value box 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.

 

eg_geocode_tbl_from_dwg01_20.png

 

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.

 

eg_geocode_tbl_from_dwg01_21.png

 

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

 

eg_geocode_tbl_from_dwg01_22.png

 

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.

 

Using Computed Fields - We could have created the new Latitude and Longitude fields  as computed fields using the same expressions with the procedure given in the Example: Add a Computed Field to a Table  topic.   In that case the new Latitude and Longitude fields would be dynamically updated if the Geom field changed, for example, if some other Manifold process or a script moved a point.

See Also

User Interface Basics

 

Tables

 

Data Types

 

Selection

 

Street Address Geocoding

 

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

 

Example: Street Address Geocoding -  Geocode a table of street addresses using the Google Geocoder.

 

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 panel and then we create a drawing that shows the cities as points.  This example shows all the infrastructure steps involved.