Example: Create a Drawing from a Geocoded Table

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.  

Add a Geom 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

In the Schema dialog we click on <new field> and then in the Field box we enter a name for the new field, Geom, and in the Type box we choose geom as the type.   Press Add to add the field and then OK to close the Schema dialog.    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 geom fields using a name like Geom, Geom1, temp_Geom and 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.    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 a Transform panel template.   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.   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 really 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 a coordinate system has not yet been assigned to the geometry, so it assigns a temporary placeholder in the form of the default Pseudo Mercator coordinate system, displaying that in red text to warn us a coordinate system must be assigned.

 

New, blank drawings are created by default using the EPSG:3857 coordinate system, which is the Pseudo-Mercator coordinate system utilized by web servers like Google and Microsoft's Bing.     But our table uses latitude and longitude values so we will assign the Latitude / Longitude coordinate system.  

eg_dwg_from_geocoded_table01_06c.png

 

btn_coord_sys_picker.pngWe click the coordinate picker button and in the drop down menu we choose Latitude / Longitude, which is conveniently listed as one of the built in Favorite coordinate systems.

 

eg_dwg_from_geocoded_table01_06d.png

 

In the New Drawing dialog we see the coordinate system assigned for the new drawing is now Latitude / Longitude. It is displayed in black text because it has been deliberately assigned and is not just a temporary placeholder.  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.  

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.

 

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

How did we know the original Cities table used Latitude / Longitude? - We did not know exactly what coordinate system was used for the latitude and longitude values in our Cities table.  When downloading geocoded data from random websites we usually will end up with tables that have latitude and longitude coordinates for each record but with absolutely no information on 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 as any other.   That is what we used.

 

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.   

 

eg_dwg_from_geocoded_table01_18.png

In the illustration below we show a different situation, where we are using two text fields, LonText and LatText, that have the longitude and latitude coordinates as nvarchar text.  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 so we know for sure the form of data being used.   

 

See Also

Tables

 

Drawings

 

Selection

 

Contents Pane

 

Contents - Select

 

Contents - Transform

 

Web Servers and Image Servers

 

Street Address Geocoding

 

Coordinate System

 

Style: Drawings

 

Transform Templates

 

Transform Templates - Drawings

 

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

 

Example: Copy one Column into Another Column with Transform - How to use the Transform panel to copy the contents of one column in a table into another column, but only for selected records.  Uses the Products table from the Nwind example data set.  

 

Example: Transform Field Values using an Expression in the Transform Panel - How the Expressions tab of the Transform panel may be used to change the values of fields.  

 

Example: Construct JSON String using Select and Transform - Use the Select panel and the Transform panel  to manually construct a JSON string using values from other fields in a table. Shows how we can manipulate text to build desired contents in a field.