How to create a SpatiaLite database with QGis

If you start to feel tired of managing your data as shape files (this format dates back to the eighties!) and you yearn for a genuine database for your projects, most certainly, you will be looking to use PostGis. There is a big gap, and the ‘jump’ is, by no means, an easy one. In this article, we will discuss an alternative solution, the SpatiaLite database. And, believe me, it is much more easier….

Comparison between SpatiaLite and PostGis

In this article we will examine the use of SpatiaLite, an open source cross-platform software, simple, lightweight and robust. SpatiaLite, a spatial database management system (DBMS)  extension of SQLite, a DBMS intrinsically simple and lightweight. SpatiaLite (and SQLite) is based on an individual architecture resulting in an, almost, inexistent management and/or installation. The defect is that it does not work properly with simultaneous access and that it does not support the client/server architecture.

For a more complete DBMS, PostGIS is an excellent option in the Open Source world. PostGis offers many more features than SpatiaLite. GeoDjango documentation shows a comparison of the spatial characteristics supported by each database https://docs.djangoproject.com/en/dev/ref/contrib/gis/db-api/#compatibility-tables.
Please note that it is not a direct comparison between the two databases. It highlights the features supported by GeoDjango. However, it offers a pretty good idea of the differences between the two applications.
The choice between PostGIS and Spatialite depends mainly on the research goals. Spatialite is a better option if you need an autonomous or embedded database to develop a mobile/desktop app. Besides, Spatialite can be used to develop a web app.
PostGIS is a better option if you need ‘writing/reading speed’, several concurrent users in the database, clustering, or network access.

How to create a spatial database

1. Open QGIS and the exploration panel. If the panel Browse is missing, click View | Panels , and click Browse . In this panel you will find the access to SpatiaLite

entrée spatialite dans le panneau Parcourir de qgis 2.8

2.Create a new database Spatiality by clicking SpatiaLite and selecting
Create a database …
3. Select the directory of your choice and name the new database, for example BaseTest. It will appear under the rubric SpatiaLite

créer une nouvelle base de données spatialite

Structure of a SpatiaLite database

Now that we have a new database SpatiaLite , we will analyze its primary structure and content .
For this task, we will use the Database Manager, an integrated QGIS plugin. DB Manager offers a simple graphical interface to manage the databases PostGIS and SpatiaLite We will be able to view and manage our database SpatiaLite by using the database manager. We will start by getting familiar with the interface of the database manager.
1. To open, click Database Manager in the Database menu. The DB management interface (as shown in the next screenshot) has four different sections: menu bar, toolbar, tree and panel information.

gestionnaire de base de données de qgis 2.8
2. Click SpatiaLite -> BaseTest to see the tree with all the tables, views and General Info in the database we have just created.
structure d'une base spatialite
When a new SpatiaLite database is created, it is automatically loaded with several tables and processed data. These tables and data include documents used by the DBMS to manage the structure and operation of the database. You should not change or remove these tables and/or data, unless you are completely aware of what you are doing.

How to import data into SpatiaLite

It is very easy to import data into SpatiaLite database if using the QGis database manager..
You can import directly the following formats:
• Shapefile (.shp)
• Dbase (.dbf)
• Text (.txt), Commas Separate Values (.csv), et feuilles Excel (.xls)
• Well-known Text (.wkt) et Well-known Binary (.wkb)
• PostGIS (.ewkt / .ewkb)
• Geography Markup Language (.gml)
• Keyhole Markup Language (.kml)
• Geometry JavaScript Object Notation (.geojson)
• Scalable Vector Graphics (.svg)

Let’s see how to import a shapefile: 1. Open the database manager by clicking Database Manager in the Database menu. Open SpatiaLite and select BaseTest.sqlite in the tree .
2. Go to the menu Table/Import a layer or a file to open the dialog Import a vector layer, as shown in the following screenshot.
fenêtre importer une couche vecteur du gestionnaire de base de données qgis
3. Highlight the shapefile you want to load.
4. Click Upgrade options to load the rest of the dialogue box. The name of the output table will be filled with the name of the input shapefile.
5. Set up the following options:
° ° Select SCR Source and enter the EPSG code for the input data..If you do not want to change the coordinates system when proceeding to the import, it is useless to define the SCR target, alternatively, you can enter the desired coordinate system for the data in the database.
° ° Select Create a spatial index.
fenêtre d'import de données rensignée
6. Click OK to import the file.
Once the table is uploadade, you can display it directly in the cartographic window by double-clicking the table’s name.
affichage de la table spatialite dans qgis
In the following article we will discuss how to upload a non spatial table in the SpatiaLite database, how to create a table using all the different documents and how to create and manage pictures.

Si cet article vous a intéressé et que vous pensez qu'il pourrait bénéficier à d'autres personnes, n'hésitez pas à le partager sur vos réseaux sociaux en utilisant les boutons ci-dessous. Votre partage est apprécié !

6 thoughts on “How to create a SpatiaLite database with QGis

  1. look forward letting me know how to proceed adding rasters underlaying polygones i’ve done.

    You are vere skilled in your pedagogical performances. Just what I need.

    MVH

    Jan-Anders Aronson

  2. Hi, Your tutorials are always quite clear and straight to the point. Unfortunately, I am new to QGIS and would like to create a geodatabase that can have feature datasets and classes. I have quite a lot of feature classes and I need to group them according to themes and sub-themes. I am also hoping to share this database with many of my colleagues to be able to download and hopeful edit the data. Which Geo-database do you recommend I should use QGIS similar to a file geodatabase in ArcMap? I

    1. The simplest solution is to use the geopackage format (which is a version of Spatialite). But it is recommended for small databases. To have the best results, widely comparable with the ArcGis geodatabase, I advise you to use a Postgresql/Postgis database. Moreover, the ArcGis geodatabase enterprise can be a Postgresql database.

  3. This great information I needed. I will try the postgresql or postgis database. Do you have a post where you explained how to do either one in your blog? Or any source that has clear instructions to them?

Leave a Reply to JT Cancel reply

Your email address will not be published. Required fields are marked *