SQLITE

SQLite is a self-contained, public domain SQL database engine that is often embedded in applications.    The GPKG format introduced by OGC for storing vector and raster spatial data uses an SQLite database container within a single .gpkg file.    To export a table to an SQLite file, choose File - Export and export as a GPKG file.   GPKG is just SQLite by another name.

 

Important:  SQLite requires the installation of third-party software.  A fast and simple installation package is available from the Manifold product downloads page.   Use that package and install as recommended below.  Using packages from other sources is not recommended.  See the discussion at the end of this topic for information on installing the third-party software required.

 

We can either import data from SQLite into a Manifold .map project or we can leave the data within the SQLite file and link to it  to enable editing or other use of the data "in place" within the SQLite database.   Which we choose will depend on the number of records involved, the speed of SQLite we can tolerate or not tolerate, and the convenience of keeping data within the SQLite for any interchange requirements.

 

SQLite has no standard three letter extension for files.   Most SQLite users will utilize .sqlite as an extension, but others will use .db or whatever they want.   ESRI, for example, uses SQLite database files for ESRI's .stylx files that save styles.  ESRI simply uses the .stylx extension.   Change the .stylx extension to .sqlite and we can double-click those ESRI files to open them as an SQLite database.  Or, we can leave the extension as .stylx and open them a SQLite database files as shown below.

 

For automatic imports, the Manifold dataport treats files with a .sqlite extension as SQLite database files, but any file name and extension can be used, as shown below.   The examples below use the Chinook sample database that is often used with SQLite.   

Import an SQLite File

Important: When importing a SQLite file the tables that appear in the Manifold project are Manifold components with no further connection to the SQLite file from which they were imported.  

 

To import from SQLite format (file named with a .sqlite extension):

 

  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 .sqlite file desired.

  4. Everything found in that .sqlite file will be imported into the project.

 

 

To import from SQLite format (file not named with a .sqlite extension):

 

  1. Choose File-Import from the main menu.

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

  3. Click the file desired, to load the file name into the File name box.

  4. Choose SQLITE Files (*.sqlite) in the file type box.

  5. Press the Import button.

  6. Everything found in the specified SQLite file will be imported into the project.

 

 

Example:  We import an SQLite database file named chinook.db:

 

 

Choose File - Import and browse to the desired folder.   Click the chinook.db file to load it into the File name box.

 

 

Choose SQLITE Files (*.sqlite) in the file type box and press Import.

 

That will import into our project everything found in the chinook.db SQLite sample database file.  

 

 

If our SQLite files are named with an .sqlite extension, we can just double-click on them as shown above.

 

 

 

The Chinook sample database contains a variety of tables.   We can double-click on one of the tables to open it.

 

 

Manifold will capture whatever there is in the SQLite database file, and will utilize key fields and indexes in the database.  For example, in the table above, Manifold automatically utilized the index on the CustomerID field so the table is fully editable and selectable.   If we had imported a table from a database with no index, we could add an index in moments, as illustrated in the Add an Index to a Table topic.

Link an SQLite File

SQLite may be slower than a fast DBMS like PostgreSQL, but it is fast enough to enable native storage of data when there are not too many objects.  In Manifold we can take advantage of that by linking a SQLite file into our project.   The link creates a data source cylinder that indicates the data is stored outside of the project, in the original SQLite file.  When we expand the data source we can see the data within.  This works well for smaller data sets until SQLite gets too slow.

 

Important: When linking a SQLite file the tables, images and drawings that appear in that data source in the Manifold project stay resident in the SQLite file.   They are SQLite components even though they may appear in many respects, for the convenience of the user, to be Manifold components.

 

To link a SQLite format file (file named with a .sqlite extension):

 

  1. Choose File-Link from the main menu.

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

  3. Click the .sqlite file desired.

  4. Check or uncheck the Save cache box as desired.

  5. Press Link.  A linked data source will appear in the project.

  6. Press the + icon next to the data source to expand the data source to see the tables it contains.

 

 

To link a SQLite format file (file not named with a .sqlite extension):

 

  1. Choose File-Link from the main menu.

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

  3. Click the file desired, to load the file name into the File name box.

  4. Check or uncheck the Save cache box as desired.

  5. Choose SQLITE Files (*.sqlite) in the file type box.

  6. Press Link.  A linked data source will appear in the project.

  7. Press the + icon next to the data source to expand the data source to see the tables it contains.

 

 

Example:  We link an SQLite database file named chinook.db:

 

 

Choose File - Link and browse to the desired folder.   Click the chinook.db file to load it into the File name box.   

 

The Save cache  button allows setting cache options.

 

 

Most often when linking to a format like SQLite, we will ensure the Save cached box is not checked.  Working with the SQLite database will be faster if we check the box, but if we are going to cache data within the project we may as well simply import the SQLite and use full Manifold speed.   We uncheck the box.

 

 

Choose SQLITE Files (*.sqlite) in the file type box and press Link

 

That will create a new data source in our project, linking into the project everything found in the chinook.db SQLite sample database file.  We can click on the + symbol next to the database cylinder to expand the data source.

 

 

The Chinook sample database contains a variety of tables.   We can double-click on one of the tables to open it.

 

 

The caption in the title bar uses Manifold nomenclature to indicate the invoices table we have opened is within the chinook data source.

Installing SQLite and SpatiaLite

SQLite is not a simple file format like .csv or .shp, but instead is a small DBMS system packaged within a file.   SQLite requires installation of SQLite software, normally installed together with SpatiaLite software, on any computer on which a SQLite file will be used.   If we do not have SQLite installed on our computer we cannot use SQLite files.

 

There are two ways to get the SQLite software required to work with SQLite.  The official way is to visit the official websites for SQLite and download what the creators of SQLite provide.   As a practical matter that path is realistic only for experienced open source users.  The unofficial way is to visit the Manifold downloads page and to download the pre-packaged zip files Manifold provides to make the process easier.  Those zip files are unofficial files that are provided with no support and no guarantees, but the process is much easier for people who are not programmers or expert users of open source.

 

There are two ways to get the SQLite software required to work with SQLite.  The official way is to visit the official websites for SQLite and download what the creators of SQLite provide and to create the various .dlls as desired.   As a practical matter that path is realistic only for experts, and even for experts that path has many opportunities for errors.

 

The practical way is to visit the Manifold downloads page and to download the pre-packaged zip files Manifold provides to make the process easier.  These include all files for SQLite, SpatiaLite and GPKG.  Those zip files may be unofficial files that are provided with no support and no guarantees, but they are provided in good faith by Manifold programmers in a configuration that works well with Manifold.   That process is much easier for people who are not programmers or expert users of open source.

For Experts

The official sources for SQLite and SpatiaLite:

 

 

To install SQLite and SpatiaLite on our computer, we visit the above web pages and then we find and download an appropriate set of pre-compiled binaries or, using our C programming skills, we create our own binaries.  We install the right combination of binaries in the right places and, like magic, everything works perfectly.  This is straightforward for experts.

For the Rest of Us

For users who prefer an easier process, Manifold has assembled an informal collection of binaries for popular open source DBMS packages into two collections of files, one for 64-bit operation and one for 32-bit operation.    The collections include binaries required for clients connecting to MySQL and PostgreSQL / PostGIS, as well as binaries for SQLite and SpatiaLite required to work with GPKG files and other SQLite / SpatiaLite databases.

 

Visit the Downloads Page on the Manifold web site and download the two extension-dlls zip files, one ending in x64 and the other ending in x86,  from the section titled DLLs For Popular Open Source DBMS Packages.  Unzip the x64 zip file into the bin64 folder of your Manifold installation.   Unzip the x86 zip file into the bin folder of your Manifold installation.  Done.

 

The Manifold collections of binaries may be unofficial files that are provided with no support and no guarantees, but they are provided in good faith by Manifold programmers in a configuration that works well with Manifold.  

Installation Example

We are working with Manifold on a 64-bit Windows 10 system.   We have unzipped the extension-dlls-x64.zip file into the bin64 folder of our Manifold installation.  That creates a folder called extension-dlls-x64 as seen below in File Explorer.

 

 

That is all we need to do.   Manifold will automatically utilize any .dll files that are located within the bin64 hierarchy.   If we are curious what .dll files have been installed, we can explore the extension-dlls-x64 folder.

 

 

It contains three folders, one each for MySQL, PostgreSQL / PostGIS and SQLite / SpatiaLite.

 

 

Within the sqlite-spatialite-x64 folder we see the .dll files that comprise the SQLite and SpatiaLite software ensemble.   If we get SQLite and SpatiaLite through other means, such as by direct downloads from the official sites, these are the .dll files we must have within our PATH for 64-bit execution.

Notes

Why doesn't Manifold install required DLLs as part of Manifold installation?  Adding the required .dlls would increase the size of the Manifold and Viewer installation packages by over 15 MB, a big jump considering the installation of Viewer is only 47 MB.   A better way would be to automatically download on demand the required .dll package for those who want to use GPKG, a feature likely to appear in future builds of Manifold.

 

How can tables be exported to SQLite files?   Choose File - Export and export as a GPKG file.   GPKG is just SQLite by another name.  If we export ordinary tables, that is, tables which do not contain geometry fields, the GPKG simply ends up being an ordinary SQLite database that does not require use of any SpatiaLite extensions.

 

See Also

GPKG

 

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 (GPKG) format and change all areas in the data to the boundary lines for those areas and then save those boundary lines as a new table.  We add a spatial index to the table and create a new drawing to visualize the new table.

 

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 styling is retained within the GPKG file.