The SpatiaLite database management is very simple. The QGis database manager provides functions to create, rename, edit, delete and empty tables using tools available in the Manager Table menu. In this article we will discuss in detail each one of these tools .
How to create a SpatiaLite table
It is quite easy to create new tables using the database manager. When you create a new table, you can specify whether it will be a spatial or a non-spatial table .
We will create a new spatial table with SpatiaLite to store data on bathymetry sensor.We can follow two different strategies :
- To create, quickly, a new layer of SpatiaLite (and possibly a database) directly from the QGis window, click Layer | Create a layer | New layer SpatiaLite … or use the keyboard shortcut Ctrl + Shift + A
- From the database manager:
1. Open the database manager by clicking Database Manager at Database. Use SpatiaLite and select the database in which you want to create a new layer .
2. Click Table | Create a table to open the window Create a table .
3. Enter the table name ( here Probes ).
4. Click the Add a field button to add a new field of the table. A new line will appear in the list of fields. Set the name field and type for each attribute desired .
5. Set the key primary field. The names of this field have to be unique for each recording .
6. Select Create a geometric columnand then choose the following options :
- Geometry type : POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTYPOLYGON,
GEOMETRY COLLECTION - The name of the geometric field
- The number of Dimensions: 2 if XY, 3 if XYZ
- The SCR (SRID) that is the EPSG code of the coordinate system
7. Select Create a spatial index to create an index for the table.
8. your dialogue must look like the following screenshot. If this is the case,click the Create button to create the new table.
9. If the table is successfully created, a message confirms that everything is ok. Close the dialog box , and then click on the Close button to close the window Create a table.
10. To display the new table, you have to update the tree by selecting the SpatiaLite database in the tree then click Refresh at Database or press the key F5 on your keyboard. Note that the created table will have the icon corresponding to the type of layer (point, line, …) which indicates that it is a spatial table with a geometry .
How to rename a table
To rename a table, proceed as follows:
1. Open the database manager. In the tree , expand the tree and select the database containing the table you want to rename .
2. In tree , select the table you want to edit. Make a right click on the table and choose Rename in the context menu to proceed .
How to edit the properties of a table
To edit the properties of a table, perform the following operations:
1. Open the database manager by clicking Database Manager at Database. In the tree , expand the tree and select the database containing the table you want to edit.
2. In the tree , select the table you want to edit. Click Table | Edit Table to open the Table Properties window.
3. The properties window of the table ( shown in the following screenshot ) has three tabs , indexes, columns and constraints – which allow the modification of certain properties of the table
The tabs and the grey buttons correspond to operations not supported by SpatiaLite . This does not mean that you cannot use them, but you will need another tool . You can download and install, for example , DB Browser for SQLite , an OpenSource tool that will allow you a more complete management of your SpatiaLite database.
The tab columns lists all the fields, their type, whether null values are authorized , and their values by default. At the fields list, there are four buttons. The button add a column opens a window and allows you to create a new field and specify its properties . The button add a geometry column opens a window and allows you to create a new geometry field and specify its properties . The edit and remove buttons are disabled .
SpatiaLite does not support the table modification commands, such as edit and delete existing fields . To that end you can use the DBBrowser for SQLite.
The tab constraints lists all the table constraints; their name,their type and the columns that are affected by the constraints .
SpatiaLite does not support additions or deletions of a constraint in an existing table; therefore, these options are deactivated. In this case you can use DB Browser for SQLite.
The tab Index lists all the indexes on the table, their name and the columns that are part of the index . The add an index button opens a window that allows you to create anew index by selecting the index field and providing an index name . The Adda spatial index button adds a spatial index for the table. This option is not available only if the table includes a geometry field. The Delete index button removes the selected index .
How to delete a table
There are two ways to delete a table in a database within QGIS: by using the BROWSE panel in QGIS or the database manager.
To delete a table using the Browse panel in QGIS Desktop, expand the database where you want to remove a table and then make a right click on the table and choose Delete the layer.
To delete a table using DB Manager, open the database manager. In the tree , expand the database containing the table you want to delete . Make aright click on the table in the tree and choose Wipe off in the context menu .
How to clear a table
To delete all records of a table without deleting the table, open the database manager. In the tree , expand the tree and select the database containing the table you want to clear . In the tree , select the table you want to clear. Click then on Table -> Clear the table.
