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.  

 

ico_nb_arrow_blue.pngImportant:  SQLite requires the installation of third-party software.  A fast and simple installation package is available from the Manifold product downloads page.    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:

 

dlg_import_sqlite01.png

 

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

 

dlg_import_sqlite02.png

 

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.  

 

dlg_import_sqlite.png

 

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

 

 

eg_import_sqlite01_01.png

 

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

 

eg_import_sqlite01_02.png

 

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:

 

dlg_link_sqlite01.png

 

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.

 

dlg_link_sqlite_cache.png

 

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.

 

dlg_link_sqlite02.png

 

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.

 

eg_link_sqlite01_01.png

 

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

 

eg_link_sqlite01_02.png

 

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.

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 SQLite 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 are informal, unofficial and not supported in any way.  They provide an experimental guide to what should be obtained and installed using the official, expert process of working with the open source involved.

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.

 

il_gpkg_dlls01_01.png

 

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.

 

il_gpkg_dlls01_02.png

 

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

 

il_gpkg_dlls01_03.png

 

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 just install required DLLs as part of Manifold installation?  Manifold probably will if GeoPackage (which depends upon SQLite) or SQLite become popular as standards with Manifold users.    As an interchange format, GPKG is much better than older interchange formats like shapefiles.   

 

How were the unofficial collections of DLLs obtained?  These have been downloaded over time by Manifold personnel for their own informal, internal use.  Since Manifold people work almost exclusively in 64-bit settings the x64 .dlls are probably more reliable than the 32-bit, x86 versions.

 

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.