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 topic, Example: Create a Drawing from a Geocoded Table

 

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 can choose Edit - Change Projection to open the Change Coordinate System dialog.    We do this as a quick shortcut to see what the coordinate system is, not to change it.

 

eg_geocode_tbl_from_dwg01_01.png

The dialog confirms the drawing is in Latitude / Longitude projection.  We press the Close button.  

 

If the Info Pane is open that too will show the coordinate system used by the drawing.

 

eg_geocode_tbl_from_dwg01_01a.png

From the Info pane we see the drawing is in Latitude / Longitude projection.

 

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

 

eg_geocode_tbl_from_dwg01_02.png

 

We click on the * asterisk column header to launch the New Field dialog.

 

eg_geocode_tbl_from_dwg01_03.png

Enter Latitude as the target name and choose float64 as the data type for the field.   Check the Set field values check box.

 

As soon as we check the Set field values check box the dialog expands to allow us to specify the contents of the field.

 

eg_geocode_tbl_from_dwg01_04.png

We click the Expression tab and then enter the expression

 

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

 

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

 

eg_geocode_tbl_from_dwg01_05.png

 

As soon as we enter the expression Manifold will preview what will happen using blue preview color in the table.   This gives us a chance to see the result is about what we expect.   We press Add Field to add the field to the table.

 

eg_geocode_tbl_from_dwg01_06.png

The result is that we now have a new Latitude field in the table that gives the latitude value for each point that was extracted from the geom by the expression.

 

We again click on the *  asterisk column header to once again launch the New Field dialog.    This time we enter Longitude as the target new field, using float64 as the data type and once again checking the Set field values check box.

 

eg_geocode_tbl_from_dwg01_07.png

 

The dialog expands and we click the Expression tab.    We enter the expression

 

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

 

to extract the X coordinate (longitude is the X axis, that is, the horizontal axis) 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.

 

eg_geocode_tbl_from_dwg01_08.png

As soon as we enter the expression Manifold previews what will happen using blue preview color in the table.   We press the Add Field button to add the new Longitude field to the table.

 

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

 

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 in whatever units were used by the projected coordinate system in use.   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 more complex way is to write more 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.    A conceptually much simpler way is to first use Edit - Change Projection to change the coordinate system of the drawing to Latitude / Longitude, do the above procedure, and then use Edit - Change Projection  to change the coordinate system of the drawing back to what it was originally.  That works because the Latitude and Longitude fields created in this example were not created as computed fields.   They are just filled with the extracted X or Y value on a one-time basis using whatever was in the Geom field at the time the new Latitude and Longitude fields were added to the table.

 

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

 

Transform Templates - Drawings

 

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

 

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