Example: Create a Drawing from a Geocoded Table

A geocoded table has records with a latitude and longitude for each record.   To create a drawing, we need a field for each record that is a geometry field, containing a point, line or area object.   To create a drawing from a geocoded table we must create a geometry field from the latitude and longitude.  The geometry field will contain a point object at the latitude and longitude location specified.

 

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 geometry field, using a geometry data type, and then we fill it with geometry computed from the latitude and longitude fields using a template in the Transform pane. We can then create a drawing from the table, which shows the cities as points, automatically drawing each point where the geometry for that city says the point should be located. 

 

The table we will use is shown below:

 

 

For each record there is a Longitude field and a Latitude field, both float64 numeric fields, that provide the longitude and latitude where the city is located.       The first task is to create a geom type field so each record can have a point object created at that longitude and latitude.

Add a Geometry Field

We choose Edit - Schema  from the main menu to launch the Schema dialog.

 

 

Press the Add command button.

 

 

Choose Field in the drop down menu.

 

 

In the Field dialog, enter the name Geom and choose type geom.  We want the geometry field to use latitude and longitude coordinates, like those in the table from which the geometry will be built, so we will change the default Pseudo-Mercator coordinate system to Latitude / Longitude.

 

Press the coordinate picker button.

 

 

Choose Latitude / Longitude from the favorites list in the drop down menu.   This is one of the two default favorites.   If we wanted to choose  different coordinate system, we could press the Edit Coordinate System choice to launch the full Coordinate System dialog to choose whatever other projection we wanted, or to specify a custom projection system.

 

 

Press OK.

 

 

Back in the Schema dialog we see the new field in provisional, bluish color.  To commit changes to the table, adding the field, we press Save Changes.     

 

Tech Tip:  We could have chosen whatever name we wanted for the new field, but a useful Manifold convention observed by most Manifold users is to name geometry fields that use the geom data type with a name like Geom, Geom1, temp_Geom, or similar.    That way, it is easy at a glance to see in a table which fields are geoms and which are something else.    It sure beats using a name like Harry or Ringo for a geometry field.

 

 

The values of Geom for each record are NULL, since so far the field has just been created and has not been populated with any values.    We will now populate the Geom field with geometry data that is a point object for each record at the coordinates specified by the Longitude and Latitude fields.

Create Geometry from Latitudes and Longitudes

That is easy to do by using the Transform pane, because there is a transform template that does just what we want.   Super! Easy is good.

 

With the focus on the Cities table, in the Transform pane we choose the Geom field, and then we double-click the Compose template to launch it in the Transform pane. 

 

 

In the Compose template, in the pull down menu for the Compose box we choose point.    In the pull down menu for the X box we choose the Longitude field, and in the pull down menu for the Y box we choose the Latitude field.

 

For the Result destination we choose Same Field, meaning we will update the Geom field with the results of the transform operation.

 

Press Transform.

 

 

The template immediately populates the Geom field with point geometry values, where the coordinates for the points are taken from the Longitude and Latitude fields.

 

The above is the main task of this example, accomplished with a single Transform operation.  Once we have created point objects in a geom we have the data in more rigorous, higher performance form than as Longitude and Latitude numeric values in the table.   What now follows is infrastructure related to how we want to use the new geometry data we have created.

Create a Drawing using the Table

 

To create a drawing from the table we right-click on the Cities table and choose Create - New Drawing in the context menu.

 

 

The New Drawing dialog opens pre-loaded with the Cities table.   Manifold knows the Latitude / Longitude coordinate system has been assigned to the Geom geometry, so it shows that coordinate system.  

 

We press Create Drawing to create the new drawing.

 

 

A new drawing called Cities Drawing appears in the Project pane.   We can open it by double-clicking on it.

 

 

The drawing shows a point at the latitude and longitude location of each city in the table.   In the illustration above, we have used Style to alter the point size to 4 instead of the default 5 and we have used a Stroke width of .5 for the round point symbols, instead of the default 1.  

Selections and Filters

At this point we have achieved the stated objective for this example, to create a drawing from a geocoded table.   But since we've created a drawing we may as well keep going for a few more tutorial steps.

 

We will begin by illustrating how selections can be made in the drawing and will automatically select corresponding records in the table.

 

 

We zoom into the European portion of the drawing and use ctrl-click-and-drag to draw a selection box that selects some of the points.

 

 

We click back onto the table to move the focus to the table.    We have resized the table window to show more rows.   Records corresponding to selected cities in the drawing have also been selected in the table, appearing in red selection color.

 

 

 

If we like, we can tell the table to show only selections.   We choose View - Filter and click on Selected to show only selected records.

 

 

The table now only shows selected records.  The small "filter" icon in the upper left corner of the table indicates the display is a filtered display.

 

 

To switch back to showing all records we click View - Filter and then choose All.

 

 

The table will then show all records.

Check the Drawing against an Image Server

When we look at the Cities Drawing window the points appear to be in the right locations relative to each other, but suppose the coordinate system is somehow wrong?   If so, the points might appear to be in the right places but only as long as we do not compare them to "known good" data.    

 

It is easy to check if we have created the drawing correctly by displaying it in a map as a layer together with "known good" data.   The easiest way to do that is to overlay the new drawing as a layer above an image server display taken from Microsoft Bing, Google Earth or other image server.

 

In the Project pane, we right-click onto Cities Drawing and choose New Map

 

 

The New Map dialog launches with the Cities Drawing selected as a layer.   For the Base layer, we choose Bing Maps Satellite from the pull-down menu.    Press Create Map.

 

The New Map dialog creates a new Map in the project, called Map.   It also automatically creates a Bing satellite data source, to use as a base layer for the new map, and it automatically adds the Cities Drawing to the new map.  

 

We double-click open the new map.  

 

 

We zoom into the European part of the Bing display.   The Cities records we selected are still selected, and are shown in red selected color in the map.

 

When coordinate systems go wrong the results are usually very obvious, like all of the cities points appearing in a single dot off the coast of Africa.    As is clear from the display above, the cities points are in the correct position.

Format the Drawing Using Style

Since we are playing with the results of our work we may as well make the points prettier.   We click on the Cities Drawing tab to ensure it has the focus, we choose Edit - Select None to clear the selection, and then we choose the Style pane.

 

 

As discussed in the Style topics, we change the Fill Color for points to a rich yellow.

 

 

The result is a more legible display with better color contrast for the points.  In the illustration above, we have clicked Edit - Select None to deselect the selected points, so now all the points appear in yellow color.

 

 

Style is a property of the drawing, not of the window, so when we change the fill color style that will change the color in any window that shows the Cities Drawing.   If we still have our Cities Drawing window open the color changes there as well.    Note from the above that a selection is a property of a table that propagates automatically to all drawings that use that table, so when we cleared the selection in the map using Edit - Select None the selection was cleared from the Cities Drawing window as well.

Creating Geometry from Text Fields

Geocoded tables often are imported from text formats, such as CSV or JSON, where the fields may look like numbers but in fact are text strings.   Can we create geometry from such strings?  Sure!  No problem.  That is what expressions using the CAST SQL Operator are for. 

 

When latitudes and longitude fields are numeric fields, we can use the procedure used in the example above, using the Compose : point operation in the Transform pane  to populate the Geom field directly from the numeric Longitude and Latitude fields in the combo boxes for the template.   Those fields are numeric fields of type float64 so they appear in the pull down menus for the X and Y boxes.  

 

When latitudes and longitude fields are text fields, we can use the same procedure, but using the Expression choice in the pull down menus for the X and Y boxes.  

 

Suppose our table has two nvarchar text fields, called LonTxt and LatTxt, that contain the longitude and latitude coordinates as text strings.

 

 

With the focus on the Cities table, in the Transform pane we choose the Geom field, and then we click the Compose : point recently used shortcut to launch that operation in the Transform pane.  
We could also double-click the Compose template if we prefer.

 

 

In the Compose template, in the pull down menu for the X box we choose Expression.   Note that the LonTxt and LatTxt fields do not appear in the pull down menu.  They do not appear because they are text fields, and the pull down menu only shows numeric fields, since only numeric fields can be used directly for X or Y values.

 

Choosing Expression launches the expression builder dialog.

 

 

In the Expression dialog we enter the following expression:

 

CAST([LonTxt] AS FLOAT64)  

 

Press OK.


The CAST expression takes the text contents of the LonTxt field and creates the float64 numeric equivalent.

 

 

Back in the Transform pane, in the pull down menu for the Y box we choose Expression, to launch the expression builder dialog for that box.

 

 

In the Expression dialog we enter the following expression:

 

CAST([LatTxt] AS FLOAT64)  

 

Press OK.


The CAST expression takes the text contents of the LatTxt field and creates the float64 numeric equivalent.

 

 

Back in the Transform pane, instead of explicit numbers or numeric fields in the combo boxes, using the Expression choice we have loaded the CAST expressions

 

CAST([LonTxt] AS FLOAT64)  

 

for X and

 

CAST([LatTxt] AS FLOAT64)

 

for Y within the parameter boxes.   Manifold evaluates those expressions on the fly to cast the nvarchar data into float64 for use by the template.   Very cool, for sure.

 

Press Transform.

 

 

The template evaluates the expressions, and constructs point geometry for each record, using converted string data from the LonTxt and LatTxt fields for the coordinates of the points.  

 

 

If we display the created points in a map we see that the on the fly CAST conversion from nvarchar text strings into float64 numbers was done correctly.

 

Notes

Is Latitude / Longitude enough? - Choosing the default favorite Latitude / Longitude coordinate system is choosing more than the use of degree-based latitude and longitude coordinates: it is also choosing the ellipsoid for the coordinate system as well.   When downloading geocoded data from random websites we often will end up with tables that have latitude and longitude coordinates for each record but with absolutely no information on all the details, such as ellipsoid, of the coordinate system used for those coordinates.   As discussed in the Latitude and Longitude are Not Enough essay topic, using the Latitude / Longitude standard coordinate system that uses the WGS84 ellipsoid is usually not a bad guess.    Given that tables with latitude and longitude are not usually very precise the WGS84 ellipsoid is probably as good a guess as any other.   That is what the default favorite Latitude / Longitude coordinate system uses.

 

What does the SQL look like to create points in a Geom from latitude and longitude fields? - Aha!  There is a Manifold command for that.   Instead of pressing the Transform button when we used the Compose Point template we could have pressed the Edit Query button in the Transform pane.    That would have automatically create the equivalent SQL query and open it in a Command Window for us:

 

 

The SQL query generated is:

 

-- $manifold$

--

-- Auto-generated

--

-- Compose

--   Layer: Cities

--   Field: Geom

--   Compose: point

--   X: Longitude

--   Y: Latitude

--   Result: (same field)

--   Resources: all CPU cores, all GPU cores

--   Transform selection only: FALSE

--

UPDATE [Cities] SET

  [Geom] = GeomMakePoint(VectorMakeX2(Coalesce([Longitude], 0), Coalesce([Latitude], 0)));

 

The query uses three  SQL functions and the COALto create point objects from scalar numbers.

 

It would be tempting to write that last line as:

 

  [Geom] = GeomMakePoint(VectorMakeX2([Longitude], [Latitude])) ;

 

It may seem that if the Longitude and Latitude fields give the coordinates we use, why bother with the Coalesce function?  Why not simply use Longitude and Latitude as arguments to the VectorMakeX2 function, which creates an x2 numeric vector from two scalar numbers, which in turn can be used by the GeomMakePoint to create a point?

 

The Coalesce function returns the first non-NULL value out of a list of values.  An expression such as Coalesce([Longitude], 0) returns either the value of Longitude or, if Longitude is NULL, zero.   It is a way of ensuring that the VectorMake2 function always is fed arguments that are non-NULL.

See Also

Tables

 

Schema

 

Drawings

 

Selection

 

Select Pane

 

Transform Pane

 

Web Servers and Image Servers

 

Style: Drawings

 

SQL Functions

 

Example: Multiple Drawings from the Same Table - Illustrates how easy it is to create multiple drawings that use the same table and same geometry by copying and pasting an existing drawing.  Each new drawing takes no additional storage space in the project, but can be formatted differently.   

 

Example: Create a Drawing Dynamically from a Geocoded Table - A more general, cooler way to create a drawing from a geocoded table, without adding any fields to the table.   We create a small query that generates geometry on the fly, and then we create a drawing from the query.   This is how more advanced users do it, using a technique that is perfect for creating drawings from tables in remote DBMS packages or read-only files.

 

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: 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 example shows how to add a spatial index using a geom field in a table.

 

Example: Create a Geocoded Table from a Drawing - A partner example to this topic.  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.

 

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

 

Example: Create a New Data Source from a Manifold Image Server - Manifold image server modules are snippets of code which use the Manifold Image Server Interface (ISI) to automatically fetch image tiles from popular image servers like Virtual Earth, Wikimapia, Yahoo!, Google Maps, Yandex and many others. Image servers can provide street maps, overhead satellite imagery, combinations of streets and satellite imagery and other data as well.  Using Manifold Image Servers is one of the most popular Manifold features.

 

Example: Drawings use Geom Fields in Tables  - An essential discussion on how drawings are created from geom fields in tables, including how the drawing knows which coordinate system to use.

 

Example: Two Drawings from the Same Table - Take a table with a geom field that is visualized by a drawing.  Add a second geom field to the table and create an rtree index on that field so it can be visualized by a drawing.   Copy the first drawing, paste it and adjust the pasted copy so it uses the second geom field. Demonstrate how to use the Transform pane to show "live" modifications in the second drawing compared to the first drawing.