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-2.3.sqlite 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
  • 4.2. Evaluating relationships between geometries
  • 4.3. Boolean operations on geometries
  • 4.4. Further operations on geometries
• 5. Managing Coordinate Reference Systems and Coordinate Transformation
• 6. Performing SQL queries directly on shapefiles
• 7. Performing SQL queries directly on CSV or TXT-tab files
• 8. Spatial Index: using SQLite's R*Tree
• 9. Spatial Index take two: using MbrCache
• 10. Struggling with locale_charset settings

1. Preparing to start

1.1. What you need to begin

1. The simplest way is to use the spatialite.exe executable; it contains all-in-one anything you need in order to get started.
   
   • spatialite.exe doesn't requires an installation.
     You simply have to create an empty folder (e.g. C:\SpatiaLite) and just copy the executable file inside; then put the test-2.3.sqlite sample database in the same folder. Now you are ready to start.
2. If you are not accustomed to use command line tools, and you strongly dislike this kind of stuff, don't worry.
   You are free to use a true GUI-tool as well [i.e. one supporting graphic windows, a mouse, buttons and so on].
   You simply have to download and run the spatialite-gui suited for the platform you are currently using; this too doesn't require any installation at all, and contains all-in-one anything you need.
3. Alternatively, you may wish to load SpatiaLite as a dynamic extension into the basic, standard sqlite.exe.
   You can do such a thing, but it's a little harder to do, and you may easily encounter some configuration headache.
   • In order to start using SpatiaLite as a SQLite's loadable extension you must have the libspatialite-2.dll somewhere on your local hard disk.
   • You need to have the SQLite front end sqlite3.exe as well 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
     Important notice: many SQLite's binary distributions are built disabling the option to load any dynamic extension; if this is your case, any attempt to load SpatiaLite will obviously fail.
   • Depending on the configuration you've chosen, others DLL's may be required, such as the libproj-0.dll, libgeos_c-1.dll and libgeos-3.x.x.dll
   • Neither sqlite3.exe nor libspatialite-2.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-2.3.sqlite sample database in the same folder.
   • It's very easy you'll fall into the DLLs hell; you are warned [and discouraged to do such a thing].
     Use the spatialite.exe all-included binary instead, and don't involve yourself into this DLLs nightmare.
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-2.3.sqlite 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 use the SpatiaLite extension.

2. Getting started with SpatiaLite

2.1 How to load SpatiaLite [for sqlite3 users only]

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 returns the average centroid when applied to a MULTYPOLYGON.

2.4. GEOMETRY ENVELOPE

1. the SpatiaLite Envelope() function always returns a POLYGON that is the Minimum 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.
4. MBRs are also widely referenced as bounding boxes, or BBOX as well.

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 .dumpshp macro command exports a whole table as a shapefile.
2. SpatiaLite handles macro command arguments as they where shell arguments:
   • argument #1 identifies the table name you want to export.
   • argument #2 is the column name that identifies geometries to be exported.
   • argument #3 is the pathname of the shapefile to be generated.
     Important notice: you must specify this path without appending any .shp, .shx or .dbf suffix
   • argument #4 is the charset_name to be used when exporting alphanumeric attribute values into the shapefile
     Assuming you are managing the test session on a Windows PC, and your national language is italian, the CP1252 Windows Latin-1 is the correct charset_name you have to specify.
   • argument #5 [optional] specifies which one GEOMETRY CLASS you intend to export.
     • GEOMETRY CLASS (if specified) must be one of: POINT, LINESTRING, POLYGON or MULTIPOINT
     • you may omit the GEOMETRY CLASS argument if this is already known via spatial metadata
       you'll see what spatial metadata are in a following paragraph

3.2 Creating a new SpatiaLite db and populating it

1. the SpatiaLite .loadshp macro command imports a whole shapefile creating a new database table.
2. SpatiaLite handles macro command arguments as they where shell arguments:
   • argument #1 is the pathname of the shapefile to be imported.
     • Important notice: you must specify this path without appending any .shp, .shx or .dbf suffix
   • argument #2 identifies the table name to be created.
     This table must not exists before loading the shapefile
   • argument #3 identifies the charset_name used to encode the shapefile's alphanumeric attribute values.
     Do you remember? We used the CP1252 Windows Latin-1 when exporting shapefiles.
   • argument #4 [optional] when specified sets the required SRID value.
     In these examples we haven't specified it, so by default -1 was implicitly assumed.
   • argument #5 [optional] when specified selects the geometry column name.
     In these examples we haven't specified it, so by default Geometry was implicitly assumed.

• the CREATE TABLE SQL statement is used in order to create a new table and defining its columns.
• the INSERT INTO is another SQL statement used to create new rows in a table.
• 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.
• the BEGIN and COMMIT SQL statements defines a transaction; a single transaction is intended to define an atomic operation.
  i.e. the whole transaction will succeed or fail; if something goes wrong for any reason, our db will remain unchanged.
• we'll see later all this in a deeper detail

1. please note that original column names can be subject to truncation.
   Unfortunately, shapefile format didn't allow column names to be longer than 10 character.
2. there is now a PK_UID_X0 column name; this is to disambiguate a potential duplicate column name

1. the .read macro command executes an SQL script.
   The init_spatialite-2.3.sql script is a standard one [you can download it] and it's used to initialize the so called Spatial MetaData
   As you can notice, it invokes the InitSpatialMetaData() SpatiaLite's SQL function
   You have to specify the charset_name used to encode the SQL Script text; this time we can use the ASCII one.
2. immediately after executing the init_spatialite-2.3.sql script you can check that two new tables have been created, i.e. spatial_ref_sys and geometry_columns.
   A geom_cols_ref_sys table-like object has been created; this actually is not a real table; we'll see very soon what it really is
3. as you can notice too, this time we used the 5 arguments form for .loadshp, specifying a SRID and a geometry column name as well.
4. consequently, the .loadshp macro function has used a separate AddGeometryColumn() statement in order to create the geometry column in way that is consistent with Spatial MetaData handling.

1. as you can check, now the Spatial MetaData correctly report that there is a Spatial column in the table NewTowns
2. and that this column has to contain Point geometries within the 32632 SRID
3. you can check too that the 32632 SRID identifies the UTM zone 32N, and has lots of geodetic parameters fully qualifying it.

1. the geom_cols_ref_sys seems to be a table, but really is a view
2. a view is actually a kind of virtual table, and is internally defined as an SQL query; but you can directly query it as if it was a real table
3. in the SQLite implementation, you can apply SELECT on a view, but INSERT, UPDATE or DELETE are forbidden
4. defining views is a very useful way to simplify database queries, as in this example

1. SQLite supports triggers; so SpatiaLite, while creating a new geometry column via AddGeometryColumn(), defines 4 triggers in oder to ensure:
   • that geometries contained in this column have all the same geometry type
   • and that they all refers to the same SRID
   • both for INSERT and UPDATE
   • so you can now confidently rely on the assumption that you are implementing a strongly constrained and well checked Spatial table.

1. first we have to set an explicit SRID value for any geometry within the NewRegions table
   Otherwise any attempt to associate Spatial MetaData will fail
2. and then we can try to use the RecoverGeometryColumn() function in order to associate the required column with Spatial MetaData:
   • this function will scan the entire table
   • checking if any value in the geometry column satisfies the required type and SRID
   • if this conditions are both TRUE, this geometry column is then associated with Spatial MetaData
   • and triggers too are created

• as you can see, anything has been recovered
• now the Geometry column in NewRegions table is correctly associated with Spatial MetaData
• it may be of limited use, but you can reverse this operation too:
  • you simply have to use: DiscardGeometryColumn('NewRegions', 'Geometry')

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. defining an MBR via GeomFromText('POLYGON(...)') is quite complex, and unpractical.
2. so SpatiaLite implements an alternative way: you can use the BuildMBR( X1 , Y1 , X2 , Y2 ) function in order to define an MBR.
   • the [ X1 , Y1 ] coordinate values identify a first point
   • the [ X2 , Y2 ] coordinate values identify a second point
   • resulting MBR is then built assuming that the line segment identified by these two points is a diagonal for this rectangle
3. another similar function is BuildCircleMBR( X , Y , radius ).
   • the [ X , Y ] coordinate values identify a point
   • a circle centered on this points and of given radius is then used in order to build the MBR

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.

4.2 Evaluating relationships between geometries

• Equal: if they are spatially identical
• Disjoint: if the intersection of both geometries is the empty set
• Touches: if the only points in common between both geometries lie in the union of their boundaries
• Within: if the first geometry is completely contained into the second one
• Overlaps: if the intersection of both geometries results in a value of the same dimension of both geometries and is different from both the first and the second geometry
• Crosses: if the intersection of both geometries results in a value whose dimension is less than the maximum dimension of both geometries and the intersection value includes points interior to both geometries, and the intersection value is not equal to either the first or the second geometry
• Intersects: if the intersection of both geometries is not empty
• Contains: if the second geometry id completely contained into the first one
  
• Relate: allows arbitrary comparison on the basis of a given pattern Matrix

Equal(g1, g2)
Disjoint(g1, g2)
Touches(g1, g2)
Within(g1, g2)
Overlaps(g1, g2)
Crosses(g1, g2)
Contains(g1, g2)

• Equals ( geom1 , geom2 )
• Disjoint ( geom1 , geom2 )
• Touches ( geom1 , geom2 )
• Within ( geom1 , geom2 )
• Overlaps ( geom1 , geom2 )
• Crosses ( geom1 , geom2 )
• Intersects ( geom1 , geom2 )
• Contains ( geom1 , geom2 )
• Relate ( geom1 , geom2 , patternMatrix )

1. all these functions may return one of the following values:
   • 1 if the spatial relation is TRUE
   • 0 if the spatial relation is FALSE
   • -1 if there is some error [i.e. one or both geometries are NULL]
2. the Relate() function is very general one, and requires a patternMatrix defining what kind of spatial relation has to be checked:
   • patternMatrix is a 9 characters string representing a 3 x 3 matrix
   • each character represents an intersection between the interior, boundary and exterior of each one geometry
     • T means TRUE
     • F means FALSE
     • * means don't care
     • 0, 1, 2 means that an intersection must exists, and must have given dimension
   • e.g. the "T*T***T**" string corresponds to the overlaps matrix
     and the "FF*FF****" string corresponds to the disjoint matrix

1. this query counts how many towns fall inside a Regional boundary
2. we want to scan only a limited list of Regions, and consequently we use the IN (..) clause
3. the Within() function will then test the spatial relation existing between each town and regional boundary
4. A little curiosity: as you can notice, there is a strange notation inside the IN() clause:
   the string 'VALLE D''AOSTA' really needs four apices [it's intended, it's not a typo]
   this is because there is an apostrophe within this noun, and the SQL syntax pretends to mask apostrophes marking them as a double apex
5. as you can easily notice, this one is a slow running query.
   Reasons explaining this behaviour are very easy to identify:
   • the Within() function implies an heavy computational load (as any other spatial relationship function does)
   • the NewTowns table contains some 8000 rows
   • we've requested to select 8 rows from the NewRegions table
   • so, this query has to evaluate some 64000 times a Within() relationship; and this takes a long time ...
   We can take full profit from an opportune usage of Spatial Index in order to optimize this query
   We'll better explain this step further away, when analyzing the Spatial Index stuff [if you now feel a morbose curiosity about this, you can jump immediately here]

1. the SpatiaLite Distance() functions measures the minimum distance intercurring between two geometries
   this one too is an useful kind of spatial relation.
2. this query identifies any town laying in the neighbourhood of the Florence town
   we've assumed a 10Km range to do this
3. Please note: if the two geometries aren't disjoined, their relative distance is exactly 0

4.3 Boolean operations on geometries

• Intersection: return a geometry that is the set intersection of both geometries
  it equals the AND boolean operator
• Difference: return a geometry that is the closure of the set difference of both geometries
  it equals the AND NOT boolean operator
• GUnion: return a geometry that is the set union of both geometries
  it equals the OR boolean operator
• SymDifference: Return a Geometry that is the closure of the set symmetric difference of both geometries
  it equals the XOR boolean operator

g3 = Intersection(g1, g2)
g3 = Difference(g1, g2)
g3 = GUnion(g1, g2)
g3 = SymDifference(g1, g2)

• geom3 = Intersection ( geom1 , geom2 )
• geom3 = Difference ( geom1 , geom2 )
• geom3 = GUnion ( geom1 , geom2 )
• geom3 = SymDifference ( geom1 , geom2 )

1. all these functions return a GEOMETRY representing the result of the boolean operation
   please note that the returned GEOMETRY may be an empty one
2. the OpenGis standard defines the Union() function; but in the SQLite's own syntax Union is a reserved keyword, so SpatiaLite renames this function as GUnion()

4.4 Further operations on geometries

• ConvexHull: given a set of points X return the minimal convex set containing X.
• Buffer: return a zone of specified distance around any generic geometry.
• Simplify: return a simplified geometry, i.e. one containing a lesser number of vertices but still preserving the original shape with a satisfying approximation.

g2 = ConvexHull(g1)		the convex hull may be easily visualized by imagining an elastic band stretched open to encompass the given object; when released, it will assume the shape of the required convex hull.
g2 = Buffer(g1, radius)		A GIS buffer operation creates a zone of a specified width around a POINT or a LINESTRING or a POLYGON. it is also referred to as a zone of specified distance around any generic geometry (this late being of complex type as well, such as MULTIPOINT, MULTILINESTRING and so on). this example shows how you can obtain different buffers repeatedly applying an increasing radius on the same geometries.
g2 = Simplify(g1, tolerance)		the aim of Douglas Peuker algorithm is to simplify geometries. this algorithm tries to preserve directional trends in a line using a tolerance factor which may be varied according to the amount of simplification required.

• geom2 = ConvexHull ( geom1 )
• geom2 = Buffer ( geom1 , radius )
• geom2 = Simplify ( geom1 , tolerance )
• geom2 = SimplifyPreserveTopology ( geom1 , tolerance )

1. all these functions return a GEOMETRY representing the result of the requested operation
   please note that the returned GEOMETRY may be an empty one
2. the OpenGis standard defines the ConvexHull() and Buffer() functions;
   the Simplify() and SimplifyPreserveTopology() aren't defined by OpenGis, but are commonly implemented by many spatially enabled DBMSs
3. ConvexHull() can be applied to any kind of geometry, because we can assume that line vertices and / or polygon border vertices are Points anyway
4. Buffer() also can be applied to any kind of geometry.
   the requested radius is intended as expressed in the same units used by referred Geometry
5. Simplify() can be applied only to linear geometries [this including also polygon borders], but not to Points or MultiPoints.
   the tolerance factor determines the desired amount of simplification [i.e. a biggest tolerance value produces a strongest simplification than a smallest one]
   tolerance has to be expressed in the same units used by referred Geometry
6. SimplifyPreserveTopology() represents a specialized version of Simplify(), applying extra care in order to fully preserve the original topology.
   e.g. it's best suited for Polygons including internal holes.

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. the spatial_ref_sys table is a Spatial MetaData one, and is initialized by executing the init_spatialite-2.3.sql script
2. each Coordinate Reference System is uniquely identified by a srid value.
3. the auth_name and auth_srid respectively identifies the authority producing this data and the original Srid
4. each CSR is qualified by an intelligible ref_sys_name
5. and has a lot of [quite obscure] geodetic projection parameters in the proj4text column
   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. you can perform a simple join to discover what srid 32632 really means.

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 NewTowns table:
   • the original geom 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.

6. Performing SQL queries directly on shapefiles

1. The VirtualShape extension is automatically bounded within the SpatiaLite extensions, so while loading SpatiaLite you can perform SQL standard queries directly on shapefiles, with no needing at all to import them in some SQLite table.
2. 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 first argument required by the VirtualShape driver, and identifies some shapefile path [without any suffix appended !!!]
   • second argument CP1252 specifies the charset_name used to encode the shapefile's alphanumeric attribute values.
     Do you remember? We used the CP1252 Windows Latin-1 when exporting this shapefile.
   • third argument 32632 specifies the SRID to be used for Geometries within this shapefile
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 PK_UID 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.

7. Performing SQL queries directly on CSV or TXT-tab files

1. the VirtualText driver is delegated to physically access the external data source [actually, a CSV or TXT-tab text file]
   • the first parameter required by the CREATE VIRTUAL TABLE ... USING VirtualText(...) is the textfile pathname
   • the second parameter is the charset encoding used by the textfile
   • third parameter [optional] can be used to select how to handle the first line:
     • you can use a 1 value [default] if first line contains column names
     • otherwise, use a 0 value if first line just contains plain column values; in this occurrence VirtualText will generate a default name for each column
   • fourth parameter [optional] can be used to select the decimal separator:
     • passing the POINT value [default] you specify that . has to used as the decimal separator
     • otherwise, passing the COMMA value you specify that , has to be used as the decimal separator
   • fifth parameter [optional] can be used to select the text separator:
     • passing the DOUBLEQUOTE value [default] you specify that " has to used as the text separator
     • otherwise, passing the SINGLEQUOTE value you specify that ' has to be used as the text separator
   • the sixth and last parameter [optional] can be used to select the field separator:
     • passing the TAB value [default] you specify that tab has to used as the field separator
     • otherwise, you can specify the field separator character enclosing it within single quotes
       • specifying ',' selects comma to be the field separator
       • specifying ':' selects colon to be the field separator and so on
2. please note: a VirtualText table is a read only table
   • so you are not allowed to perform any DELETE, INSERT or UPDATE command.
     

8. Spatial Index: using SQLite's R*Tree

1. the CREATE INDEX SQL statement is used in order to build an Index
2. when an Index is added to some table, then SQLite applies the following actions:
   • while inserting, updating or deleting any row, the Index too has to be coherently updated, in order to avoid any misalignment
   • while querying, if an Index can speed up the query, then has to be automatically used.

1. the DROP INDEX SQL statement is used in order to remove an Index

1. do you remember of Spatial MetaData ? we've met them in a preceding paragraph.
2. the spatial_index_enabled column is used in order to detect if a Geometry-type column has to be Spatially-Indexed.

1. the SQLite RTree is implemented as a VIRTUAL TABLE
   you already know what SQLite's Virtual Tables are; we have just see this point about the VirtualShape
2. an RTree requires four tables:
   • prefix
   • prefix_node
   • prefix_parent
   • prefix_rowid
3. the most relevant one is the prefix, that represents the MBRs [aka BBOXs] for indexed geometries;
   the pkid column contains the Primary Key value identifying the corresponding row within the indexed table
   the xmin, xmax, ymin and ymax columns are used to represent the MBR.

1. SpatiaLite on its own implements three triggers in order to ensure a full correspondence between geometries into the main table and corresponding spatial index.
2. each time you'll insert, update or delete a row within the main table, you can safely assume that index infos are consequently updated the right way, because a trigger actively enforces such a constraint.

1. The first query has nothing of interesting; it's a trivial query, and doesn't involves the Spatial Index.
2. The second query instead will take full advantage from using the Spatial Index.
3. Important notice: when you'll try these two queries, very easily you'll notice no difference at all between them.
   This is because the NewTowns table contains only 8,000 rows; if you are using a quite decent hardware, a so simple dataset cannot evidentiate any penalization when using a full table scan.
   But while accessing a more complex dataset (say one containing some million rows) the difference become quite impressive; query execution time may vary from some minutes to few seconds

1. We've simply rewritten the former query in order to take advantage of the Spatial Index that is now available.
2. This means that we are no longer evaluating the Within() spatial relationship for each NewTowns entity, but only for the ones falling inside each NewRegions entity MBR.
3. The Spatial Index allows to quickly retrieve such NewTowns entities
4. As you can easily notice, the query rewritten in this later form takes a noticeably shorter time to complete

1. first you have to call DisableSpatialIndex(); this will remove the triggers, so to stop Spatial Index updating.
2. this action doesn't removes the RTree itself; you have to call DROP TABLE explicitly in order to physically remove it.
3. after this, performing a VACUUM is a very good idea, in order to maintain your database in an efficient state.

9. Spatial Index take two: using MbrCache

1. the MbrCache is implemented as a VIRTUAL TABLE; you already know what SQLite's Virtual Tables are
2. each row of this Virtual Table represents the MBRs [aka BBOXs] for corresponding indexed Geometry;
   the rowid column contains the Primary Key value identifying the corresponding row within the indexed table.
   the mbr column is used to represent the MBR [please note: this is not an ordinary Geometry-type value; thus you cannot use the AsText() etc functions on it]

1. SpatiaLite on its own implements three triggers in order to ensure a full correspondence between geometries into the main table and corresponding MbrCache.
2. each time you'll insert, update or delete a row within the main table, you can safely assume that cached infos are consequently updated the right way, because a trigger actively enforces such a constraint.
3. the BuildMbrFilter() function is a special one, and has to be mandatorily used in order to create the MBRs stored within an MbrCache

1. The first query has nothing of interesting; it's a trivial query, and doesn't involves the MbrCache.
2. The second query instead will take full advantage from using the MbrCache.
3. Important notice: when you'll try these two queries, very easily you'll notice no difference at all between them.
   This is because the Towns table contains only 8,000 rows; if you are using a quite decent hardware, a so simple dataset cannot evidentiate any penalization when using a full table scan.
   But while accessing a more complex dataset (say one containing a million rows or so on) the difference become quite impressive; query execution time may vary from several seconds to few milliseconds

• FilterMbrWithin() will scan the MbrCache returning any entity whose MBR is spatially within the selection rectangle
• FilterMbrContains() will scan the MbrCache returning any entity whose MBR spatially contains the selection rectangle
• FilterMbrIntersects() will scan the MbrCache returning any entity whose MBR intersects the selection rectangle

10. Struggling with locale_charset settings

• there was a time, many and many years ago, in the happy '70s, when computers had no character_set hell at all, or quite so ...
• there was then just the standard, universal ASCII encoding; as an exception, the IBM mainframes from their own used an esoteric EBCDIC encoding.
• both ASCII and EBCDIC where 7-bits encodings, supporting digits [0-9], lowercase letters [a-z], uppercase letters [A-Z] and a few other interpunctuation signs [+-*(),;=!?#] and control characters
• there was no way at all to represent national characters using those charsets
  i.e. it was impossible to use the italian (or french) language accent vowels, such as à é è ì ò ù
• so, when PCs and MS-DOS become widespread in the '80s, the original ASCII was extended as an 8-bits encoding, thus allowing to support odds chars used by many european languages ... and the chaos begun
• after while, Windows conquered the world in the '90s; in order to support asiatic languages (chinese, japanese etc), 16-bits encodings become widespread ... and the chaos was paramount
• in an desperate, last-ditch effort to re-establish law and order, someone started to classify and standardize charset_encodings, in order to allow some kind of interoperabily, as the Internet required
• then, the UTF-8 encoding was defined; this one is a variable length encoding, requiring from 1 to 4 bytes to encode a single character; UTF-8 really is an universal encoding able to support any known human language, european or asiatic as well
• Linux fully supports UTF-8, and this one really is a very good thing
• unhappily, Windows still continues supporting its own charsets; so, a major compatibility problem arises while transferring files from one system to the other

• we'll now connect the test-2.3.sqlite database, which is fully UTF-8 encoded
• there are some accent vowels within the Name column belonging to the Towns table
• consequently, we'll try to extract them using an SQL query

• I'm actually performing my spatialite test session on a Windows PC, using italian settings
• accordingly to this, the platform locale_charset is defined as CP1252 Windows Latin-1;
  spatialite can easily detect this, automatically converting keyboard input from CP1252 to UTF-8, and screen output from UTF-8 to CP1252
• unhappily this doesn't seems to work at all; reason is that we are actually running spatialite.exe from the Command Prompt.
• as by one of the many Windows oddities, the italian settings used by the Command Prompt isn't the one corresponding to CP1252 Windows Latin-1, but its older MS-DOS equivalent, i.e. the CP850 DOS/OEM Western Europe
• we have to fix this mismatch, in order to get italian accent vowels handled the right way

• the macro command .charset CP850 tells spatialite to use this locale_charset to convert keyboard input and screen output
• after this, you can check that any query involving accent vowels is now correctly processed

• if you know for sure that the shapefile you are trying to import was produced on some Linux platform, it's very alike it is UTF-8 encoded
• if it was produced on some Windows platform, it's very alike it uses one of the encodings in the range CP1250 - CP1258
  at least, if you live in an european language speaking country
• and if it was produced on some Mac platform, it's very alike it uses one of the encodings in the range MACxxx
• anyway, there is no way to know for sure which one encoding was used to produce some shapefile; very often you have to discover this by yourself, following a try and error process