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 dialog and then we create a drawing that shows the cities as points.  

 

See this example as a YouTube video on the Manifold Sales YouTube Channel.

Add a Geom Field

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.

 

eg_dwg_from_geocoded_table01_01.png

We click on the * asterisk column header to create a new field.

 

eg_dwg_from_geocoded_table01_02.png

In the New Field dialog we choose a name for the new field, Geom, and choose geom as the type.       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.

 

Press Add Field to add the field to the table.

 

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, 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 given there is a Transform dialog template which does just that.   With the focus on the Cities table we choose Edit - Transform to launch the Transform dialog.

 

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.  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 dialog previews in blue preview color what the template will do.   We press Update Field to commit the action and then we press Close to close the Transform dialog.

 

eg_dwg_from_geocoded_table01_06.png

 

Dragging the right border of the Geom column head to widen the column 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.   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 New Drawing in the context menu.

 

eg_dwg_from_geocoded_table01_06b.png

 

The New Drawing dialog opens pre-loaded with the Cities table.  We accept defaults, using the recommended Name, Geometry field and the checked box to create a spatial index.   However, by default the new drawing would be created using the default Pseudo-Mercator coordinate system Manifold uses.   That is not the right choice in this case.

 

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 and not the more abstract numbers used in any form of Mercator projection, such as EPSG:3857.  

 

We do 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 will use.

 

We press the [...] browse button to launch the Coordinate System dialog.

 

eg_dwg_from_geocoded_table01_06c.png

In the Coordinate System  dialog we click on the Standard tab and then we enter lat into the filter box to reduce the endless list of coordinate systems down to those few which have lat in their names.  

 

We click on Latitude / Longitude to select it as the coordinate system desired and then press OK.

 

eg_dwg_from_geocoded_table01_06d.png

 

Back in the New Drawing dialog we see the coordinate system to be used for the new drawing is now Latitude / Longitude.  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.  

Demonstrate Selection from the Drawing into the Table

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 then transferred to 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.

 

Next, we choose Edit - Select to launch the Select dialog.

 

eg_dwg_from_geocoded_table01_11.png

 

We click on the Window tab and click on the Cities Drawing entry to choose that as the source of any selection we might want to transfer to the table.

 

eg_dwg_from_geocoded_table01_11a.png

 

Right away, the table window previews for us in blue preview color the records that would be selected based on the selection of objects in the Cities Drawing.   To use that selection we press the Replace Selection button in the Select dialog.

 

eg_dwg_from_geocoded_table01_11b.png

 

The selected records appear in red selection color.

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 so 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 Data Source.   We choose Web Server: imageserver as the Type and choose the Bing Maps Satellite image server.   We press OK.

 

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 and then we choose Edit - Style to launch the Style dialog.

 

eg_dwg_from_geocoded_table01_14.png

 

As discussed in the Style topics, we change the Color, fill color to a bright yellow.

 

eg_dwg_from_geocoded_table01_15.png

 

The result is a much 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 window, so the Cities Drawing window can have a selection while there is no selection in the Cities Drawing tab of the map.

 

If we want to transfer the selection to the  Cities Drawing tab of the map that is easy to do.   We click on the Cities Drawing tab of the map to ensure it has the focus and then we choose Edit - Select to launch the Select dialog.

 

eg_dwg_from_geocoded_table01_17.png

 

In the Select dialog we click on the Window tab.   That tab now lists both the Cities table as well as Cities Drawing, since both of those windows have selections in them that our drawing's tab in the map can use.   We click on Cities Drawing.

 

eg_dwg_from_geocoded_table01_18.png

 

That immediately previews in blue preview color in the map what the selection would be if we pressed the Replace Selection button in the Select dialog.

Notes

 

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 dialog.     That would have automatically create the equivalent SQL query and open it in a Command Window for us:

 

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

 

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 use the CAST expressions CAST([LonText] AS FLOAT64)  and CAST([LatText] AS FLOAT64) within the combo 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.   

 

eg_dwg_from_geocoded_table01_20.png

 

 

See Also

Tables

 

Drawings

 

Selection

 

Web Servers and Image Servers

 

Street Address Geocoding

 

Coordinate System

 

Style: Formatting Drawings and Labels

 

Transform Dialog

 

Transform Templates

 

Transform Templates - Drawings

 

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 dialog 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 dialog 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 Dialog -  How the Expressions tab of the Transform Dialog may be used to change the values of fields.   We include an example of changing the price of selected products and using two different Transform dialogs open at the same time for two different table windows.

 

Example: Construct JSON String using Select and Transform - Use the Select and Transform dialogs 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.