Blog GIS & Territories

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

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

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.

2. Click SpatiaLite -> BaseTest to see the tree with all the tables, views and General Info in the database we have just created.

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.

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.

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.

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é !

Atilio Francois