{"id":4866,"date":"2018-02-05T09:34:40","date_gmt":"2018-02-05T08:34:40","guid":{"rendered":"http:\/\/www.sigterritoires.fr\/?p=4866"},"modified":"2018-02-27T19:54:33","modified_gmt":"2018-02-27T18:54:33","slug":"how-to-create-a-spatialite-database-with-qgis","status":"publish","type":"post","link":"https:\/\/www.sigterritoires.fr\/index.php\/en\/how-to-create-a-spatialite-database-with-qgis\/","title":{"rendered":"How to create a SpatiaLite database with QGis"},"content":{"rendered":"

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<\/a>. There is a big gap, and the \u2018jump\u2019 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\u2026.<\/p>\n

Comparison between SpatiaLite and PostGis<\/h2>\n
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)\u00a0 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.<\/p>\n
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<\/a>.
\nPlease 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.
\nThe 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.
\nPostGIS is a better option if you need \u2018writing\/reading speed\u2019, several concurrent users in the database, clustering, or network access.<\/p>\n

How to create a spatial database<\/h2>\n
1. Open QGIS and the exploration panel. If the panel Browse<\/strong> <\/em>is missing, click View | Panels<\/strong><\/em> , and click Browse<\/strong> <\/em>. In this panel you will find the access to SpatiaLite<\/strong><\/em><\/p>\n
$\"entr\u00e9e$ <\/a><\/p>\n
2.Create a new database Spatiality by clicking SpatiaLite<\/strong> <\/em>and selecting
\nCreate a database …<\/strong><\/em>
\n3. Select the directory of your choice and name the new database, for example BaseTest<\/em>. It will appear under the rubric SpatiaLite<\/em><\/strong><\/p>\n
$\"cr\u00e9er$ <\/a><\/p>\n

Structure of a SpatiaLite database<\/h2>\n
Now that we have a new database SpatiaLite<\/strong> <\/em>, we will analyze its primary structure and content .
\nFor this task, we will use the Database Manager<\/strong><\/em>, 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.
\n1. To open, click Database Manager<\/strong><\/em> in the Database<\/strong> <\/em>menu. The DB management interface (as shown in the next screenshot) has four different sections: menu bar, toolbar, tree and panel information.<\/p>\n
$\"gestionnaire$ <\/a>
\n2. Click SpatiaLite -> BaseTest<\/strong><\/em> to see the tree with all the tables, views and General Info<\/em> in the database we have just created.
\n $\"structure$ <\/a>
\nWhen 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.<\/p>\n
How to import data into SpatiaLite<\/h2>\n
It is very easy to import data into SpatiaLite database if using the QGis database manager..
\nYou can import directly the following formats:
\n\u2022 Shapefile<\/a> (.shp)
\n\u2022 Dbase (.dbf)
\n\u2022 Text (.txt), Commas Separate Values (.csv), et feuilles Excel (.xls)
\n\u2022 Well-known Text (.wkt) et Well-known Binary (.wkb)
\n\u2022 PostGIS (.ewkt \/ .ewkb)
\n\u2022 Geography Markup Language (.gml)
\n\u2022 Keyhole Markup Language (.kml)
\n\u2022 Geometry JavaScript Object Notation (.geojson)
\n\u2022 Scalable Vector Graphics (.svg)<\/p>\n
Let\u2019s see how to import a shapefile: 1. Open the database manager by clicking Database Manager<\/strong><\/em> in the Database<\/strong> <\/em>menu. Open SpatiaLite<\/strong><\/em> and select BaseTest.sqlite<\/strong><\/em> in the tree .
\n2. Go to the menu Table\/Import a layer or a file<\/strong><\/em> to open the dialog Import a vector layer,<\/strong><\/em> as shown in the following screenshot.
\n $\"fen\u00eatre$ <\/a>
\n3. Highlight the shapefile you want to load.
\n4. Click Upgrade options<\/strong><\/em> to load the rest of the dialogue box. The name of the output table will be filled with the name of the input shapefile.
\n5. Set up the following options:
\n\u00b0 \u00b0 Select SCR Source<\/strong><\/em> 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.
\n\u00b0 \u00b0 Select Create a spatial index<\/strong><\/em>.
\n $\"fen\u00eatre$ <\/a>
\n6. Click OK<\/strong> <\/em>to import the file.
\nOnce the table is uploadade, you can display it directly in the cartographic window by double-clicking the table\u2019s name.
\n $\"affichage$ <\/a>
\nIn 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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"give_campaign_id":0,"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"sfsi_plus_gutenberg_text_before_share":"","sfsi_plus_gutenberg_show_text_before_share":"","sfsi_plus_gutenberg_icon_type":"","sfsi_plus_gutenberg_icon_alignemt":"","sfsi_plus_gutenburg_max_per_row":"","_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[1379,1377,1381],"tags":[1384,1386,1388],"class_list":["post-4866","post","type-post","status-publish","format-standard","hentry","category-database","category-qgis-en","category-spatialite","tag-db-manager","tag-qgis-en","tag-spatialite-en"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p6XU0A-1gu","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts\/4866","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/comments?post=4866"}],"version-history":[{"count":0,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts\/4866\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/media?parent=4866"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/categories?post=4866"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/tags?post=4866"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}