../_images/logo_spatialite.png

SpatiaLite Quickstart

SpatiaLite is an SQLite database engine with spatial functions added.

SQLite is a Database Management System (DBMS) which is simple, robust, easy to use and very lightweight. Each SQLite database is simply a file. You can freely copy it, compress it, and port it between Windows, Linux, MacOs etc.

This Quick Start describes how to open a spatialite database from both GUI applications and the command line. Some sample SQL queries are shown.

Start Spatialite and load a database

Spatialite-gui provides a visual interface for viewing and maintaining a spatialite database. You can easily see the structure of the tables and data contents using point and click functions, many of which construct common SQL queries, or craft your own SQL queries.

  1. Open the Spatialite GUI by selecting Geospatial->Databases->Spatialite GUI.

  2. Select File->Connecting an existing SQLite DB

  3. Browse to the /home/user/data/spatialite directory and choose trento.sqlite.

    ../_images/spatialite-gui-trento.png
  4. Right click on the MunicipalHallsView table and select “Show Columns”

    ../_images/spatialite-gui-columns.png
  5. You will notice the display broken into 3 areas:

    • The left panel displays the database hierarchy, such as a list of tables, and columns within the table. Right click on elements of the left panel to select from a list of common database actions.
    • The top right panel shows SQL for the action selected. You can enter your own customised SQL into this panel.
    • The bottom right panel shows the results of the SQL query.
  6. Right Click on the MunicipalHalls table and select “Edit table rows”. Notice the SQL query which has been created for you in the top right pane, and results in the bottom right.:

    SELECT ROWID, "PK_UID", "AREA", "PERIMETER", "COMU", "Geometry"
        FROM "MunicipalHalls"
        ORDER BY ROWID
    

Run an SQL query

  1. Now let’s try tweaking this SQL statement to get NOME and (Lat,Long) for only the NOME_PROV fields include “BRESCIA”, this time using the MunicipalHallsView. In the upper right SQL pane type:

    SELECT NOME, X(Geometry) AS Longitude, Y(Geometry) AS Latitude
        FROM "MunicipalHallsView"
        WHERE NOME_PROV LIKE "BRESCIA";
    
  2. Click the “Execute SQL” button at the right, and see the results in the bottom right pane.

    ../_images/spatialite-gui-select.png

Run spatialite from the command line

Users needing to script or automate queries will learn the advantages of working with a spatialite database from the command line interface. In this example, we will load a shapefile, and search for schools which are near highway 42.

  1. Before working from the command line, we need to open a terminal window: System Tools -> LXTerminal.

  2. In the terminal open a sample database with spatialite by typing:

    spatialite /home/user/data/spatialite/trento.sqlite
    

    Helpful commands from the command line:

    .help
    .tables
    .quit
    

Create a new spatialite database and load a shapefile

  1. Let’s create a new, empty spatialite database, and load two shapefiles from the north_carolina dataset:

    user@osgeo-6:~$ spatialite test.sqlite
    SpatiaLite version ..: 5.0.0    Supported Extensions:
        - 'VirtualShape'        [direct Shapefile access]
        - 'VirtualDbf'          [direct DBF access]
        - 'VirtualText'         [direct CSV/TXT access]
        - 'VirtualGeoJSON'              [direct GeoJSON access]
        - 'VirtualXL'           [direct XLS access]
        - 'VirtualNetwork'      [Dijkstra shortest path - obsolete]
        - 'RTree'               [Spatial Index - R*Tree]
        - 'MbrCache'            [Spatial Index - MBR cache]
        - 'VirtualFDO'          [FDO-OGR interoperability]
        - 'VirtualBBox'         [BoundingBox tables]
        - 'VirtualSpatialIndex' [R*Tree metahandler]
        - 'VirtualElementary'   [ElemGeoms metahandler]
        - 'VirtualRouting'      [Dijkstra shortest path - advanced]
        - 'VirtualKNN'  [K-Nearest Neighbors metahandler]
        - 'VirtualGPKG' [OGC GeoPackage interoperability]
        - 'SpatiaLite'          [Spatial SQL - OGC]
        - 'VirtualXPath'        [XML Path Language - XPath]
    PROJ version ........: Rel. 6.3.1, February 10th, 2020
    GEOS version ........: 3.9.0-CAPI-1.16.2
    RTTOPO version ......: 1.1.0
    TARGET CPU ..........: x86_64-linux-gnu
    the SPATIAL_REF_SYS table already contains some row(s)
    SQLite version ......: 3.31.1
    Enter ".help" for instructions
    SQLite version 3.31.1 2020-01-27 19:55:54
    Enter ".help" for instructions
    Enter SQL statements terminated with a ";"
    spatialite>
    spatialite> .loadshp data/north_carolina/shape/schools_wake schools utf-8 3358
    spatialite> .loadshp data/north_carolina/shape/roadsmajor roads utf-8 3358
    

    Note the format of the .loadshp command: first the shapefile without the .shp extension, then the name of the new spatialite table, next the character encoding, and finally the EPSG code of the shapefile’s CRS.

  2. Now we’ll query for schools near to highway 42.:

    spatialite> SELECT s.NAMESHORT, s.ADDRNUMBER, s.ADDRROOT
         ...> FROM schools AS s, roads AS r
         ...> WHERE r.ROAD_NAME = "NC-42" AND
         ...> ST_Distance(s.Geometry, r.Geometry) < 1000;
    FUQUAY-VARINA|6600|Johnson Pond Rd
    WILLOW SPRINGS|6800|Dwight Rowland Rd
    FUQUAY-VARINA|109|N Ennis St
    LINCOLN HEIGHTS|307|Bridge St
    
  3. Finally, we output the query to a “comma separated values” text file “schools_rt42.txt” with the following commands:

    spatialite> .mode csv
    spatialite> .output "schools_rt42.txt"
    spatialite> SELECT s.NAMESHORT, s.ADDRNUMBER, s.ADDRROOT
        ...> FROM schools AS s, roads AS r
        ...> WHERE r.ROAD_NAME = "NC-42" AND
        ...> ST_Distance(s.Geometry, r.Geometry) < 1000;
    spatialite>.q
    

Things to try

Here are some additional challenges for you to try:

  • Inspect geometries with spatialite-gui
  • Open and edit SpatiaLite layers in QGIS

What next?

To learn more about SpatiaLite, a starting point is the SpatiaLite project page.

Be sure to visit the tutorial Spatialite cookbook