Example: Import E00 and Rearrange

The world's geospatial data archives are filled with millions of data sets providing spectacularly useful data that are stored within older formats that do not have the conveniences of modern formats.  At times the format is not the problem but instead the data is inconveniently organized.  Getting at the data we want sometimes requires extensive tinkering with Manifold to convert the data into more modern, more useful form.  Lucky for us, Manifold provides a very wide array of tools that can be used to slice and dice data into sensible, modern form.

 

In this example we take a legacy E00 file that contains highly useful data, a drawing showing the geologic regions of Arizona.   Although it was originally created decades ago, the geology of Arizona has not changed since then so the data it contains continues to be extremely useful, if we can rearrange it from the bizarrely opaque form in which it exists into more efficient, modern form.  

 

We have two primary tasks:

 

 

Let's begin by importing the same .e00 file shown in the E00, ESRI topic.

Import an E00 File

To recall the basic process of importing an E00 file:

 

To import from E00 format:

 

  1. Choose File-Import from the main menu.

  2. In the Import dialog browse to the folder containing data of interest.

  3. Double-click the .e00 file desired.

  4. Tables and drawings and other components, such as labels, will be created.

 

 

We navigate to the folder where our source file is located, we click on the az_geol.e00 file and then we click the Open button.

 

 

The Manifold E00 dataport swings into action to import the file and creates in the project multiple tables, drawings, and a map.

 

 

Opening the az_geol map will show a bewildering array of layers containing points, lines and areas.   The AZ_GEOL.ITC layer shows the four points that mark the extents of the data by marking the corners.   The ARC layer shows lines (which ESRI calls "arcs") which outline the areas.   The CNT layer shows points at the centers of areas.  The LAB layer shows points which, apparently, are intended to anchor any labels that are created and added to the display.    The PAL layer is the one we want, because that uses area objects to show the various regions of geology.

 

Further processing using the Style pane and other Manifold facilities can produce a far more comprehensible display, as seen below:

 

 

Let us now create the display above from the daft collection of tables and drawings which the import brought into our project.

Eliminate a Covering Area

We start by cleaning up the main PAL  drawing imported from the E00.  The drawing consists of many area objects that show the various geologic regions of Arizona.  However, it has one massively inconvenient characteristic, a single large area that covers the entire drawing.   We must select that one large area so we can delete it, but we must select it without also selecting and deleting any of the smaller areas underneath that it covers.

 

The technique shown below requires a lengthy list of illustrations and steps because every last detail is shown.  In real life, an experienced operator would accomplish the task in a matter of seconds with a few lighting-fast mouse moves.

Open the PAL Drawing

It is often convenient to work with data against a "known good" background, so we will open the PAL drawing as a layer in a map that uses Bing as a background layer.

 

To do that, we first create a map using a Bing streets image server as a base layer, and then we drag and drop the PAL drawing into the map as a layer.  The .e00 file that was imported provided correct projection information so it will appear in the map in the right location.

 

 

The PAL drawing appears to be just one area object.

 

Important note on Projections:   If we open the Info pane we see that the PAL drawing is in Lambert Conformal Conic coordinate system.  That is OK, but it is often easier when manipulating drawings, as we will do later in this topic, to have the drawing in a coordinate system that is one of our Favorites.   Why? Because then in any dialogs where we must choose what coordinate system we want we can choose that coordinate system with a single click.    Therefore, we will take a moment to change the coordinate system of the PAL drawing to the system default, WGS 84 / Pseudo-Mercator (EPSG:3857).   

 

 

If we open the table for the PAL drawing we see it includes many tables.   The experienced operator immediately realizes the problem is that one big area is covering many smaller areas underneath.

 

 

With the focus on the PAL drawing we use the Style pane and set the area fill color to transparent color.

 

 

Using a transparent fill color only the boundaries of areas are seen, colored in black.  In the illustration above we have clicked off the Bing layer.   The boundaries of smaller areas below the one big area can be seen through the transparent fill color inside the big area.  

 

To get rid of the big, covering area we would like to simply Ctrl-click on it to select it, and then press Delete to delete it.   However, anywhere we Ctrl-click in the drawing within the big, covering area we will end up clicking on not only the big, covering area but also upon any smaller areas below.   For example, suppose we Ctrl-click at the location indicated above (with the selection cursor illustrated in color for the sake of a clearer illustration).

 

 

We end up selecting the big, covering area as well as the smaller area underneath. We have selected two areas, even though we can see only the big selected area.   

 

We can verify two areas were selected by choosing Edit - Select Inverse or by simply pressing Ctrl-I to do the same.

 

 

Inverting the selection will deselect the two areas that were selected (the big area and the smaller area where we Ctrl-clicked) while selecting all of the other areas.  

 

We need to find a way to select just the big, covering area.   There is a simple trick to do that.   

 

We choose Edit - Select None  (or, using keyboard shortcuts, press Shift-Ctrl-A or press Ctrl-A followed by Ctrl-I to do the same thing).   

 

 

Next, we right-click and drag to zoom into the small region shown above in green color.

 

 

This region contains some small objects, including a small U-shaped area.  We could use any region with a small object but we just happen to use this one.   We Ctrl-click at the indicated location.  

 

 

This will select the U-shaped area as well as the big, covering area.   

 

To verify we have done the right thing:  We cannot see the smaller, U-shaped area underneath but we can verify it was selected by doing a Ctrl-I or Edit - Select Inverse.

 

 

That deselects both the big, covering area as well as the smaller, U-shaped area, see in white unselected color above.     We again press Ctrl-I or choose Edit - Select Inverse to get back to just having the big, covering area and the smaller, U-shaped area selected.

 

 

We now Shift-Ctrl-click and drag to begin drawing a box that almost fills the window, ending the motion by mid-way through the motion releasing the Ctrl key to finish with just a Shift-drag.  That mouse motion will deselect all objects that lie entirely within the box.

 

There were only two objects that were selected, the smaller, U-shaped area and the big, covering area.  Only the smaller, U-shaped area fits entirely within the deselection box so that area will be deselected, leaving only one object in the selection, the big, covering area.

 

 

After the deselection the display looks the same because we cannot see below the big, covering area.   We can check if the smaller, U-shaped area was indeed deselected by doing a Ctrl-I or Edit - Select Inverse.

 

 

That deselects the big, covering object and selects everything else.  We can see that the smaller, U-shaped object is now selected, as well as the adjacent area that surrounds it, both of which were hidden below the big covering area.    Great!  We can go back to just the big, covering object being alone in the selection with a Ctrl-I or Edit - Select Inverse.

 

 

Having verified only the big, covering area is in the selection we can press the Delete key.

 

 

That deletes the big, covering area and shows the other objects below.

 

 

Zooming out we can now see all of the areas without a big, covering area hiding them.

 

Whew!  That was lots of work to get rid of a single, inconveniently positioned area...  now we can move on to attaching attributes to the areas so we can color them by their geologic meaning.

Attach Attributes to Areas

We would like to color each area by the type of geology it contains.  However, there are no fields in the table for the PAL drawing that provide that information for each area.  

 

 

However, there is another table that provides a list of geology types, the AZ_GEOL.PAT table:

 

 

The table includes a DATA field that provides a typical abbreviation of geology type that we can use to color areas by geometry.  This table has no geometry, but it appears to have a field, AZ-GEOL-ID, that matches an ID field, #, used in the PAL Table.   We can use the commonality between the two fields to do a JOIN between the two tables to add a new field that for each area has a geology identifier taken from the DATA field.

 

We have two ways to do a JOIN in Manifold, and will show both in this example:

 

 

 

Add a Column using Edit - Join

With a few clicks we can add the desired column to the PAL Table using the Edit - Join dialog.

 

With the focus on the open PAL Table window, we choose Edit - Join in the main menu

 

 

In the Join dialog we choose AZ_GEOL.PAT from the pull down menu for the joined table box. Manifold guesses we want to use the # field as the key field for that table, a good guess.   Manifold guesses we want to use the AZ-GEOL-ID field as the key field for the joined table.   That also is a good guess, so we leave it in place.   If Manifold guessed wrong, we could easily pick the # field and the AZ-GEOL-ID field from the pull down menus.

 

Press the Add button to add a new field.  

 

 

The pull down menu shows fields in the AZ_GEOL.PAT table.  We choose the DATA field.

 

 

Press Join.   Done!   

 

 

The new DATA field appears in the PAL Table, correctly populated with the right text description for each DATA field, automatically copied from the AZ_GEOL.PAT table.

Style the Drawing

We can now use Style to color the areas using the geology code in the Data field for each area.   

 

 

With the focus on the new drawing, we use the Style pane.

 

 

In the Style pane we click on the area fill color well.   We choose DATA as the controlling field,  meaning we want to use the field called DATA to control the color.   In the method box we choose unique values with 100 set in the Values box to cover up to 100 unique values.   We press Tally to fill the palette pane with a list of the many unique values found for geology codes in the DATA field.

 

To assign a palette, we click on the Apply palette button and choose the CB Accent palette from the Color Brewer portion of the palette pull-down menu.   Now we can press Update Style to apply that palette to the areas in the drawing based on the content of their DATA field.

 

 

Immediately, we get a much better sense of the geology of Arizona.  In the illustration above we have turned on the Bing layer.  

Notes

How did we know to use AZ_GEOL-ID to match to the # field in the Join?  - We did not know, but it seemed like a reasonable guess.  The only other field that was a candidate for an easy JOIN in the AZ_GEOL.PAT table was AZ_GEOL#, but we tried the AZ_GEOL-ID field first and the results looked good, so that is what we used for this example.   Sometimes when trying to make sense of legacy data it is quicker and easier to apply trial and error than to spend a lot of time trying to find documentation for ancient data sets.  Good documentation is better, of course, if we can find it.

Doing a JOIN with SQL

If we want to learn more about doing JOINs, a very powerful capability of SQL, we can achieve the same task as accomplished with the Edit - Join dialog above using an SQL query.   This requires a bit more work than using the point and click Join dialog, but mastering SQL for such tasks opens the door to endless automation and flexibility.

 

 

We create a query that we name Join Query and enter the following SQL:

 

SELECT [PAL Table].[#], [PAL Table].[Geom], [AZ_GEOL.PAT].[DATA]

INTO [Geology Table]

FROM [PAL Table] LEFT JOIN [AZ_GEOL.PAT]

ON [PAL Table].[#]=[AZ_GEOL.PAT].[AZ_GEOL-ID];

 

The query will take some fields from the two tables, do a JOIN on the fields that have values which can be matched (the ID of each region) and put the results as new records into a table called Geology Table.   The JOIN will run very slowly, since we have not created an index on either of the fields in the two tables that are used in the JOIN.   It could go much faster if we take a moment in the Edit - Schema dialog for either of the tables to create an index on the # field or the AZ-GEOL-ID field.

 

 

The Geology Table has a geom and an attribute giving a geology code for each area.  Fields are shown in read-only gray background color since we have not added an index to the table.   We could have done that in the JOIN query, but to keep the query simpler we did not.  Instead, we use the technique shown in the Add an Index to a Table  topic to add an index to the table.

 

 

That's better!   Now we have all we need to create a new drawing and color it as desired.

 

In the Project pane we right-click onto Geology Table and choose Create-New Drawing.

 

 

The dialog launches already based on the Geology Table, using the Geom field.   The coordinate system is indicated in red color as not yet assigned.  To assign the coordinate system we click on the picker button.

 

 

We choose the default, Pseudo-Mercator system from our favorites since earlier in this topic we re-projected the PAL drawing into that default standard coordinate system.    We press Create Drawing.

 

 

Dragging and dropping the new drawing into our map (and turning off the lower PAL drawing layer) we see it is, as expected, a drawing of areas.  

 

With the focus on the new drawing, we use the Style pane.

 

 

In the Style pane we click on the area fill color well.   We choose DATA as the controlling field,  meaning we want to use the field called DATA to control the color.   In the method box we choose unique values with 100 set in the Values box to cover up to 100 unique values.   We press Tally to fill the palette pane with a list of the many unique values found for geology codes in the DATA field.

 

To assign a palette, we click on the Apply palette button and choose the CB Accent palette from the Color Brewer portion of the palette pull-down menu.   Now we can press Update Style to apply that palette to the areas in the drawing based on the content of their DATA field.

 

 

That makes for a beautiful display.  We can get rid of the unnecessary PAL layer, enlarge the window size, and turn on the Bing layer.  

 

 

The image seen above is the same map published in larger size on the Data Sources page on the Manifold website.  

Videos

Join Dialog Part 1 -  Spatial Joins - See the new Join dialog in action, effortlessly doing spatial joins without SQL.  Using a map with two layers, a cities layer showing cities in the US and a states layer showing states in the US we add the name of the state for each city to the cities attributes.   Next, we take data from the cities layer and add it to the states layer, summing up the populations for cities in each state to get a total population for the state, plus maximum and minimum city populations in that state.  Works in Viewer, too.

 

Join Dialog Part 2 - Joins between Tables - A fast-paced video showing three examples of joins between tables, with no SQL required:  We add a Publishers field to each title in a list of books (one to many) and then we show how with a single click we update the table to incorporate changes made and new data added.  Next, we add three new fields to the Publishers table to harvest data from books (many to one) to create fields that contains a list of titles, the average sales price of a book and the last publication date.   Finally, we show how to alter the saved query to update the list of titles into JSON format. Wow!  Works in Viewer, too.

 

Join Dialog Part 3 - Raster to Vector Joins - The third, action-packed video on the new Join dialog shows effortless raster to vector joins using a point and click dialog with no SQL required. Everybody thinks of joins as just between tables, but taking data from a raster to load into attributes in vector objects is just another join.  We use a single-channel raster that shows terrain elevation near Livermore, California, and use a point and click join to transfer the height of the terrain to points in a drawing layer above that terrain.  Next, we switch to a vector layer of parcels as areas, and transfer the average height of the terrain under each parcel, the maximum height and the minimum height, all done effortlessly with a point and click.  Along the way we show how to customize the update query to round values.  Easy!  Works in Viewer, too.

 

See Also

Selection

 

Select Pane

 

Style

 

Edit - Join

 

E00, ESRI

 

Example: Spectacular Images and Data from Web Servers

 

Example: An Imageserver Tutorial

 

Example: Use Edit - Join to Map a Pandemic - Creating a data source for a CSV web server, we acquire current data on cases and deaths worldwide for the Covid-19 pandemic.  We then use the point and click Edit - Join dialog to rapidly join that data, automatically aggregated as we desire, into a world map for visualization.

 

Example: Create a Map Showing OSM Use by Country - A start-to-finish real life example of map creation that combines various Manifold capabilities, including use of Edit - Join.  Copying a table of numbers from a web site, we create a map that is thematically colored to show usage of OpenStreetMap by country in proportion to the population of that country.

 

Example: Trace Vector Areas from Raster Pixels - This example follows the Trace Vector Areas from Raster Pixels video on the Gallery page.   We use the Trace Areas template in the Transform pane for images to create a drawing with vector areas covering regions of similarly-colored pixels.  Next, we use a simple query to add classification codes from a USGS table of classes to the resulting drawing, using a simple INNER JOIN SQL statement.