User Tools

Site Tools


wiki:spatialite

Hands on SpatiaLite

  • Create a spatiaLite database
    • Import a shp file
    • describe your data
    • check layer was created in qgis
    • perform sql quary on data
    • load a non georeferenced table from a csv file
    • create the join between the two tables
    • Import a georeferenced csv table with x,y locations of cities in Ireland
    • perform query
    • perform spatial sql query


| basic.sql
# SPATIALITE script FROM the Spatial-Ecology.net course
# OPEN a bash terminal AND run the following
# 	IF vector_database_cli.sqlite does NOT exist it will be created
#	IF vector_database_cli.sqlite EXISTS, it will OPEN the DATABASE
 
spatialite vector_database_cli.sqlite
 
# you should now be IN the spatialite command line interface AND you should see the following:
#SpatiaLite version ..: 4.1.1    Supported Extensions:
#        - 'VirtualShape'        [direct Shapefile access]
#        - 'VirtualDbf'          [direct DBF access]
#        - 'VirtualXL'           [direct XLS access]
#        - 'VirtualText'         [direct CSV/TXT access]
#        - 'VirtualNetwork'      [Dijkstra shortest path]
#        - 'RTree'               [Spatial INDEX - R*Tree]
#        - 'MbrCache'            [Spatial INDEX - MBR cache]
#        - 'VirtualSpatialIndex' [R*Tree metahandler]
#        - 'VirtualXPath'        [XML Path LANGUAGE - XPath]
#        - 'VirtualFDO'          [FDO-OGR interoperability]
#        - 'SpatiaLite'          [Spatial SQL - OGC]
#PROJ.4 version ......: Rel. 4.8.0, 6 March 2012
#GEOS version ........: 3.4.2-CAPI-1.8.2 r3921
#SQLite version ......: 3.8.7.1
#Enter ".help" FOR instructions
#SQLite version 3.8.7.1 2014-10-29 13:59:56
#Enter ".help" FOR instructions
#Enter SQL statements TERMINATED WITH a ";"
#spatialite>
 
#all OF the following commands should be typed AS they are inside the spatialite cli
#print cli help
.help
 
#print available TABLES
.tables
 
#import shp 
#syntax:
# .loadshp shpfilename layername encoding epsg_code
.loadshp nuts3_100k2006_ireland nuts3_ie utf-8 3035
 
#check layer was created 
.tables
 
#describe the TABLE
pragma table_info(nuts3_ie)
 
#check layer was created IN qgis
qgis vector_database_cli.sqlite #(run FROM bash prompt)
 
#select everything FROM nuts3_ie TABLE
SELECT * FROM nuts3_ie;
 
#select everything FROM nuts3_ie TABLE ORDER alphabetically
SELECT * FROM nuts3_ie ORDER BY nuts_id;
 
#select based ON a wildcard (e.g. everything starting WITH 'IE'
SELECT * FROM nuts3_ie WHERE nuts_id LIKE 'IE%';
 
#count the NUMBER OF records FROM above query
SELECT COUNT(*) FROM nuts3_ie WHERE nuts_id LIKE 'IE%';
 
#use the ST_ARea TO calculate the area USING the Geometry COLUMN
SELECT nuts_id, ST_Area(Geometry) FROM nuts3_ie; 
 
#use the ST_ARea TO calculate the area  IN hectares
SELECT nuts_id, ST_Area(Geometry)/10000 FROM nuts3_ie; 
 
#add a NEW FIELD TO calculate the area
ALTER TABLE nuts3_ie ADD COLUMN area_ha INTEGER;
 
#update the TABLE AND SET the area
UPDATE nuts3_ie SET area_ha = round(ST_Area(Geometry),0);
 
###
# EXERCISE 2 - JOINING ATTRIBUTES (CSV) TO VECTOR
###
 
#CREATE TABLE TO hold the CSV DATA
CREATE TABLE nuts3_forestarea (nuts_id TEXT, nonnullcells INTEGER, forest_area INTEGER);
 
#DEFINE CSV FIELD SEPARATOR
.sep ,
 
#import the csv USING the .import command
.import 'nuts3_forestarea.csv' nuts3_forestarea
 
#check the import was successful
SELECT * FROM nuts3_forestarea;
 
#notice that the header was included IN the import!
#we need TO remove this FROM the TABLE
DELETE FROM nuts3_forestarea WHERE nuts_id == 'nuts_id';
 
#create the JOIN BETWEEN the two TABLES
#N.B.:
# a. = nuts3_ie
# b. = nuts3_forestarea
SELECT a.nuts_id, a.area_ha, b.forest_area FROM nuts3_ie AS a,  nuts3_forestarea AS b  WHERE a.nuts_id = b.nuts_id;
 
#make this JOIN permanent AS a VIEW
CREATE VIEW v_nuts3_forestarea AS SELECT a.nuts_id, a.area_ha, a.Geometry, b.forest_area FROM nuts3_ie AS a,  nuts3_forestarea AS b WHERE a.nuts_id = b.nuts_id;
 
#register the VIEW IN the geometry COLUMNS so that you can VIEW it IN a GIS
INSERT INTO views_geometry_columns VALUES ('v_nuts3_forestarea', 'geometry', 'rowid', 'nuts3_ie', 'geometry', 1);
 
#view the VIEW IN qgis (note run this FROM bash!)
bash$>qgis vector_database_cli.sqlite
 
 
 
 
###
# EXERCISE 3 - IMPORT A CSV WITH X,Y locations OF cities IN Ireland
###
 
#create a TABLE TO hold the xy TABLE
CREATE TABLE towns (x INTEGER, y INTEGER, county TEXT, town TEXT, population INTEGER, male_population INTEGER, female_population INTEGER);
 
#import the towns CSV file
.import 'towns_ie.csv' towns 
 
#select ALL FROM towns
SELECT * FROM towns;
 
#note, it's not spatial!!
 
#Add a geometry column to the towns table
#note 29900 is the Irish National Grid projection (epsg:29900)
SELECT AddGeometryColumn('towns', 'Geometry', 29900, 'POINT', 1);
 
#set the Geometry type to Point 
UPDATE towns SET Geometry=MakePoint(x,y, 29900);
 
#check the output and print the geometry field and the SRID as TEXT
SELECT x, y, AsText(geometry), SRID(geometry) FROM towns;
 
#Let's ADD a NEW Geometry COLUMN FOR a different projection (e.g. epsg: 3035)
ALTER TABLE towns ADD COLUMN epsg3035 BLOB;
 
#Transform (reproject the geometry COLUMN)
UPDATE towns SET epsg3035 = TRANSFORM(Geometry, 3035);
 
#Register the NEW geometry
SELECT RECovergeometrycolumn('towns', 'epsg3035', 3035, 'POINT', 1);
 
#view the VIEW IN qgis (note run this FROM bash!)
#you should see two towns layers, WITH the NEW layers AS towns(3035)
bash$>qgis vector_database_cli.sqlite
 
#in spatialite
#return the coordinates IN the two projections AND the projection (SRID)
SELECT AsText(geometry), SRID(geometry), AsText(epsg3035), SRID(3035) FROM towns;
 
 
###
# EXERCISE 4 - SOME SPATIAL SQL
###
#calculate the distance FROM the towns DATABASE AND each polygon centroid WHERE the town IS called 'Wexford'
SELECT a.town, b.nuts_id,  ST_Distance(a.geometry, ST_Centroid(b.geometry)) AS dist FROM  towns AS a, nuts3_ie AS b WHERE a.town = 'Wexford';
 
#select the points IN polygon WHERE the nuts_id = 'IE023'
SELECT a.town FROM towns AS a, nuts3_ie AS b WHERE ST_Within(Transform(a.geometry, 3035), b.geometry) AND b.nuts_id = 'IE023';
 
 
 
##Exiting Spatialite CLI
.quit 
# OR !!
CTRL+D
wiki/spatialite.txt · Last modified: 2017/12/05 22:53 (external edit)