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.  

 

This is a real world example dealing with real world issues: we use real data delivered in an open source format, GeoPackage, that requires the installation of third party, open source software, SQLite.   

 

We will:

 

 

The data we would like to use comes in a GeoPackage format file.   Our first task is to enable the ability to work with such files.   Manifold understands GeoPackage format but the format in turn requires installation of a small, self-contained DBMS engine called SQLite.   This sounds like a hassle but in fact the use of a small, but modern, file-based open source DBMS confers onto GeoPackage a host of advantages over Neolithic technology such as shapefiles.

Find and install SQLite capabilitytech_ravi_sm.png

Tech Tip:  The GeoPackage format packs data into a .gpkg file that internally is an SQLite database file.   To work with SQLite database files we must have installed binaries required by SQLite and SpatiaLite on our computer within our execution PATH.  

 

GPKG is a far better choice than shapefiles as an interchange format for vector data.  But the installation requirements for GPKG are not the most user friendly for consumers as a short visit to the SpatiaLite home page at https://www.gaia-gis.it/fossil/libspatialite/index will reveal.  For example, as of this writing there is no installation package for Windows that we can download and install in the usual Windows way to confer GPKG / SQLite / SpatiaLite capability to our system.  Instead, we must find and obtain the right .dll files to enable our applications to do SQLite things and to then place those .dll files where Manifold can use it.

 

The easiest way to do that is set forth in the GPKG topic in the Installing SQLite and SpatiaLite section.

Import spatial data from a GeoPackage file

We can now proceed to importing spatial data from a GeoPackage 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.

 

 

eg_gpkg01_01.png

 

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.   To dock or undock a window or a pane we Alt-click the caption bar or tab.

 

eg_gpkg01_02.png

 

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.

 

eg_gpkg01_02a.png

 

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

 

eg_gpkg01_02b.png

 

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.

 

eg_gpkg01_03.pngeg_gpkg01_03a.png

 

We slow-double-click onto the table name in the usual Windows manner to edit the name into something shorter.   We will choose landcover Table as the name of the table.  Note that if we change the name of a table that is used by a drawing, we will have to tell the drawing about the name change by editing the drawing's properties.

 

eg_gpkg01_04.png

 

We next change the name of the drawing to a similarly short name.  Note that we do not have to name drawings with a name that ends in Drawing. We could use whatever name we want for a drawing but it usually is a good idea to choose a name that has some mnemonic value to it.   In this example we maintain a common style with default naming.

 

We then right-click onto the drawing and choose Properties to edit the drawing's properties.  We edit properties to tell the drawing about the name change we made for the table it uses.

 

eg_gpkg01_05.png

 

We double-click onto the name of the table and edit it to the new name of landcover Table, making sure to retain the square brackets [ ]  which enclose the name.

 

eg_gpkg01_06.png

 

We press Enter to commit the edit and then press OK.

 

eg_gpkg01_07.png

 

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

 

eg_gpkg01_08.png

 

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

 

eg_gpkg01_09.png

 

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

 

Thinking Ahead

Later on in this example we will create a new table in which we will place new geometry derived from the above area objects.   We would like that new geometry to use the same coordinate system as the landcover drawing.   The easiest way to make available the exact, same coordinate system is to save the coordinate system used in the landcover drawing as one of our Favorite Coordinate Systems.

 

eg_gpkg01_09a.png

 

With the focus on the open landcover drawing we switch to the Component panel of the Contents pane.   It shows the specialized New Zealand coordinate system used by the landcover drawing.   We click the coordinate picker button and choose Change Coordinate System.

 

eg_gpkg01_09b.png

 

In the Change Coordinate System dialog we again click the coordinate picker button and in the menu we choose Edit Favorites.

 

eg_gpkg01_09c.png

 

The Favorites dialog opens with current coordinate system in use already loaded into the lower pane.   We press Add to Favorites to add it to the favorites list and then we press OK.  That saves the coordinate system as a favorite and brings us back to the Change Coordinate System dialog.  We click Close.

 

We now have a new favorite coordinate system that we can assign with a single click.

 

Modify the geometry of objects using SQL / Put the modified objects into a new table

We accomplish both objectives with a single SELECT INTO query.   We begin by choosing View - New Command Window - SQL to launch a Command Window.

 

eg_gpkg01_10a.png

 

The Command Window launches with the Query Builder tab on by default.   

 

eg_gpkg01_10.png

 

First, a bit of housekeeping to bring information we want at our fingertips into the window:  We drag the landcover table from the Project pane and drop it into the lower right pane of the Command Window.  That populates the lower right pane with the table's constituent parts so we can name them in our query by double-clicking on them and thus save some keyboarding.

 

eg_gpkg01_11.png

 

We also want to narrow down the large number of items in the query builder list to just those functions we might use.  To do so we click into the filter box ...

 

eg_gpkg01_12.png

 

... and then enter GeomCon to use as a filter.  (The filter box is not case sensitive but using upper and lower case in this example helps teach the names of the functions.)

 

As soon as we start entering the letters the list will get filtered down to those list items that match.  It is often the case that we semi-remember a function's name but not exactly.  If we remember part of the function's name we can narrow down the list to find what we want quickly.   In this case right away we can see the GeomConvertToLine function we want to use.

 

eg_gpkg01_13.png

We begin writing the query, writing out SELECT by hand (an automatic thing for anyone who has used SQL over time...) and then double-clicking on the [fid] entry in the lower right pane to add that instead of manually keyboarding it.   We enter a comma , and then we double-click on the GeomConvertToLine entry in the functions list to add that without manually keyboarding it.   

 

To enter the name of the field to be used as the <geom> argument we highlight the <geom> text and then double-click on the [geom] field entry in the lower right pane to automatically use that field name in place of the <geom> text.  It may sound lazy to double-click that instead of just manually changing the angle brackets < > to square brackets [ ] but in real life use we will become very proficient at using clicks within the Command window's panes to save even the slightest amount of keyboarding.

 

eg_gpkg01_14.png

We write out the rest of the query by hand, double-clicking on the [landcover] entry to add that automatically.   We will call the new table the query creates lines Table.

 

SELECT [fid], GeomConvertToLine([geom])

INTO [lines Table]

FROM [landcover Table];

 

To run the query, we press the ! button in the main menu to execute what is in the Command Window.

 

eg_gpkg01_15.png

 

Manifold swings into action, thinks for a moment and a few seconds later almost 500000 area objects have been converted into boundary lines and the results placed in a newly created table called lines Table.

 

eg_gpkg01_16.png

 

We see that the new table appears the project.   

 

If we double-click the lines Table table to open it we can see that it has two fields: the fid field and the result of the GeomConvertToLine function, a geom that contains line objects within a Manifold geom type.  By default the name of the field resulting from the function is called result.

 

eg_gpkg01_17.png

 

Note that while the GeomConvertToLine function we used can take a geom in any form Manifold understands, such as the geomwkb type of geom that gets imported from a GeoPackage format, it returns a Manifold type geom.  There are, of course, conversion functions in Manifold to change that into any other type of geom we want if we so prefer.

 

If we do not like the default name of result for the results of the function we can use AS to give it a different name, for example, as in the query:

 

SELECT [fid], GeomConvertToLine([geom]) AS [Lines]

INTO [lines Table]

FROM [landcover Table];

 

The above would create a field named Lines instead of result.  

 

If we wanted additional fields in the new table we could, of course, simply add them in the query:

 

SELECT [fid], [WET_CONTEX], [Onshore], [EditDate], GeomConvertToLine([geom])

INTO [lines Table]

FROM [landcover Table];

 

After all, we are using SQL... we can do almost anything we can imagine.

 

Create a drawing from the new table

A more experienced user might write the query to also create a spatial index in the table and to then create a new drawing.  To keep this example simple, and as real world as possible where not everyone writes absolutely everything into their queries, instead of creating the spatial index in the query we will take advantage of how the New Drawing dialog can automatically create a spatial index at the same time it creates a new drawing.

 

eg_gpkg01_17a.png

 

We right-click on the linestable table in the Project pane and choose New Drawing from the context menu.

 

eg_gpkg01_18.png

 

The New Drawing dialog opens loaded with the lines Table table we just created, offering a default Name.  The Geometry box is already loaded with the result field, the one field in the table that is a geometry field.   The dialog knows there is no spatial index in the table it has already checked the Create spatial index box for us by default, since it assumes if we are creating a drawing using a geometry field we will want to also create a spatial index in the table.  

 

The table has been created without a FieldCoordSystem.geom property in the table that specifies the coordinate system used by the geometry in the table, so the New Drawing dialog shows the coordinate system in red text to indicate that the coordinate system must be specified.     The easiest way to do that is to click the coordinate picker button and to choose the Favorite we so thoughtfully saved earlier.

 

eg_gpkg01_18a.png

 

One click does the trick.   This is faster than manually copying the coordinate system JSON string from the FieldCoordSystem.geom property in landcover Table into the equivalent property in the new lines Table we have created.

 

eg_gpkg01_18b.png

 

Now that we have assigned the correct coordinate system for the new drawing to use we are ready to go.  We click Create Drawing.

 

eg_gpkg01_19.png

 

The system creates a new drawing that uses the lines Table table.   We double-click the new drawing to open it.

 

eg_gpkg01_20.png

 

 

Zoomed out to fit the drawing does not seem much different as there are so many line objects they blur together in mostly black rendering.

 

eg_gpkg01_21.png

 

When we zoom further in we see that the area objects in the original drawing (seen below)  have indeed been replaced by their boundary lines.

 

eg_gpkg01_09.png

 

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