Example: Modify GPKG Geometry with SQL then Add Drawing

This topic provides a "Hello, World" example that shows a simple, but typical, task involving spatial data.  We will take a country-sized data set in GeoPackage format and change all areas in the data to the boundary lines for those areas and then place those boundary lines into a new table. We add a spatial index to the table and then create a new drawing to view the boundary lines in the new table.  The topic seeks to illustrate every small step, including those an experienced user would find completely automatic and not worth mentioning.  

 

We will:

 

 

The data we would like to use comes in a GeoPackage (GPKG) format file.   

Import spatial data from a GPKG file

Choose File - Import and then navigate to the folder where the GeoPackage file is, with an extension of .gpkg.   Note that the file we will use in this example is over one gigabyte in size.

 

 

Press Open.  The file is imported as a table and a drawing, seen in the Project pane below.   

 

In this topic we will illustrate windows and panes as undocked. Undocked windows can be moved about on our Windows desktop and can be resized as we like, for example, to make them smaller to make smaller illustrations.   Shift-click the title tab or caption bar for a window or pane to undock or dock.

 

 

The file was downloaded from the lris.scinfo.org.nz site in New Zealand. It shows what covers the land area of New Zealand by covering New Zealand with over 400,000 areas, each of which is classed into one of 43 different types of cover, such as pine forest, deciduous hardwoods, saltmarsh, urban parkland, built up area and other classifications.   

 

 GPKG is a very slow format with many objects so the data takes a long time to import.  See the discussion on GPKG performance in the GPKG topic.

Simplify Project

The project has many tables we do not need and will not use, so we will delete them to reduce clutter in the project.

 

 

 We highlight all components except the two we wish to leave, and then we press the Delete tool bar button.

 

 

Manifold raises a confirmation dialog, advising that many components will be deleted at once.   We can remove this confirmation dialog either in the Tools - Options dialog or by checking the Never show this again box.

Shorten Names

Our next step is to change the inconveniently long names used into something shorter.   If we are happy with the long name we can skip the following steps to rename the table and drawing.   But in the real world such a long name is likely to get on our nerves over time so we would probably change it to something shorter, as we will now do.

 

 

We right-click onto the long drawing name and choose Rename Related.

 

 

We double-click into the drawing name and enter landcover as the new name for the drawing, and then we press Enter.

 

 

We then double-click into the table name, which the dialog has already partially changed for us, and enter landcover Table as the new name for the table, and then we press Enter.

 

 

Press Rename.  The dialog will automatically update all properties within the drawing that reference the table to correctly use the new name of the table.  If the drawing had been used in any map or in a window, all such references would also be updated.

 

 

The new names appear in the Project pane.

 

 

We can now double-click the drawing to open it.

 

 

Seen zoomed out there are so many areas the display is mostly black.  

 

 

If we zoom in we can see there are very many highly detailed areas covering all of New Zealand.

 

 

In the Style dialog we choose a palette for the area background color, using Class_2012 as the field for thematic formatting (which gives a unique value for each area based on the land cover type in that area), and a method of unique values with 100 intervals.  We think there are only 43 different classifications in this table, but using 100 will make sure to get them all in case there are more than we think.  Pressing Tally will find all the unique values.  

 

We press the palette button to assign a palette we like, and then we press Update Style.

 

The actual palette used in the illustration above is the Land Cover palette in the Classic collection, used in reverse order.

 

 

The drawing updates to show thematic formatting based on the palette we used.   The palette provides reasonably green colors in areas that are intended to show forest and similar land cover.   We could tinker with the colors used, perhaps picking colors that are standard within New Zealand government cartography, if we liked.

Convert Areas to Lines

It is easy to convert the geometry of objects, for example, from areas to lines, by using the Transform pane.   The Transform pane provides a list of geoprocessing tools, called transform templates, or simply just transforms, that encapsulate SQL functionality in a point-and-click dialog.

 

With the focus on the opened landcover drawing window, we choose the Transform pane.

 

 

We click on the Convert to Line template.  

 

 

Right away, the drawing window shows a preview of what the transform will do, using blue preview color.   This is just a preview - no changes have been made to the drawing.

 

 

If we press the Update Field action button, the transform will be applied, changing all objects from areas to lines, as shown in the preview.  However, we might not want to alter our original drawing.   Instead, we might want to make the change, but save those changes into a new drawing.  

 

To do that, in the pull down menu for the action button, we change the action button to Add Component.

 

 

A new Options button appears, which allows us to choose what fields will be transferred to the new component and how that new component will be named.  

 

We press Options.  

 

 

In the Transform Options dialog, we change the default names for the new components to something shorter.   A new drawing and table will be created.   We press OK.

 

 

Back in the Transform pane, we press Add Component.  

 

 

Manifold creates a new table and drawing using the names we specified.

 

 

If we double-click open the landcover lines drawing, and then match that view to the landcover window using the Locations button, we can see how each area object in the drawing has been converted to boundary lines.  The illustration below shows the landcover drawing for comparison.

 

 

See Also

Queries

 

File - Create - New Data Source

 

Command Window

 

GPKG

 

Example: Create a New Data Source from a MAP File - Create a new data source from an existing Manifold .map project file.   This is the classic way to nest projects, one calling another, to create libraries of data and projects.   Access to nested projects has effectively zero performance loss and the links within projects take up effectively zero space so we can create huge constellations of data at our fingertips.

 

Example: Create and Use New Data Source using an MDB Database - This example Illustrates the step-by-step creation of a new data source using an .mdb file database, followed by use of SQL.  Although now deprecated in favor of the more current Access Database Engine formats, .mdb files are ubiquitous in the Microsoft world, one of the more popular file formats in which file databases are encountered.  

 

Example: Create and Run a Query - See how the different parts of a command window operate when creating and running SQL queries.   Includes use of the Log tab as well as the ?expression and !fullfetch commands.

 

Example: Automatically Generating CREATE Queries - How to use the Command window to automatically generate SQL in the form of CREATE queries that create a desired component.

 

Example: Clip Areas with a Transform Expression - Use the Expression tab of the Transform pane to clip areas in a drawing to fit within horizontal bounds.   Includes examples of using the Add Component button and also the Edit Query button.

 

Example: Link GPKG and Save Style - A companion topic to the GPKG topic.   How to link a GPKG, open a drawing, Style it and then save so the stying is retained within the GPKG file.