User Tools

Site Tools


wiki:spatialite

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

wiki:spatialite [2017/12/05 22:53] (current)
Line 1: Line 1:
 +====== 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
 +\\
 +
 +
 +<code sql | 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 ​
 +
 +</​code>​
  
wiki/spatialite.txt ยท Last modified: 2017/12/05 22:53 (external edit)