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 data type geom, and then we fill it with geometry computed from the latitude and longitude fields using a template in the Transform panel. 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. 

Add a Geometry Field

The table we will use is shown below:

eg_dwg_from_geocoded_table01_01.png

 

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.

 

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

 

eg_dwg_from_geocoded_table01_02.png

btn_schema_add.png Press the Add command button.

 

eg_dwg_from_geocoded_table01_02a.png

 

Choose Field in the drop down menu.

 

eg_dwg_from_geocoded_table01_02b.png

 

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.

 

btn_coord_sys_picker.pngPress the coordinate picker button.

 

eg_dwg_from_geocoded_table01_02c.png

 

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.

 

eg_dwg_from_geocoded_table01_02d.png

 

Press OK.

 

eg_dwg_from_geocoded_table01_02e.png

 

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_yoshi_sm.png

 

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.

 

eg_dwg_from_geocoded_table01_03.png

 

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 panel, because there is a transform template that does just what we want.   Super! Easy is good.

 

With the focus on the Cities table we click the Contents pane and then choose the Transform panel.  

 

eg_dwg_from_geocoded_table01_04.png

 

We choose the Geom field as the Target and then we click on Compose Point to choose that template.  We have entered comp into the Filter box to reduce the number of templates to scroll through to find the Compose Point template.  

 

In the X and Y boxes that appear we choose the Longitude and Latitude fields respectively.

 

eg_dwg_from_geocoded_table01_05.png

 

The moment we choose the Compose Point template and the Longitude and Latitude fields the Transform panel previews in blue preview color what the template will do.   

 

In the Transform panel, we press Update Field to commit the action.

 

eg_dwg_from_geocoded_table01_06.png

 

We see that the Compose Point template populated the Geom field with geoms that contain point objects.

 

The above is the main task of this example, accomplished with a single template.   Once we have created point objects in a geom we have the data in more rigorous, high performance form than as Longitude and Latitude 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

eg_dwg_from_geocoded_table01_06a.png

 

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

 

eg_dwg_from_geocoded_table01_06b.png

 

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.

 

eg_dwg_from_geocoded_table01_07.png

 

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

 

eg_dwg_from_geocoded_table01_08.png

 

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.

 

eg_dwg_from_geocoded_table01_09.png

 

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.

 

eg_dwg_from_geocoded_table01_10.png

 

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.

 

 

eg_dwg_from_geocoded_table01_11.png

 

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.

 

eg_dwg_from_geocoded_table01_11a.png

 

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.

 

 

eg_dwg_from_geocoded_table01_11b.png

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

 

eg_dwg_from_geocoded_table01_10.png

 

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.

 

See the Example: An Imageserver Tutorial topic for a step-by-step guide to creating an image server data source.   What follows is a quick summary.

 

 

eg_dwg_from_geocoded_table01_11c.png

 

We right-click into the Project pane and choose New Favorite Data Source.   From the factory installed favorite data sources, we choose the Bing Maps Satellite image server.

 

eg_dwg_from_geocoded_table01_12.png

 

That creates a new data source, which we can expand to see the Bing Maps Satellite Image.   We will use that in a map.   

 

We right-click into the Project pane and choose New Map and then press OK to create a new map, accepting the default coordinate system proposed.     We double-click the new map to open it and then drag and drop the Bing Maps Satellite Image into the map.

 

eg_dwg_from_geocoded_table01_13.png

We zoom into the European part of the Bing display and then drag and drop the Cities Drawing into the map to get the display seen above.      

 

When coordinate systems go wrong the results are usually very obvious, like all of the cities points appearing in a tiny clump off the coast of Africa.    As is clear from the display above, in our work 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 in the Contents pane we click on the Style panel.

 

eg_dwg_from_geocoded_table01_14.png

 

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

 

eg_dwg_from_geocoded_table01_15.png

 

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.

 

eg_dwg_from_geocoded_table01_16.png

 

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.

 

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 Update Field button when we used the Compose Point template we could have pressed the Edit Query button in the Transform panel.    That would have automatically create the equivalent SQL query and open it in a Command Window for us:

 

eg_dwg_from_geocoded_table01_17.png

 

The SQL query generated is:

 

-- $manifold$

--

-- Auto-generated

-- Transform - Compose Point - Update Field

--

PRAGMA ('progress.percentnext' = '100');

UPDATE (

  SELECT [mfd_id],

    [Geom],

    GeomMakePoint(VectorMakeX2([Longitude], [Latitude])) AS [n_Geom]

  FROM [Cities]

  THREADS SystemCpuCount()

) SET [Geom] = [n_Geom];

 

 

What if my latitude and longitude fields are not float64 but are text fields, like nvarchar? - No problem.  That is what CAST is for.   When we used the Compose Point template in the Transform panel  to populate the Geom field we chose the Longitude and Latitude fields in the combo boxes for the template, to use them directly since they were float64 numeric fields.   

 

Suppose our table has two nvarchar text fields, called LonText and LatText, that contain the longitude and latitude coordinates.

 

eg_dwg_from_geocoded_table01_18.png

 

In the illustration above, instead of using the fields directly in the combo boxes we instead choose Expression in the X and Y boxes, and then use the CAST expressions

 

CAST([LonText] AS FLOAT64)  

 

and

 

CAST([LatText] AS FLOAT64)

 

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

 

Manifold is usually quite robust about accepting different types of data within templates but it is always good practice to do an explicit CAST to gurantee the form of data being used.   

 

See Also

Tables

 

Schema

 

Drawings

 

Selection

 

Contents Pane

 

Contents - Select

 

Contents - Transform

 

Web Servers and Image Servers

 

Style: Drawings

 

Transform Templates

 

Transform Templates - 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 panel to show "live" modifications in the second drawing compared to the first drawing.