SpatiaLite Cookbook Topics (Version 5.0.0) Quick reference list to the Cookbook pages

General usage notes: for all Cookbook pages:

Areas with Sql-Queries:

SELECT
 "Mouse cursor will be shown as a 'pointer'" AS Mouse_Cursor,
 "With a 'DblClick', the Sql-Statement will be selected" AS Text_Select,
 "-> and copied to the Clipbord" AS Text_Copy,
 "--> which then can be Pasted into spatialite_gui" AS Text_Paste;

---

Chapter 01: Kitchen tools and cooking techniques

Topics Topic page quick technical intro read getting started [installing the software] Download of the ISTAT (the Italian Census Bureau) dataset Download of the cities-1000 (GeoNames) dataset Download of the Railways vs Communities (rearranged work based on OSM) dataset --- building your first Spatial Database Creation of the Database 2011.Italy.db, with TABLEs com2011_s,prov2011_s and reg2011_s Creation of the TABLE cities1000 --- what's a Shapefile ? read what's a Virtual Shapefile ? (and Virtual Tables ...) read what's a Charset Encoding ?? (and why must I deal with such nasty things ?) read what's this SRID stuff ? ... I've never heard this term before now ... read executing your first SQL queries. read basics about SQL queries read understanding aggregate functions read

---

Chapter 02: Commonly used ingredients

Topics Topic page your first Spatial SQL queries read more about Spatial SQL: WKT and WKB read Spatial MetaData Tables read viewing SpatiaLite layers in QGIS read

---

Chapter 03: Family cooking

Topics Topic page recipe #1: creating a well designed DB read recipe #2: your first JOIN queries read recipe #3: more about JOIN read recipe #4: about VIEWs read recipe #5: creating a new table (and related paraphernalia) read recipe #6: creating a new Geometry column read recipe #7: Insert, Update and Delete read recipe #8: understanding Constraints read recipe #9: ACIDity: understanding Transactions read recipe #10: wonderful R*Tree Spatial Index read

---

Chapter 04: Haute cuisine

Topics Topic page recipe #11: Guinness Book of Records read recipe #12: Neighbours read recipe #13: Isolated Islands read recipe #14: Populated Places vs Communities read recipe #15: Tightly bounded Populated Places read recipe #16: Railways vs Communities read recipe #16a: SpatialIndex as BoundingBox read recipe #17: Railways vs Populated Places read recipe #18: Railway Zones as Buffers read recipe #19: merging Communities into Provinces and so on ... Creation of the VIEW admin_cities Creation of the TABLE admin_provinces Creation of the TABLE admin_regions Creation of the TABLE admin_countries --- recipe #20: Spatial Views read recipe #20: Spatial Views - Writable read

---

Chapter 05: Desserts, spirits, tea and coffee

Topics Topic page System level performace hints read Importing/Exporting Shapefiles (DBF, TXT ...) read Language bindings: [C/C++, Java, Python, PHP ...] read Fine dining experience: Chez Dijkstra read

---

Chapter 06: Cooking basics, which are useful to know

Topics Topic page An introduction to SpatiaLite read The OpenGIS geometry model The geometry class hierarchy class Geometry class Point class Curve class LineString class Surface class Polygon class GeometryCollection class MultiPoint class MultiCurve class MultiLineString class MultiSurface class MultiPolygon read Supported Spatial Data Formats Well-Known Text (WKT) format Well-Known Binary (WKB) format Internal BLOB format read Coordinate Reference Systems; the EPSG dataset read Spatial Metadata support read The VirtualShape module read The VirtualText module Importing the GeoNames dataset shapes_all_low.txt with VirtualText --- Spatial Indexing support read Mbr Caching support read SpatiaLite conformance and compatibility read Dynamically loading SpatiaLite as an extension module read

Chapter 07: Exotic Cooking aspects, which are often forgotten (or avoided)

Topics Topic page Geodesic background knowlage, needed for Geodesic functions read

Chapter 08: Background Information and Sql-HowTo's

Topics Topic page Background Information about: Aggregate, Functions, with links to SQLite command documentation Non-Aggregate Functions, with links to SQLite command documentation Planning a SpatialIndex-Query Using GROUP BY to find Duplicate Entries --- Administration Tasks: Adminstration: Introduction Adminstration: Basics When does a TABLE become a SpatialTable ? When does a VIEW become a SpatialView ? Sample Triggers for SpatialView Adminstration: The Renaming of a TABLE or COLUMN SQLite: Background Information SQLite: CREATE VIEW syntax explicitly declaring columns Spatialite: Background Information --- How to Sql samples: Extract a geometry from a portions of another geometries: San Marino Create a geometry of the Bounding Box as Exterior-Ring and geometry as Interior-Ring Search for Degree-Points, within meter a distance using the SpatialIndex with ST_Buffer and PtDistWithin --- How to Topology: Understanding TopoGeo_FromGeoTableExt dustbin results ---

Other topics of interest, for which no recipes (yet) exist

Topics Topic page Spatialite Tools (installing): general (overview) Spatialite Convert Spatialite DEM Spatialite OSM-Tools Spatialite XML-Tools --- Dynamically loading SpatiaLite as an extension module read Correspondences between SQL Functions and DE-9IM Intersection Matrices read Affine Transformations read SQL functions supporting Circles and Ellipses read using CloneTable() read Importing and exporting geospatial data as CAD drawing files read GPX tracks read Ground Control Points read ISO Metadata read ISO Topology read SRID Inspection read ST_Cutter read SQL Procedures, Stored Procedures and Stored Variables read Tessellation-related SQL functions read about VirtualElementary read Loading geospatial data from a WFS datasource read XmlBlob - Quick start read about VirtualPG (Version 2) read VirtualKNN: a quick intro read Separate complex geometries into distinct elements: VirtualElementary read

		Authors: Alessandro Furieri (first and second edition 3.0, 5.0) a.furieri@lqt.it Mark Johnson (second edition 5.0) mj10777@googlemail.com
		This work is licensed under the Attribution-ShareAlike 4.0 International license.
		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.