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.

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 .
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:
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

CC-BY-SA logo Author: Alessandro Furieri a.furieri@lqt.it
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.