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.

SpatiaLite logo

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.

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.

  REGIONE AS name,
  ST_Area(Geometry) / 1000000.0 AS "Surface (sq.Km)"
FROM reg2001_s
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 .
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:
  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
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

CC-BY-SA logo Author: Alessandro Furieri
This work is licensed under the Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0) license.

GNU logo 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.