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 : <\/p>\n\n\n\n\n\n\n\n
- To create, quickly, a new layer of SpatiaLite (and possibly a database) directly from the QGis window, click Layer |<\/em><\/strong> Create<\/em><\/strong> a<\/em><\/strong> layer<\/em><\/strong> |<\/em><\/strong> New<\/em><\/strong> layer<\/em><\/strong> SpatiaLite<\/em><\/strong> …<\/em><\/strong> or use the keyboard shortcut Ctrl + Shift + A<\/em><\/strong>
<\/li><\/ul>\n\n\n\n![\"\"](\"https:\/\/i0.wp.com\/www.sigterritoires.fr\/wp-content\/uploads\/2018\/12\/111.png?resize=525%2C142&ssl=1\")
<\/a><\/figure>\n\n\n\n- From the database manager: <\/li><\/ul>\n\n\n\n
1. Open the database manager by clicking Database<\/em><\/strong> Manager\u00a0<\/em><\/strong>at Database.<\/em><\/strong> Use SpatiaLite<\/em><\/strong> and select the database in which you want to create a new layer .
2. Click Table |<\/em><\/strong> Create<\/em><\/strong> a<\/em><\/strong> table\u00a0<\/em><\/strong>to open the window Create<\/em><\/strong> a<\/em><\/strong> table<\/em><\/strong> .
3. Enter the table name ( here Probes ).
4. Click the Add<\/em><\/strong> a<\/em><\/strong> field<\/em><\/strong> button to add a new field of the table. A new line will appear in the list of fields. Set the name<\/em><\/strong> field and type<\/em> for each attribute desired .
5. Set the key primary field. \u00a0The names of this field have to be unique for each recording .
6.\u00a0 Select Create<\/em><\/strong> a<\/em><\/strong> geometric column<\/em><\/strong>and then choose the following options : <\/p>\n\n\n\n- Geometry type : POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTYPOLYGON,\nGEOMETRY COLLECTION <\/li>
- The name of the geometric field <\/li>
- The number of Dimensions: 2 if XY, 3 if XYZ <\/li>
- The SCR (SRID) that is the EPSG code of the coordinate system <\/li><\/ul>\n\n\n\n
![\"\"](\"https:\/\/i0.wp.com\/www.sigterritoires.fr\/wp-content\/uploads\/2018\/12\/112.png?resize=492%2C601&ssl=1\")
<\/a><\/figure>\n\n\n\n7. Select Create<\/em><\/strong> a<\/em><\/strong> spatial index\u00a0<\/em><\/strong>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. \u00a0Close the dialog box , and then click on the Close<\/em><\/strong> button to close the window Create<\/em><\/strong> a\u00a0<\/em><\/strong>table.<\/em><\/strong>
10. To display the new table, you have to update the tree by selecting the SpatiaLite database in the tree then click Refresh<\/em><\/strong> at Database\u00a0\u00a0<\/em><\/strong>or press the key F5<\/em><\/strong> 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 . \u00a0
<\/p>\n\n\n\n![\"\"](\"https:\/\/i0.wp.com\/www.sigterritoires.fr\/wp-content\/uploads\/2018\/12\/113.png?resize=525%2C505&ssl=1\")
<\/a><\/figure>\n\n\n\nHow to rename<\/strong> a<\/strong> table<\/strong> <\/p>\n\n\n\n
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 . <\/p>\n\n\n\nHow to edit the properties<\/strong> of a<\/strong> table<\/strong> <\/p>\n\n\n\n
To edit the properties of a table, perform the following operations:
1. Open the database manager by clicking Database<\/em><\/strong> Manager<\/em><\/strong> at Database.<\/em><\/strong> 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<\/em><\/strong> |<\/em><\/strong> Edit<\/em><\/strong> Table<\/em><\/strong> to open the Table Properties<\/em><\/strong> 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
<\/p>\n\n\n\n![\"\"](\"https:\/\/i0.wp.com\/www.sigterritoires.fr\/wp-content\/uploads\/2018\/12\/114.png?resize=525%2C439&ssl=1\")
<\/a><\/figure>\n\n\n\nThe 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<\/a> , an OpenSource tool that will allow you a more complete management of your SpatiaLite database. <\/p>\n\n\n\n
The tab columns<\/em><\/strong> 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<\/em><\/strong> a <\/em><\/strong>column<\/em><\/strong> opens a window and allows you to create a new field and specify its properties . The button add<\/em><\/strong> a<\/em><\/strong> geometry column<\/em><\/strong> 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<\/em><\/strong> 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.
<\/p>\n\n\n\n![\"\"](\"https:\/\/i0.wp.com\/www.sigterritoires.fr\/wp-content\/uploads\/2018\/12\/115.png?resize=525%2C435&ssl=1\")
<\/a><\/figure>\n\n\n\n
The tab Index<\/em><\/strong> lists all the indexes on the table, their name and the columns that are part of the index . The add<\/em><\/strong> an <\/em><\/strong>index<\/em><\/strong> button opens a window that allows you to create anew index by selecting the index field and providing an index name . The Add<\/em><\/strong>a<\/em><\/strong> spatial index<\/em><\/strong> button adds a spatial index for the table. This option is not available only if the table includes a geometry field. The Delete<\/em><\/strong> index<\/em><\/strong> button removes the selected index . <\/p>\n\n\n\nHow to delete<\/strong> a<\/strong> table<\/strong> <\/p>\n\n\n\n
There are two ways to delete a table in a database within QGIS: by using the BROWSE<\/em><\/strong> panel in QGIS or the database manager.
To delete a table using the Browse<\/em><\/strong> 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<\/em><\/strong> the<\/em><\/strong> layer<\/em><\/strong>.
To delete a table using DB Manager,<\/em><\/strong> 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<\/em><\/strong> in the context menu . <\/p>\n\n\n\nHow to clear<\/strong> a<\/strong> table<\/strong> <\/p>\n\n\n\n
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 -><\/em><\/strong> Clear the table.<\/em><\/strong> <\/p>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_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_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[1260],"tags":[],"class_list":["post-7550","post","type-post","status-publish","format-standard","hentry","category-non-classe-en"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t
- From the database manager: <\/li><\/ul>\n\n\n\n
![\"\"](\"https:\/\/i2.wp.com\/www.sigterritoires.fr\/wp-content\/uploads\/2015\/05\/q40.png\")
<\/a><\/figure>\n\n\n\n