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 the sqlite3.dll installation file for SQLite on our computer within our execution PATH.  

 

SQLite is not at all a bad choice as the backbone for a vector data format. But it is not the most user friendly for consumers as a short visit to the SQLite home page at sqlite.org 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 SQLite capability to all applications on our system.  Instead, we must find and obtain the right .dll file to enable our applications to do SQLite things and to then place that .dll file where Manifold can use it.

 

Visit the sqlite.org site and find the precompiled binaries for Windows to download and then if you are working in 64-bit Windows get both the 64-bit and 32-bit precompiled binary packages and if you are working in 32-bit Windows get the 32-bit precompiled binary package.  Download those, unzip and find the sqlite3.dll file so you can put it where Windows and Manifold can find it.

 

If we do not plan on working with SQLite other than through Manifold we can simply put the 64-bit sqlite3.dll installation file into the Bin64 (for 64-bit Manifold installations) and put the 32-bit sqlite3.dll installation file  Bin (for 32-bit Manifold installations) folders within our Program Files\Manifold installation folders for Manifold.  

 

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.   

 

Our first 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.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 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, 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.

 

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

 

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

 

eg_gpkg01_16.png

 

We see that the new table appears the project.   

 

If we double-click the linestable 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 [linez]

INTO linestable

FROM [landcover];

 

The above would create a field named linez 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 linestable

FROM [landcover];

 

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 linestable 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 dialog also knows the coordinate system used by the geometry in the table, so it has pre-loaded that same coordinate system for use by the drawing.    Everything is set up for us.  We click Create Drawing.

 

eg_gpkg01_19.png

 

The system creates a new drawing that uses the linestable 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 have indeed been replaced by their boundary lines.

Notes

Why doesn't Manifold just install the sqlite3.dll as part of Manifold's installation? Manifold probably will if GeoPackage becomes popular as a standard.

 

See Also

Queries

 

File - Create - New Data Source

 

Command Window

 

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 Dialog Expression - Use the Expression tab of the Transform dialog 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.