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 Info pane Component tab 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 Info 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:

 

 

 

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.   

 

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 float64 will be remembered for the next time we add a 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 - Geometry: Compose 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.   Our prior choice of float64 has been remembered and is already loaded into the Type box.

 

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 - Geometry: Compose 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.   

 

 

For a less busy display, we right-click onto the column headers for the Latitude and the Longitude fields, and choose Style to format the numbers.  We pick a format that shows only four digits to the right of the decimal point.   The numbers are still saved in full float64 accuracy, but they are formatted for display to use only four digits past the decimal point.

 

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, using the records as waypoints for GPS devices, or in other systems that can work with geocoded tables but not with geometry data.  

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 Cities Table window, in the Transform pane we choose the Geom field, and then double-click the Copy template to launch it.  

 

 

In the Copy template we choose coordinate Y as the Use option, and we enter 0 as the Value, that is, the number of the coordinate desired.   Choosing 0 means to extract the X coordinate values for the location of the first coordinate (zero based numbering means 0 is the first coordinate) in each object.  Points only have one coordinate, the first coordinate, so that means we will extract the X coordinate for the location of each point.

 

We choose New Field for the Result destination, entering Latitude as the name we would like the new field to use.   We leave the Result type as the default float64.

 

To see a preview, press Preview.

 

 

A new preview column in blue background appears, with the caption of the column head giving the name of the template that is being previewed.   We can drag the preview column to a different position or resize it.  The column shows how the Y coordinate, that is, the latitude, will be extracted from the Geom field.

 

We do not have to do a preview before using a transform, but doing a quick check with a preview is a great way to avoid errors.  In this case, we can see the numbers look right for latitudes.  If any were outside the range of +/- 90 we would instantly know that we got X and Y confused as to which is Longitude and Latitude.

 

Press Transform to apply the template.

 

 

The template adds a new Latitude field to the table, of type float64, and populates it with the Y coordinate value from the geometry for each point.

 

 

Back in the Transform pane, all values are still preserved.  In the pull down menu for the Use box we choose coordinate x.   We edit the Result destination to Longitude, leaving all other values the same.

 

Press Preview.

 

 

A preview column appears using blue preview color.  Scrolling down a bit in the table to look at different values, we can see typical values for longitude.

 

We press Transform.

 

 

The template adds a new Longitude field to the table, of type float64, and populates it with the X coordinate value from the geometry for each point.

 

 

To clean up the display, we right-click onto the column headers for the Latitude and the Longitude fields, and choose Style to format the numbers.  We pick a format that shows only four digits to the right of the decimal point.

 

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 so they are shown with a white background.

 

However, since the added Latitude and Longitude fields are not computed fields, taking their values automatically from the Geom geometry field, if we edit the drawing by moving about the points for airports the Latitude and Longitude fields automatically will not be updated.   The values are whatever they were at the time we used the Transform template to copy their values from the geometry field.

 

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 Reproject Component command to change the coordinate system of the drawing to Latitude / Longitude, do the above procedure, and then use Reproject Component  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.  

 

 

See Also

User Interface Basics

 

Tables

 

Data Types

 

Selection

 

Street Address Geocoding

 

Style Pane

 

Transform Pane

 

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.  

 

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