SpatiaLite logo

Executing your first SQL queries

2011 January 28

Previous Slide Table of Contents Next Slide

spatialite_gui Fig.8
You can follow two different approaches in order to query a DB table:
  1. you can lazily use the Query Table menu item.
    1. this surely is quickest and easiest way, completely user friendly.
    2. you simply have to click the mouse's right button over the required table, so to make the context menu to be shown.
    3. then you can simply use the lowermost buttons in order to scroll the result-set back and forth at your will.
    4. anyway, this approach is rather mechanical, and doesn't allow you to exploit SQL at the best of its powerful capabilities.
  2. alternatively you can hand-write any arbitrary SQL statement into the uppermost pane, then pressing the Execute button.
    1. this one is the hardest way: you are responsible for what you are doing (or even misdoing ...)
    2. but this way you can take full profit of the impressive SQL unconstrained fire power.
spatialite_gui Fig.9
You surely noticed in the above illustration that Geometry columns simply reports an anonymous BLOB GEOMETRY: this is far to be satisfactory.
But you can get a much richer preview of any Geometry simply clicking the mouse's right button over the corresponding value, then selecting the BLOB explore menu item.
spatialite_gui Fig.10

SELECT COL002 AS name,
  COL006 AS longitude,
  COL005 AS latitude,
  MakePoint (COL006, COL005, 4326) AS geom
FROM cities1000
WHERE COL009 = 'IT';

You can test how free-hand SQL works using this SQL statement: simply copy the above SQL statement, then paste into the query pane, and finally press the Execute button.
Just a quick and fast explanation: All right, you are now supposed to be able to really start working seriously.
In the next slides we'll start exploring the mystery world of SQL and Spatial SQL.

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.