Note: these pages are no longer maintained

Never the less, much of the information is still relevant.
Beware, however, that some of the command syntax is from older versions, and thus may no longer work as expected.
Also: external links, from external sources, inside these pages may no longer function.

	Your first Spatial SQL queries
2011 January 28

Previous Slide

Table of Contents

Next Slide

SpatiaLite is a Spatial DBMS, so it's now time to perform some Spatial SQL query.
There isn't absolutely nothing odd in Spatial SQL: it basically is exactly as standard SQL, but it supports the exotic data-type Geometry.
Usually you cannot directly query a Geometry value (as we've already seen they simply are a meaningless BLOB):
you are expected to use some appropriate spatial function to access a Geometry value in a meaningful way.

SELECT COD_REG, REGIONE, ST_Area(Geometry)
FROM reg2001_s;

The ST_Area() function is one of such Spatial functions; usually you can easily recognize any Spatial function, simply because all them are ST_ prefixed.
This one is not an absolute rule, anyway: SpatiaLite is able to understand the alias name Area() to identify the same function.
As the name itself states, this function computes the surface of the corresponding Geometry.

SELECT COD_REG AS code,
REGIONE AS name,
ST_Area(Geometry) / 1000000.0 AS "Surface (sq.Km)"
FROM reg2001_s
ORDER BY 3 DESC;

As you surely noticed, the first query returned very high figures: this is because the current dataset uses meters as length unit, and consequently surfaces are measured in m².
But we simply have to apply an appropriate scale factor to get the most usual km² units.
Please note two SQL features we are introducing for the first time:

SQL isn't constrained to directly return the column value into the result-set: you can freely define any valid arithmetic expression as required.
Referencing a complex expression into some ORDER BY clause isn't too much practical:
but you can easily identify any column using its relative position (first column has index 1, and so on).

SELECT COD_REG AS code,
  REGIONE AS name,
  ST_Area(Geometry) / 1000000.0 AS "Surface (sq.Km)",
  POP2001 / (ST_Area(Geometry) / 1000000.0)
    AS "Density: Peoples / sq.Km"
FROM reg2001_s
ORDER BY 4 DESC;

And you can perform even more complex calculations in SQL.
This query will compute the population density (measured as peoples / km²).

All right, you have now acquired a basic SQL / Spatial SQL knowledge.
You are now ready to confront yourself with most complex and powerful queries: but this requires building a serious database.
Do you remember ? for now we where simply using Virtual Shapefiles tables; i.e. the faint imitation of real Spatial tables (internally stored).
So during the next steps we'll first create and populate a well designed DB (not a so trivial task), and then we'll come again to see most complex and sophisticated SQL queries.

Previous Slide

Table of Contents

Next Slide

	Author: Alessandro Furieri a.furieri@lqt.it
	This work is licensed under the Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0) license.

	Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.3 or any later version published by the Free Software Foundation; with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts.