Ales Nordeen informed me about the availability of a set of tutorials on SQLite. The tutorial covers the basics of SQLite (introduction, installation guide, database and table operations). It also introduces data types, keys, clauses, queries, and joins, SQLite Index, Trigger, View, and Functions.<\/p>\n\n\n\n
[\/ stextbox] <\/p>\n\n\n\n
Database fundamentals <\/strong><\/p>\n\n\n\n A database is a structured set of data. Databases\noffer several advantages over data stored in a simple file format, such as\nshapefile<\/a> or KML. Benefits include complex\nqueries, complex relationships, scalability, security, and data integrity, to\nname a few. Database tables<\/strong>\n<\/p>\n\n\n\n A relational database stores the data in tables.\nA table is made up of rows and columns, where each row is a\nsingle data record and each column stores a field value associated with each\nrecord. A table can contain any number of records. However,\neach field is uniquely named and stores a specific data type. <\/p>\n\n\n\n A data type restricts the information that can be stored in a\nfield, and it is very important that the appropriate data type and its\nassociated parameters be correctly chosen for each field in a table.\nThe common data types are: Relationships between tables<\/strong>\n<\/p>\n\n\n\n A given relationship between tables connects the records of\nthese tables. The advantage of linking tables\nis to reduce data redundancy and increase data integrity. In\norder to link two tables, each table must contain an indexed key field.\n Structured query language\n(SQL)<\/strong> <\/p>\n\n\n\n Structured Query Language (SQL) is a language designed to\nmanage databases as well as the data they contain. Addressing\nSQL exhaustively is a task beyond the scope of this tutorial. We\nwill resume here some basic elements of the language that we will use in this\ntutorial. <\/p>\n\n\n\n Note: A complete list of SQLite SQL commands is available\nat<\/em> http:\/\/www.sqlite.org\/lang.html<\/em><\/a>\n.<\/em> A complete list of SQL commands for the SpatialLite\nextension is available at<\/em> http:\/\/www.gaia-gis.it\/gaia-sins\/spatialite-sql-4.2.0.html<\/em><\/a>\n.<\/em> <\/p>\n\n\n\n SQL provides functions to select, insert, delete, and update\ndata. Four frequently used functions of SQL data are:\n WHERE <operator> <value>;<\/strong> <\/p>\n\n\n\n where <field (s)><\/strong> is the name of the field whose\nvalues \u200b\u200bare to be retrieved and <table><\/strong> is the table on which the\nquery is to be executed. The <operator> part<\/strong>\nchecks the condition (such as =,> =, like) and <value><\/strong> is the\nvalue to compare to the field. <\/p>\n\n\n\n \u2022 INSERT: This function inserts new records into a table.\nfor example <\/p>\n\n\n\n INSERT INTO <table> (<field1>, <field2>,\n<field3>) VALUES (<value1>, <value2>, inserts three values \u200b\u200bin their respective domains, where <value1><\/strong>\n, <value2><\/strong> , and <value3><\/strong> are stored in\n <field1><\/strong> , <field2><\/strong>\n, and <field3><\/strong> of <table><\/strong> . <\/p>\n\n\n\n \u2022 UPDATE: This function modifies an existing record in a\ntable. for example <\/p>\n\n\n\n UPDATE <table><\/strong> This statement updates the value of a field, where <value><\/strong>\nis stored in <field><\/strong> of <table><\/strong>\n. <\/p>\n\n\n\n \u2022 DELETE: Deletes records from a table. For\nexample, the following statement deletes all records that satisfy the WHERE\nclause: <\/p>\n\n\n\n DELETE FROM <table> WHERE<\/strong> where <table><\/strong> is the table where the records\nmust be deleted, <field><\/strong> is the name of the field, <operator><\/strong>\nchecks the equality, and <value><\/strong> is the value to check for the\nfield. <\/p>\n\n\n\n Another SQL function of interest is VIEW<\/strong> .\nA view is a stored query that looks like a table but is\nactually dynamically built when called to view. To\ncreate a view, we simply precede a SELECT statement<\/strong> with CREATE VIEW\nAS <view_name><\/strong> and this will create a view named <view_name><\/strong>\n . You can then process the new\nview as if it were a table. <\/p>\n\n\n\n We will now turn to the following points: <\/p>\n\n\n\n Chapter\n1: Creating a spatial database Spatialite<\/a> – Creating a database, Importing\ndata from a shapefile <\/p>\n\n\n\n Chapter\n2: Import Tabular (Non-Spatial) Data into a Database \nspatial\n– Import an Excel table, create geometry from XY fields, export data from\nSpatiaLite to shapefile. <\/p>\n\n\n\n
\nThere\nare several types of databases. However, the most common type of\ndatabase is the Relational Database. In\nthis tutorial we\u2019ll refer to this type. <\/p>\n\n\n\n
\n\u2022\nInteger
\n\u2022\nVariable \/ real \/ decimal
\n\u2022\nText
\n\u2022\nDate
\nEach\nof these data types can have a set of additional constraints, such as setting a\ndefault value, limiting the size of the field, or prohibiting null values.\n
\nIn\naddition to the common data types that were mentioned earlier, some databases\nsupport the \u00ab\u00a0geometry\u00a0\u00bb field type, allowing the following types of\ngeometry to be stored:
\n\u2022\nPoint
\n\u2022\nMulti-point
\n\u2022\nLine
\n\u2022\nMulti-line
\n\u2022\nPolygon
\n\u2022\nMulti-polygon
\nThe\nmultipoint \/ line \/ polygon types store multipart geometries: for a single\nrecord of the table, several geometry parts are associated with it.\n
\nShapefiles\nstore geospatial data in multi-type geometry. Using\nthis type of geometry is a good practice if you plan to convert to or from this\nformat <\/p>\n\n\n\n
\nOrganizing\ntables to reduce redundancy is called normalization. Usually,\nnormalization involves splitting large tables into smaller and, therefore, less\nredundant tables, followed by defining relationships between tables.\n
\nA\nfield can be defined as an index. A field defined as an index must\ncontain only unique values \u200b\u200bfor each record, and therefore, it can be used to\nuniquely identify each record in a table. An\nindex is useful for two reasons. First, it allows records to be\nquickly found during a query (if the indexed field is part of the query).\nSecond, an index can be defined to be a primary key for a\ntable, making it possible to build relationships between tables.
\nA\nprimary key consists of one or more fields that identify a record within a\ntable.
\nAn\nexternal key consists of one or more fields that identify a record in another table.\n
\nWhen\na relationship is created, a record in one table is linked to a record in\nanother table. With related tables, more\ncomplex queries can be executed and redundancy in the database can be reduced.\n<\/p>\n\n\n\n
\n\u2022\nSELECT: This function retrieves a dataset of one or more tables based on an\nexpression. A query is basically a structure\nof type
\nSELECT <field (s)> FROM <table><\/strong>\n<\/p>\n\n\n\n
\n<value3>);\n<\/p>\n\n\n\n
\nSET <field> = <value>;<\/strong> <\/p>\n\n\n\n
\n<field> <operator> <value>;<\/strong>\n<\/p>\n\n\n\n