A quick tutorial to SpatiaLite - a Spatial extension for SQLite

Table of Contents

• 1. Preparing to start
  • 1.1. What you need to begin
  • 1.2. The test.db database
  • 1.3. Getting started with SQL and SQLite
• 2. Getting started with SpatiaLite
  • 2.1. How to load SpatiaLite
  • 2.2. A first glance: familiarizing with GEOMETRY
  • 2.3. GEOMETRY classes
  • 2.4. GEOMETRY ENVELOPE
• 3. Managing a GIS databases
  • 3.1. Exporting GIS data outside a SpatiaLite db
  • 3.2. Creating a new SpatiaLite db and populating it
  • 3.3 Inserting, updating and deleting rows in a SpatiaLite db
• 4. Performing some Spatial data analysis
  • 4.1. Evaluating MBRs relationships
• 5. Managing Coordinate Reference Systems and Coordinate Transformation
• 6. Performing SQL queries directly on shapefiles

1. Preparing to start

1.1. What you need to begin

1. In order to start using SpatiaLite you must have the SpatiaLite.dll somewhere on your local hard disk. You surely have received one copy of it within the download.
2. SpatiaLite is simply an extension; you absolutely need to have the SQLite front end slite3.exe as well, and sqlite3.dll too, in order to use SpatiaLite. Note that you must have a recent version, with a version number 3.3.6 or higher.
   If not, please download them at www.sqlite.org
3. Neither sqlite3.exe nor SpatiaLite.dll requires an installation.
   You simply have to create an empty folder (e.g. C:\SpatiaLite) and just copy the files inside; put the test.db sample database in the same folder.
4. If you don't have any GIS tool already installed, may be a good idea to install the open source QuantumGis; it can be useful in order to display at a glance the shapefiles we are going to produce among this tutorial.
   You can download it from www.qgis.org; it has an ordinary Windows Installer that creates a Desktop icon and so on.

1.2. The test.db database

1. the regions table contains 109 rows; each entity has a geometry, that is a POLYGON.
2. the towns table contains 8101 rows; each entity has a geometry, that is a POINT.
3. the highways table contains 775 rows, ; each entity has a geometry, that is a LINESTRING.

1.3. Getting started with SQL and SQLite

1. the SELECT statement requests SQLite to perform a query
2. fetching all columns [*]
3. FROM the database table of name towns
4. retrieving only the first five rows [LIMIT 5]
5. note the any SQL statement must end with a semicolon [;]

1. in SQL constructs using lower- or upper-case has identical effects; SELECT is the same of select, FROM and from are equivalents.
2. you can freely choose which one columns has to be fetched, determine at your will their order, and even rename then if you want to do so by using the AS clause.
3. you can order the fetched rows by using the ORDER BY clause.

1. you can filter a specific set of rows by imposing a WHERE clause; only those rows that satisfies the logical expression you specify will be fetched.
   In this example only Towns with a population greater than 350000 peoples has been fetched.
2. you can order rows in descending order if appropriate, by using the DESC clause.

1. you can split complex queries along many lines; SQLite starts executing the query only where it finds a line closed by a semicolon [;] In other words, until you enter a [;] as the last character in a line, you are always continuing to type the same query, and SQLite waits until you have finished to complete the query.
2. you can use functions in an SQL query.
   COUNT(), MIN(), MAX() and SUM() are functions. Not at all surprisingly:
   • COUNT(() returns the total number of rows.
   • MIN() returns the minimum value for the given column.
   • MAX() returns the maximum value for the given column.
   • SUM() returns the total of all values for the given column.
3. even more, you can docalculations in your query.
   e.g. we have calculated the mean of peoples per village dividing the SUM() by the COUNT() values.

1. the (10 - 11) * 2 term is an example of expression
2. the ABS() functions returns the absolute value of a number.
3. note that in this example we have not used any DB column or DB table at all.

1. the HEX() function returns the hexadecimal representation of a BLOB column value.
2. in the preceding execution of this query, the geom column seemed empty; now, by using the HEX() function, we discover that contains lots of strange binary data.
   
3. geom contains GEOMETRY values, stored as BLOBs and encoded in the internal representation used by SpatiaLite.
   SQLite in its own hasn't the slightest idea of what GEOMETRY is, and cannot do any other operation on it.
   To really use GEOMETRY values, it's time to load the SpatiaLite extension.

2. Getting started with SpatiaLite

2.1 How to load SpatiaLite

2.2 A first glance: familiarizing with GEOMETRY

1. the AsText() function is a SpatiaLite one, and returns the Well Known Text - WKT representation for a GEOMETRY column value.
   WKT is a standard notation conformant to OpenGIS specification.
2. in the preceding execution of this query, the HEX() function returned lots of strange binary data.
   Now the AsText() function shows useful and quite easily understandable GEOMETRY values.
   
3. a POINT is the simplest GEOMETRY class, and has only a couple of [X,Y] coordinates.

1. the SpatiaLite X() function returns the X coordinate for a POINT.
2. the Y() function returns the Y coordinate for a POINT.

1. the SpatiaLite GeomFromText() function returns the internal BLOB representation for a GEOMETRY.
2. the AsBinary() function returns the Well Known Binary - WKB representation for a GEOMETRY column value.
   WKB is a standard notation conformant to OpenGIS specification.
3. the GeomFromWKB() function converts a WKB value into the corresponding internal BLOB value.

2.3. GEOMETRY classes

1. LINESTRING is another GEOMETRY class, and has lots of POINTs.
2. in this one case you have fetched a very simple LINESTRING, representing a polyline with just 4 vertices.
3. it isn't at all unusual to encounter LINESTRINGs with some thousands of vertices in real GIS data.

1. the SpatiaLite NumPoints() function returns the number of vertices for a LINESTRING GEOMETRY.
2. the GLength() function returns the geometric length [expressed in map units] for a LINESTRING GEOMETRY.
3. the Dimension() function returns the dimensions' number for any GEOMETRY class [obviously 1 for lines].
4. the GeometryType() function returns the class type for any kind of GEOMETRY value.

1. the SpatiaLite StartPoint() function returns the first POINT for a LINESTRING GEOMETRY.
2. the EndPoint() function returns the last POINT for a LINESTRING GEOMETRY.
3. the PointN() function returns the selected vertex as a POINT; each one vertex is identified by a relative index.
   The first vertex is identified by an index value 1, the second by an index value 2 and so on.
4. please note that you can freely nest the various SpatiaLite functions, by passing the return value of the inner function as an argument for the outer one.
   

1. POLYGON is another GEOMETRY class.
2. in this one case you have fetched a very simple POLYGON, having only the exterior ring [i.e. it doesn't contains any internal hole].
   Remember that POLYGONs may optionally contain an arbitrary number of internal holes, each one delimited by an interior ring.
   
3. the exterior ring in itself is simply a LINESTRING [and interior rings too are LINESTRINGS].
4. note that a POLYGON is a closed geometry, and thus the first and the last POINT for each ring are exactly identical.

1. we have already meet the SpatiaLite Dimension() and GeometryType() functions; they works for POLYGONs exactly in same fashion as for any other kind of GEOMETRY.
2. the SpatiaLite Area() function returns the geometric area [expressed in square map units] for a POLYGON GEOMETRY.
3. the Centroid() function returns the POINT identifying the centroid for a POLYGON GEOMETRY.

1. the SpatiaLite ExteriorRing() functions returns the exterior ring for a given GEOMETRY.
   Any valid POLYGON must have an exterior ring.
   Remember: each one of the rings belonging to a POLYGON is a closed LINESTRING
2. the SpatiaLite NumInteriorRings() function returns the number of interior rings belonging to a POLYGON.
   A valid POLYGON may have any number of interior rings, including zero i.e. no interior ring at all.
3. The SpatiaLite InteriorRingN() function returns the selected interior rings as a LINESTRING; each one interior ring is identified by a relative index.
   The first interior ring is identified by an index value 1, the second by an index value 2 and so on.
4. Any ring is a LINESTRING, so we can use the NumPoints() function in order to detect the number of related vertices.
   If we call the NumPoints() function on a NULL GEOMETRY [or on a GEOMETRY of non-LINESTRING class] we'll get a NULL result.
   This explains why the the last three rows has a NULL NumPoints() result; there is no corresponding interior ring !

1. we have already met in the preceding ones the usage of nested functions.
   For POLYGONs it becomes to be a little more tedious, but still easily understandable.
2. e.g. to obtain the last column we have used InteriorRingN() in order to get the first interior ring, and then PointN() to get the fifth vertex.
   At last we can call Y() to get the coordinate value.
3. it's quite boring, but not difficult at all, no ?

1. a MULTIPOINT is a collection of two or more POINTSs belonging to the same entity.
2. a MULTILINESTRING is a collection of two or more LINESTRINGs.
3. a MULTIPOLYGON is a collection of two or more POLYGONs.
4. a GEOMETRYCOLLECTION is an arbitrary collection containing any other kind of geometries.

1. the SpatiaLite NumGeometries() function returns the number of elements for a collection.
2. the GeometryN() function returns the N-th element for a collection.
3. the GLength() function applied to a MULTILINESTRING returns the sum of individual lengths for each LINESTRING composing the collection.
4. the Area() function applied to a MULTIPOLYGON returns the sum of individual areas for each POLYGON composing the collection.
5. the Centroid() function in the SpatiaLite implementation always returns the centroid related to the POLYGON composing the collection which has the largest individual Area().

2.4. GEOMETRY ENVELOPE

1. the SpatiaLite Envelope() function always returns a POLYGON that is the Minimun Bounding Rectangle - MBR for the given GEOMETRY.
   Because an MBR is a rectangle, it always has 5 POINTs [remember: in closed geometries the last POINT must be identical to the first one].
2. individual POINTs are as follow:
   • POINT #1: minX,minY
   • POINT #2: maxX,minY
   • POINT #3: maxX,maxY
   • POINT #4: minX,maxY
   • POINT #5: minX,minY
3. MBRs are of peculiar interest, because by using them you can evaluate spatial relationships between two geometries in a simplified and roughly approximative way. But MBRs comparisons are very fast to compute, so they are very useful and widely used to speed up data processing.

3. Managing a GIS databases

1. how can you import any existing GIS dataset in a new SpatiaLite database.
2. how can you insert, modify or delete the GIS datasets once they are stored inside the database you have just created.
3. how can you export GIS datasets out of the SpatiaLite database.

1. an SQLite database is just an ordinary file; you can freely copy it, or even upload and download it on the WEB, send it as an e-mail attachment, zip and unzip it, and so on.
2. SQLite's database are cross-platform, so you can produce them on a Windows PC and then use them on a Linux server, without any problem.
   Of course, SpatiaLite too guarantees that Spatial data didn't corrupts when going cross-platform.

3.1 Exporting GIS data outside a SpatiaLite db

1. the SpatiaLite ExportShapefile() function exports a whole table as a shapefile.
2. argument #1 identifies the table name you want to export.
3. argument #2 specifies which one GEOMETRY CLASS you intend to export.
4. argument #3 is the pathname of the shapefile to be generated.
5. argument #4 is the column name that identifies geometries to be exported.

1. lines starting with a double hyphen [--] are comments, and are not executed.
2. other lines are exactly the same you have just manually typed in the last exercise.
3. DumpShapefile() is just another alias name that identifies the ExportShapefile() function.

3.2 Creating a new SpatiaLite db and populating it

1. the SpatiaLite LoadShapefile() function creates a new table and populate she by importing a whole shapefile.
   • argument #1 is the pathname of the shapefile to be imported.
   • argument #2 identifies the table name you want to create.
2. the ANALYZE and VACUUM SQL commands tells SQLite to reorganize and restructure the database.
   It's always a very good idea to perform them each time you insert or delete a large amount of data, in order to maintain your db in a well performing state.

1. please note that the GaiaPrimaryKey and LocalCouncil original column names have been truncated.
   Unfortunately, shapefile format didn't allow column names to be longer than 10 character.
2. the primary key column now has a PK_UID name, and the geometry column now has a geom name.
   
3. Those are the default column names SpatiaLite assumes.
   You can alter this behaviour, of course, but you need to specify some extra parameter to the LoadShapefile() function.

3.3 Inserting, updating and deleting rows in a SpatiaLite db

1. the BEGIN SQL statement tells SQLite that you intend to start a transaction.
   Any operation that you perform within a transaction can be cancelled at any subsequent time, and the db will come back to its original unchanged state.
2. the ROLLBACK SQL statement tells SQLite exactly this.

1. the COMMIT SQL statement definitively confirms all operations still pending.
2. both ROLLBACK and COMMIT close the current transaction; if you want to continue working in transactional way, you should start a new transaction invoking BEGIN once again.
3. if you simply omit to use at all the BEGIN, COMMIT or ROLLBACK statements, SQLite will assume you wish to operate in the so-called autocommit mode, i.e. each single statement is implicitly assumed to be contained within an automatic transaction.
   When you are working in autocommit mode typing a simple DELETE ... statement is understood to mean: BEGIN; DELETE ...; COMMIT;
4. SQLite is a transactional database engine; working in autocommit mode easily can be a major cause for performance degradation.
5. so, you are strongly encouraged to use transactions as appropriate in a sensible way.

1. the CREATE TABLE allows you to create a new table, specifying the columns it contains too.
2. you know well that a Spatial column is a BLOB one
3. the INSERT INTO allows you to store new rows in the table.
4. using the GeomFromText() you can create new GEOMETRY values at your will.
5. you have also performed a final check, using SELECT.
6. wow !!! you have just created a table and inserted Spatial data in it, so you can happily exit the SQLite session now.

1. you started a transaction by invoking BEGIN
2. then you have created your table
3. then you have inserted some rows
4. ... and after all you have suddenly stopped your session omitting to confirm all pending operation.
   You've forgotten to invoke COMMIT.
   So all the job you had done has gone forever.
5. this one is the dark side of transactions ... never forget to COMMIT a pending transaction, or your work will simply disappear.
   I hope this catastrophic example may help you to avoid transaction disasters.

1. the UPDATE SQL statement lets you modify any column value.
   You simply have to SET column names and new values that will replace the current ones.
2. you can use as usual the GeomFromText() function to obtain GEOMETRY values.
3. the PK_UID column is a special one, being the PRIMARY KEY for this table.
   In every table a PRIMARY KEY column assures you that one and only one row may contains a selected value, thus assuring univocity.
4. if one of your tables, for any reason, has no PRIMARY KEY, don't worry; every way SQLite manages automatically an useful unique identifier for each row in a table, whom name is ROWID; you can reference it SQL expressions exactly as it was an ordinary column name.
5. note also that in this example you have apparently not used any transaction [you have used neither BEGIN nor COMMIT]; we have already explained this specific point, but it's interesting to underline this a second time.
   SQLite acts always as a transactional DBMS; what really happens behind the scenes in cases like this, is that you are implicitly using auto-commit mode i.e.:
   • a new transaction is implicitly BEGINned for each SQL statement
   • and an implicit COMMIT is automatically performed after processing the SQL statement.
   • this may easily cause a more or less severe performance degradation; SQLite prefers by far that you explicitly BEGIN and COMMIT transaction, particularly when you are performing many and many consecutive INSERTs and / or UPDATEs.

1. using CREATE TABLE .... AS SELECT ... SQLite clones automatically a new table [exactly identical to the original one; same column names etc].
2. and then all rows coming out from the SELECT will be written in this new table.

1. first you have create the new table, freely choosing columns, their names etc.
2. then you call INSERT INTO ... SELECT that performs an automatic data transfer [either selected or complete] between the two tables.

1. a DROP statement will completely eliminate a table and all data it contains.
2. never forget to perform a VACUUM after dropping tables, in order to really free unused space, compact the database and so on.

1. SQLite does not enforce type checking for column values
2. you can place any kind of data you want in each column, without restrictions; no checking is performed in order to assure conformance with column type declared in CREATE TABLE
3. you may either love or hate this; it's not at all a bug, it's an explicit design feature of SQLite.

1. the CHECK clause allows you to define column-based constraints, thus enforcing type conformity.
2. SQLite enforces CHECKing before inserting or updating column values.
3. an implicit type conversion may be performed as well when appropriate.

4. Performing some Spatial data analysis

4.1 Evaluating MBRs relationships

1. Evaluating spatial relationship existing between two geometries by comparing their MBRs relations is a very imperfect and approximative way to check real spatial relations.
2. Anyway, MBRs comparisons are the coarsest but fastest way to compare spatial relations.
   Evaluating real relation existing between two arbitrary complex geometries may be an heavily time consuming task. Vice-versa an MBRs comparison is very quick to evaluate.
3. So evaluating MRBs is at least the best strategy to restrict the field.
   Once you have eliminated the [many] incompatible couples, then you can pass to check better only the [few] couples that may be of real interest.

1. if two MBRs are disjointed, surely related geometries are disjointed as well.
2. but if two MBRs overlaps or intersects, you cannot be sure at all that related geometries overlaps or intersects as well; in this circumstance you need to check better by evaluating real Spatial relations.

1. the SpatiaLite MBRContains() function allows you to restrict the search field.
2. the GeomFromText('POLYGON(...)') build the MBR corresponding to current frame
   [i.e. screen, some raster image, paper sheet on a printer etc]

1. you can easily use subsequent call to MBRContains() with different frames [MBRs]in order to obtain a zoom in sequence.
2. simply you have to use each time a wider or narrower MBR, as needed.

1. the MBRWithin() function is the same as MBRContains(), but the order of MBRs is inverted.
2. you can freely use the one of them you find more convenient; result is the same anyway.

1. do you remember? the HighWay table has a GEOMETRY class of type LINESTRING / MULTILINESTRING
2. when accessing LINESTRINGs or POLYGONs [or their MULTI-collections] the SpatiaLite MBRIntersects() function allow you to select entities within a given frame.
3. for MBRIntersects() the order by which you refer geometries hasn't any influence.

5. Managing Coordinate Reference Systems and Coordinate Transformation

When you try to use two GIS datasets belonging to different Coordinate Reference Systems, entities falls very far because numeric values for coordinates are obviously in two different spaces.

But if you apply some opportune coordinate reprojection for one dataset, so to put it in the same Coordinate Reference Systems of the other one, entities will correctly overlap as expected.

1. each Coordinate Reference System is uniquely identified by a srid value.
2. and is qualified by an intelligible name
3. and has a lot of [quite obscure] geodetic projection parameters
   Don't worry about this ... you can just get them as black magic for now ...
   If you want to learn more about them, you can usefully read the followings: http://en.wikipedia.org/wiki/Geodetic and ftp://ftp.remotesensing.org/proj/OF90-284.pdf

1. the SpatiaLite SRID() function allows you to identify the srid value that identifies any kind of geometry.
2. all geometries in the test.db have a srid value of 32632.
   You can check this by yourself.

1. you can perform a simple join to discover what srid 32632 really means.
2. all geometries in the test.db pertains to the UTM zone 32N Coordinate Reference System.

1. the srid that identifies WGS 84 is 4326.
   You can check this by yourself.
2. the SpatiaLite Transform() function obtains a new geometry from an original one, applying a coordinate reprojection as required.
3. Now you have two alternative geometries for the Towns table:
   • the original GaiaGeometry column contains geometries in the 32623 - UTM zone 32N Coordinate Reference System.
   • the new wgs84 column contains geometries in the 4326 - WGS84 Coordinate Reference System.
   • you can now use the one or the other at your will, as required and appropriate.

1. the SpatiaLite SRID() function returns an unexpected –1 value.
   For SpatiaLite this means our geometries have an unknown / undefined Coordinate Reference System.
2. the reason for this is in that we loaded the my_new_db.db database from shapefiles.
   Unhappily shapefile is a data format that don't contains any information referencing SRIDs.
3. we can easily recover from this mismatch; simply we have to assign explicitly a SRID to our geometries.

1. the SpatiaLite SetSRID() function returns a new geometry identical to the original one, but with a different SRID value.
2. Please note that no coordinate transformation is applied; this function merely modifies the SRID value.

1. we have encountered a second problem.
2. the epsg table doesn't exists in the my_new_db.db database we are currently using.
3. if you remember the steps we have performed since now, this one should not be a surprise at all:
   • we have created the my_new_db.db database from scratch.
   • then we've populated it by importing some shapefiles.
   • so, it's obvious that no epsg table has ever been created since now.
4. we have to learn now how it is possible to create and load the epsg table required by SpatiaLite in order to perform coordinate reprojections.
   • you need to have a copy of the epsg-sqlite.sql script to create and initialize the epsg table.
   • if you don't have one, you can easily download it from here.
   • then we shall run this sql script.

6. Performing SQL queries directly on shapefiles

1. You already know this; you can use .load in order to dynamically load an SQLite extension.
2. If you load both the VirtualShape and the SpatiaLite extensions, your SQLite db will be able to perform SQL standard queries directly on shapefiles, with no needing at all to import them in some SQLite table.
3. we can perform a very quick test by using one shapefile, i.e. the shape_towns we have previously created.

1. with CREATE VIRTUAL TABLE you request SQLite to access some exotic external data source:
   • test_shape is the table name [from an SQL perspective]
   • the USING VirtualShape() declarations specifies the drivers name you wish to use
   • shape_towns is the only argument required by the VirtualShape driver, and identifies some shapefile path [without any suffix appended !!!]
2. the PRAGMA info_table simply requests SQLite to show all columns within a table; you already known this.
3. as you can see, any attribute defined for your shapefile has been mapped into a corresponding SQL type.
4. you can notice too that there are two other columns:
   • the PKUID column is an unique value that identifies each entity.
     [it simply identifies relative entity positions aka record numbers]
   • the GEOMETRY column contains shapefile geometries, but translated into standard SpatiaLite [OpenGis] geometries.

1. the VirtualShape driver is delegated to physically access the external data source [actually, a shapefile]
   • and then to perform any requested data format conversion
   • thus allowing the SQL engine to process data exactly in same way as if they where native SQL data
2. the only restriction is that [for now ...] the current VirtualShape implementation is limited to read only operations
   • so you are not allowed to perform any DELETE, INSERT or UPDATE command.
     
   • you can instead perform any kind of SELECT command using a VIRTUAL TABLE directly based on some shapefile via VirtualShape.
3. please note that VIRTUAL TABLEs definitions are stored permanently inside the SQLite database
   • if you stop your current SQLite session, you'll discover next time you begin a new session using the same database that your VIRTUAL TABLEs are still present and valid.
   • you must explicitly destroy any VIRTUAL TABLE, in the same way you destroy ordinary tables.

1. your VIRTUAL TABLE will be cancelled, and is no longer available
2. don't worry about the original external shapefile; it still exists, with no modifications.
   Dropping a VIRTUAL TABLE simply cancels the SQLite link with the external data source, but don't deletes this later one.