SpatiaLite

https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/
Admin
SpatiaLite is a spatial database extension for SQLite that enables the storage, indexing, and...
Tokens:70,923
Snippets:822
Trust Score:9.3
Update:5 months ago
Show doc for...
Tokens:
Raw
### WKT Example: LineStringZ (3D)

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.02.html

Provides the WKT format for a 3D linestring, composed of PointZ vertices.

```sql
LINESTRINGZ(15.21 57.58 0.31, 15.81 57.12 0.33)
```

--------------------------------

### WKT Example: GeometryCollectionZ (3D)

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.02.html

Demonstrates the WKT format for a 3D geometry collection, containing PointZ and LineStringZ.

```sql
GEOMETRYCOLLECTIONZ(POINTZ(13.21 47.21 0.21), LINESTRINGZ(15.21 57.58 0.31, 15.81 57.12 0.33))
```

--------------------------------

### WKT Example: MultiPointZ (3D)

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.02.html

Illustrates the WKT format for a 3D multipoint collection, using PointZ vertices.

```sql
MULTIPOINTZ(15.21 57.58 0.31, 15.81 57.12 0.33)
```

--------------------------------

### WKT Example: Geometry Collection

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.02.html

Illustrates a WKT representation of a GEOMETRYCOLLECTION containing multiple points and a linestring.

```sql
GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(4 5, 6 7, 8 9), POINT(30 30))
```

--------------------------------

### WKT Example: PointZ (3D)

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.02.html

Shows the WKT format for a 3D point, including X, Y, and Z (height/dem) values.

```sql
POINTZ(13.21 47.21 0.21)
```

--------------------------------

### Install SpatiaLite Tools

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.tools.html

Steps to clone the spatialite-tools repository, configure, and compile the command-line utilities. Ensure you are in the correct directory after cloning.

```bash
fossil clone https://www.gaia-gis.it/fossil/spatialite-tools spatialite-tools.fossil
mkdir spatialite-tools
cd spatialite-tools
fossil open spatialite-tools.fossil
./configure --help > ../help.configure.spatialite-tools.txt
./configure
make
```

--------------------------------

### Type Casting Examples

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.02.html

Demonstrates how to cast geometries between different types using SpatiaLite functions.

```APIDOC
## Type Casting Functions (e.g., CastToMultiLineString, CastToXYZM, CastToXY)

### Description
These functions allow you to convert geometries from one type to another, useful for ensuring compatibility with column types or for data manipulation.

### Method
SQL Function

### Endpoint
N/A

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```sql
-- Cast a LINESTRING to MULTILINESTRING
SELECT ST_AsText(CastToMultiLineString(ST_GeomFromText('LINESTRING(1.2345 2.3456, 12.3456 23.4567)')));

-- Cast a POINT to POINT ZM
SELECT ST_AsText(CastToXYZM(ST_GeomFromText('POINT(1.2345 2.3456)')));

-- Cast a POINT ZM to POINT (XY)
SELECT ST_AsText(CastToXY(ST_GeomFromText('POINT ZM(1.2345 2.3456 10 20)')));
```

### Response
#### Success Response (200)
- **WKT String**: The Well-Known Text representation of the cast geometry.

#### Response Example
```
MULTILINESTRING((1.2345 2.3456, 12.3456 23.4567))
POINT ZM(1.2345 2.3456 0 0)
POINT(1.2345 2.3456)
```
```

--------------------------------

### WKT MultiPoint Example

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.06.html

Represents a collection of distinct points.

```WKT
MULTIPOINT(1234.56 6543.21, 1 2, 3 4, 65.21 124.78)
```

--------------------------------

### WKT Example: PointZM (3D + Measure)

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.02.html

Shows the WKT format for a 3D point that also includes a measure value (M).

```sql
POINTZM(13.21 47.21 0.21 1000.0)
```

--------------------------------

### WKT MultiLineString Example

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.06.html

Represents a collection of one or more LineString objects.

```WKT
MULTILINESTRING((1 2, 3 4), (5 6, 7 8, 9 10), (11 12, 13 14))
```

--------------------------------

### WKT Example: MultiPointZM (3D + Measure)

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.02.html

Illustrates the WKT format for a 3D multipoint collection with measure values, using PointZM vertices.

```sql
MULTIPOINTZM(15.21 57.58 0.31 1000.0, 15.81 57.12 0.33 1100.0)
```

--------------------------------

### WKT Example: PointM (2D + Measure)

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.02.html

Shows the WKT format for a 2D point with a measure value (M), not related to 3D.

```sql
POINTM(13.21 47.21 1000.0)
```

--------------------------------

### WKT Example: LineStringZM (3D + Measure)

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.02.html

Provides the WKT format for a 3D linestring with measure values, composed of PointZM vertices.

```sql
LINESTRINGZM(15.21 57.58 0.31 1000.0, 15.81 57.12 0.33 1100.0)
```

--------------------------------

### WKT Example: MultiPointM (2D + Measure)

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.02.html

Illustrates the WKT format for a 2D multipoint collection with measure values, using PointM vertices.

```sql
MULTIPOINTM(15.21 57.58 1000.0, 15.81 57.12 1100.0)
```

--------------------------------

### WKT Point Example

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.06.html

Represents a single point with X and Y coordinates. Coordinates are specified without a comma separator.

```WKT
POINT(123.45 543.21)
```

--------------------------------

### WKT Example: GeometryCollectionM (2D + Measure)

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.02.html

Demonstrates the WKT format for a 2D geometry collection with measure values, containing PointM and LineStringM.

```sql
GEOMETRYCOLLECTIONM(POINTM(13.21 47.21 1000.0), LINESTRINGM(15.21 57.58 1000.0, 15.81 57.12 1100.0))
```

--------------------------------

### WKT LineString Example

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.06.html

Represents a line defined by a sequence of points. Point coordinate pairs are separated by commas.

```WKT
LINESTRING(100.0 200.0, 201.5 102.5, 1234.56 123.89)
```

--------------------------------

### WKT Example: LineStringM (2D + Measure)

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.02.html

Provides the WKT format for a 2D linestring with measure values, composed of PointM vertices.

```sql
LINESTRINGM(15.21 57.58 1000.0, 15.81 57.12 1100.0)
```

--------------------------------

### WKT Example: Polygon with Interior Ring

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.02.html

Demonstrates a WKT representation of a polygon that includes an interior ring, showcasing complex polygon structures.

```sql
((0 0,10 20,30 40,0 0),(1 1,2 2,3 3,1 1))
```

--------------------------------

### Get Index Information with PRAGMA index_info

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.03.html

Use PRAGMA index_info to inspect the columns that constitute a specific index.

```SQL
PRAGMA index_info(idx_people_name);
```

--------------------------------

### WKT MultiPolygon Example

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.06.html

Represents a collection of two or more Polygon objects. The example shows one polygon with an interior ring and another with only an exterior ring.

```WKT
MULTIPOLYGON(((0 0,10 20,30 40,0 0),(1 1,2 2,3 3,1 1)),((100 100,110 110,120 120,100 100)))
```

--------------------------------

### SQL Query Example for Mouse Cursor and Text Selection

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/index.html

This SQL query demonstrates how to alias strings to describe mouse cursor behavior and text selection/copy/paste actions within a GUI environment. It's useful for understanding UI feedback mechanisms.

```sql
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;
```

--------------------------------

### Create Cities Table with Data Types

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.01.html

Define a table schema for city data, including various data types like INTEGER, TEXT, and DOUBLE. This example demonstrates creating a table with auto-incrementing primary key, geoname IDs, names, coordinates, and feature classifications. Comments indicate the intended data type and purpose of each column.

```sql
CREATE TABLE IF NOT EXISTS cities1000
(
 -- integer id as primary key
 id_rowid INTEGER PRIMARY KEY AUTOINCREMENT,
 -- integer id of record in geonames database
 id_geoname INTEGER DEFAULT 0,
 -- name of geographical point
 name TEXT DEFAULT '',
 -- name of geographical point in plain ascii characters (sortable)
 name_sort TEXT DEFAULT '',
 -- alternatenames, comma separated, ascii names automatically transliterated, convenience attribute from alternatename table
 name_alt TEXT DEFAULT '',
 -- latitude in decimal degrees (wgs84) [Y-Position]
 latitude DOUBLE DEFAULT 0,
 --longitude in decimal degrees (wgs84) [X-Position]
 longitude DOUBLE DEFAULT 0,
 -- feature class: see http://www.geonames.org/export/codes.html
 feature_class TEXT DEFAULT '',
 -- feature code: see http://www.geonames.org/export/codes.html
 feature_code TEXT DEFAULT '',
 -- country code: ISO-3166 2-letter country code
 country_code TEXT DEFAULT '',
 -- alternate country codes, comma separated, ISO-3166 2-letter country code,
 country_codes_alt TEXT DEFAULT '',
 -- fipscode (subject to change to iso code), see exceptions below, see file admin1Codes.txt for display names of this code
 admin1_code TEXT DEFAULT ''
);
```

--------------------------------

### Get Geometry Type

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.02.html

Retrieves the geometry type from an internal BLOB Geometry value.

```APIDOC
## ST_GeometryType()

### Description
Returns the Geometry Type from the given internal BLOB Geometry value.

### Method
SQL Function

### Endpoint
N/A

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```sql
SELECT ST_GeometryType(ST_GeomFromText('POINT(1.2345 2.3456)'));
```

### Response
#### Success Response (200)
- **Geometry Type**: The type of the geometry (e.g., POINT, POINT Z, POINT ZM).

#### Response Example
```
POINT
```
```

--------------------------------

### Get SRID

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.02.html

Retrieves the Spatial Reference Identifier (SRID) from an internal BLOB Geometry value.

```APIDOC
## ST_Srid()

### Description
Returns the SRID from the given internal BLOB Geometry value. If unspecified, -1 is assumed.

### Method
SQL Function

### Endpoint
N/A

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```sql
SELECT ST_Srid(ST_GeomFromText('POINT(1.2345 2.3456)', 4326));
```

### Response
#### Success Response (200)
- **SRID**: The Spatial Reference Identifier.

#### Response Example
```
4326
```
```

--------------------------------

### Get Geometry Type from WKT

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.02.html

Use ST_GeomFromText to create an internal geometry from WKT, then ST_GeometryType to determine its type.

```sql
SELECT
 ST_GeometryType(ST_GeomFromText('POINT(1.2345 2.3456)'));
```

```sql
SELECT
 ST_GeometryType(ST_GeomFromText('POINTZ(1.2345 2.3456 10)'));
```

```sql
SELECT
 ST_GeometryType(ST_GeomFromText('POINT ZM(1.2345 2.3456 10 20)'));
```

--------------------------------

### Create a Simple Table

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.03.html

Use this statement to create a basic table with specified columns and data types. Ensure data types are appropriate for the intended data.

```sql
CREATE TABLE people (
 first_name TEXT,
 last_name TEXT,
 age INTEGER,
 gender TEXT,
 phone TEXT
);
```

--------------------------------

### Check Text File (Linux)

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.01.html

Use the 'head' command in Linux to preview the first 10 lines of a text file, saving the output to 'check_text.txt' for easier inspection before importing.

```bash
head cities1000.txt > check_text.txt
```

--------------------------------

### Create cities_test Table

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.03.html

Defines a table named 'cities_test' with columns for ID, name, latitude, longitude, and a geometry column.

```sql
CREATE TABLE cities_test  
 (  
 id_geoname INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,  
 name TEXT DEFAULT '',  
 -- Y-Position,  
 latitude DOUBLE DEFAULT 0,  
 -- X-Position,  
 longitude DOUBLE DEFAULT 0  
 );
```

--------------------------------

### Attempt to Set Page Size

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.05.html

You can attempt to set a new page size using PRAGMA, but it requires a database reorganization (VACUUM) to take effect.

```sql
PRAGMA page_size = 4096;
-- 
PRAGMA page_size;
-- 1024
```

--------------------------------

### Rename a Table

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.03.html

Change the name of an existing table using the ALTER TABLE RENAME TO statement. This is supported starting with SQLite 3.25.

```sql
ALTER TABLE people2
 RENAME TO people_ok;
```

--------------------------------

### Spatialite DEM Tool: Display Help

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.tools.html

Displays the help message for the spatialite_dem tool, outlining its available options and usage.

```bash
spatialite_dem --help
```

--------------------------------

### WKT Polygon without Interior Ring Example

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.06.html

Represents a polygon with only an exterior boundary. Double parentheses are used to denote the exterior ring.

```WKT
POLYGON((101.23 171.82, 201.32 101.5, 215.7 201.953, 101.23 171.82))
```

--------------------------------

### Query Current Page Size and Count

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.05.html

Use PRAGMA statements to check the current page size, total allocated pages, and free pages in the database.

```sql
PRAGMA page_size;
-- 1024
PRAGMA page_count;
-- 31850
PRAGMA freelist_count;
-- 12326
```

--------------------------------

### Insert with Malformed Geometry

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.03.html

Example of attempting to insert data with a malformed WKT expression. ST_GeomFromText() will return NULL, potentially violating NOT NULL constraints.

```sql
INSERT INTO mothers (first_name, last_name, home_location)  
 VALUES('Pilar', 'Fernandez',
 ST_GeomFromText('POINT(4.7 45.6), 4326'));
```

--------------------------------

### Initialize SpatiaLite Metadata Tables

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.02.html

Call InitSpatialMetaData() immediately after creating a new database and before using any other Spatial SQL functions. This function creates and populates the necessary metadata tables. It is safe to call multiple times, but subsequent calls have no effect if the tables already exist. Tools like spatialite_gui handle this automatically.

```sql
SELECT InitSpatialMetaData();
```

--------------------------------

### Update Latitude by Adding Value

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.03.html

Increases the latitude value of rows by 45.0. This example is incomplete as it lacks a WHERE clause, implying it would affect all rows.

```sql
UPDATE cities_test SET  
  latitude = latitude + 45.0;
```

--------------------------------

### Create a new table for geometry data

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.03.html

First, create the table with standard columns before adding the geometry column. This ensures proper structure for spatial data.

```sql
CREATE TABLE cities_test  
 (  
 id_geoname INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 name TEXT DEFAULT '',  
 -- Y-Position,  
 latitude DOUBLE DEFAULT 0,  
 -- X-Position,  
 longitude DOUBLE DEFAULT 0  
 );
```

--------------------------------

### Get SRID from Geometry

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.02.html

Use ST_GeomFromText to create an internal geometry, then ST_Srid to retrieve its Spatial Reference Identifier (SRID). If not specified, -1 is assumed.

```sql
SELECT
 ST_Srid(ST_GeomFromText('POINT(1.2345 2.3456)'));
```

```sql
SELECT
 ST_Srid(ST_GeomFromText('POINT(1.2345 2.3456)', 4326));
```

--------------------------------

### Drop and Create Table

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.adminstration.html

Used to ensure a clean slate before defining a new table. It first drops any existing view or table with the same name, then creates a new table with specified columns.

```sql
DROP VIEW IF EXISTS admin_cities;
DROP TABLE IF EXISTS admin_cities;
```

```sql
CREATE TABLE admin_cities  
(
  -- the id of the City  
  id_city INTEGER NOT NULL PRIMARY KEY,
  -- the name of the City  
  name_city TEXT NOT NULL,
  -- Population of City, as whole persons  
  population_city INTEGER DEFAULT 0,
  -- id of the Province the City belongs to  
  id_province INTEGER DEFAULT 0,
  -- name of the Province the City belongs to  
  name_province TEXT DEFAULT '',
  -- The Car Plate of the Province the City belongs to  
  car_plate_code TEXT DEFAULT '',
  -- id of the Region the Province belongs to  
  id_region INTEGER DEFAULT 0,
  -- name of the Region the Province belongs to  
  name_region TEXT DEFAULT '',
  -- id of Country the Region belongs to [default: 39, Italy]  
  id_country INTEGER DEFAULT 39,
  -- name of Country the Region belongs to [default: Italy]  
  name_country TEXT DEFAULT 'Italy'
);
```

--------------------------------

### Convert WKT to Hexadecimal WKB

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.02.html

Use ST_GeomFromText to convert a WKT string to an internal geometry representation, then Hex() to get its hexadecimal WKB string.

```sql
SELECT
 Hex(ST_GeomFromText('POINT(1.2345 2.3456)'));
```

--------------------------------

### Convert Hexadecimal WKB to Internal Geometry and then to WKT

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.02.html

Use ST_GeomFromWKB to convert a hexadecimal WKB string into an internal geometry representation, and then ST_AsText to get the WKT string.

```sql
SELECT
 ST_AsText(ST_GeomFromWKB(x'01010000008D976E1283C0F33F16FBCBEEC9C30240'));
```

--------------------------------

### Create a SQL View

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.03.html

Defines a persistent, read-only view named 'view_community' by joining communities, provinces, and regions tables. This view simplifies querying community data with associated province and region information.

```sql
CREATE VIEW view_community AS
 SELECT
 c.community_id AS community_id,
 c.community_name AS community_name,
 c.population AS population,
 p.province_id AS province_id,
 p.province_name AS province_name,
 p.car_plate_code AS car_plate_code,
 r.region_id AS region_id,
 r.region_name AS region_name,
 c.geometry AS geometry
 FROM communities AS c
 JOIN provinces AS p ON (c.province_id = p.province_id)
 JOIN regions AS r ON (p.region_id = r.region_id);
```

--------------------------------

### Querying Failing Geometry Details

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.howto_topology.html

This SQL query retrieves detailed information about a specific failing edge, including its source row ID, name, and the faces it starts and ends in.

```sql
SELECT  
 'A new edge for ['||  
 (SELECT 'id_rowid='||id_rowid||', name['||name||']]' FROM topology_admin_borders_topofeatures_1 WHERE (id_rowid=19))||  
 ' and starts in face ['||  
 (SELECT 'face_id='||topo_feature.face_id||', id_rowid='||layer_feature.id_rowid||', name['||layer_feature.name||']]' FROM topology_admin_borders_topofeatures_1 layer_feature  
```

--------------------------------

### Create an Index

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.03.html

Create an index on a table column to improve query performance. Specify the index name and the table/column to index.

```sql
CREATE INDEX idx_people_phone ON people_ok (phone);
```

--------------------------------

### Drop Existing admin_regions Table if Present

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.04.html

Removes the 'admin_regions' table if it already exists in the database. This is typically done before creating a new version of the table to ensure a clean setup.

```SQL
DROP TABLE IF EXISTS admin_regions;
```

--------------------------------

### Create Spatial View with Sample Data

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.adminstration.html

Defines a spatial view named 'middle_earth_farthings' based on the 'middle_earth_polygons' table. This view filters for polygons with an admin_level of 4.

```sql
DROP VIEW IF  EXISTS 'middle_earth_farthings';
CREATE VIEW IF NOT EXISTS 'middle_earth_farthings'
(id_rowid,id_admin,name,admin_level,valid_since,valid_until,id_belongs_to,belongs_to_01,
id_belongs_to_02,belongs_to_02,order_selected, rule_type,rule_text,meters_length, meters_area,notes,text,eur_polygon) AS
 SELECT
  id_rowid,id_admin,name,admin_level,valid_since,valid_until,id_belongs_to,belongs_to_01,
  id_belongs_to_02,belongs_to_02,order_selected, rule_type,rule_text,meters_length, meters_area,notes,text,eur_polygon
 FROM "middle_earth_polygons"
 WHERE
 ( -- 4=farthings, counties, provinces
  (admin_level IN (4))
 )
 ORDER BY name;
```

--------------------------------

### Reorganize Database with VACUUM

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.05.html

The VACUUM command reorganizes the entire database, applies structural changes like new page sizes, and compacts the database by discarding unused pages.

```sql
VACUUM;
-- 
PRAGMA page_size;
-- 4096
PRAGMA page_count;
-- 5197
PRAGMA freelist_count;
-- 0
```

--------------------------------

### WKT Polygon with Interior Ring Example

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.06.html

Represents a polygon with an exterior boundary and one or more interior boundaries (holes). Each ring must be closed, meaning the last point is identical to the first.

```WKT
POLYGON((10 10, 20 10, 20 20, 10 20, 10 10),(13 13, 17 13, 17 17, 13 17, 13 13))
```

--------------------------------

### Type Casting for Multi-part Geometries

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.02.html

Demonstrates how to use type casting functions like CastToMultiLineString and CastToXYZM to convert geometries, which is useful when inserting single geometries into multi-part geometry columns.

```sql
SELECT
 ST_GeometryType(ST_GeomFromText('MULTIPOINT(1.2345 2.3456)'));
```

```sql
SELECT
 ST_AsText(CastToMultiLineString(ST_GeomFromText('LINESTRING(1.2345 2.3456, 12.3456 23.4567)')));
```

```sql
SELECT
 ST_AsText(CastToXYZM(ST_GeomFromText('POINT(1.2345 2.3456)')));
```

```sql
SELECT
 ST_AsText(CastToXY(ST_GeomFromText('POINT ZM(1.2345 2.3456 10 20)')));
```

--------------------------------

### SQL DISTINCT Clause Example

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.01.html

Use the DISTINCT clause to retrieve unique combinations of region and province codes. Note that DISTINCT removes duplicate rows and cannot perform calculations on them, unlike GROUP BY.

```sql
SELECT 
 DISTINCT cod_reg, cod_pro  
 FROM com2011_s  
 ORDER BY cod_reg, cod_pro;
```

--------------------------------

### Spatialite XML Tools: Collapse Database

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.tools.html

Simplifies a SpatiaLite database file that was previously created using spatialite_xml_load.

```bash
spatialite_xml_collapse
```

--------------------------------

### Project Point North in Spatialite

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.01.html

Projects a point 1000 meters due north of a starting point and returns the distance in WGS84 degrees for 1 Km. Ensure your geometry column is in WGS84.

```sql
SELECT ST_Distance(geom_wgs84,ST_Project(geom_wgs84,1000,0))
);
```

--------------------------------

### Start a Transaction with BEGIN

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.03.html

Use the BEGIN statement to initiate a transaction. All subsequent SQL statements will be executed within this transaction's context until it is committed or rolled back. SQLite does not support nested transactions.

```sql
BEGIN;
```

--------------------------------

### Populate Communities Table with Geometry Data

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.03.html

Insert data into the 'communities' table, including geometry, by selecting from the 'com2011_s' source table. Ensure that community names are not empty and order the results by name.

```sql
INSERT INTO communities  
 (community_id,community_name, population, province_id, geometry)
 SELECT  
 pro_com AS community_id,  
 comune AS community_name,  
 pop_2011 AS population,  
 cod_pro AS province_id,  
 geometry  
 FROM com2011_s  
 -- avoid empty names [1]  
 WHERE ( community_name <> '')  
 ORDER BY community_name;
```

--------------------------------

### Create a Virtual Text Table

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.01.html

Use the VirtualText extension to create a virtual table from a text file. This allows you to query text data as if it were a database table. Specify the file path, encoding, header presence, decimal separator, text separator, and field separator.

```sql
CREATE VIRTUAL TABLE virtual_cities1000 USING
VirtualText
(
 -- absolute or relative path leading to the textfile
 'cities1000.txt',
 -- charset encoding used by the textfile
 'UTF-8',
 -- does the first line contains column names [0=no, 1=yes]
 0,
 -- the decimal separator [POINT or COMMA]
 POINT,
 -- the text separator [NONE, SINGLEQUOTE or DOUBLEQUOTE]
 NONE,
 -- the field separator [TAB, ',', ':' or other character]
 TAB
);
```

--------------------------------

### Insert E-Class Railway Zones (10-20Km)

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.04.html

Inserts data for E-class zones by calculating the difference between a 20000.0 unit buffer and a 10000.0 unit buffer around railway geometries, creating the largest ring-shaped zone in this example.

```SQL
INSERT INTO railway_zones  
 (id, railway_name, zone_name, geometry)  
SELECT  
NULL, name,  
'E class [< 20Km]' AS zone_name,  
CastToMultiPolygon  
(
-- Create a POLYGON that surrounds the D class  
ST_Difference  
(
ST_Buffer(ST_Transform(geometry,32632), 20000.0),
ST_Buffer(ST_Transform(geometry,32632), 10000.0)
)
) AS geometry  
FROM railways;
```

--------------------------------

### Example of Renaming Columns and Aliasing in a Query

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.adminstration.html

This SQL snippet demonstrates how to alias columns and rename them within a query, specifically showing how 'position_x' can be aliased as 'longitude' and 'position_y' as 'latitude'. It also includes a WHERE clause for filtering results.

```sql
name AS name, "position_x" AS longitude,"position_y" AS latitude  
FROM populated_places  
WHERE name LIKE "roma,%"
```

--------------------------------

### Using TopoGeo_FromGeoTableExt with Dustbin Tables

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.howto_topology.html

This SQL function imports geometries from a non-topology table into a topology, creating dustbin tables to log errors and failing geometries.

```sql
SELECT TopoGeo_FromGeoTableExt  
 ( --topo-name to use  
 'topology_admin_borders',  
 -- db-prefex (of an ATTACHed Database,  
 -- if NULL=MAIN/local-table]  
 'db_import',  
 -- table-name of external (non-topo) TABLE  
 'middle_earth_farthings',  
 -- column-name of external TABLE  
 -- (NULL can be used of ONLY 1 Geometry,  
 -- otherwise column-name of geometry]  
 'eur_polygon',  
 -- name of the table intended to store PK values,  
 -- with error message  
 -- corresponding to features failing to be imported  
 'dustbin_middle_earth_farthings',  
 -- name of the Spatial View supporting  
 'dustbin_middle_earth_farthings_view',  
 -- lines-max-points  
 NULL,  
 -- lines-max-length  
 NULL,  
 -- tolerance [default=0]  
 0  
 );  
```

--------------------------------

### Import OSM Data with spatialite_osm_net

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.05.html

Use the spatialite_osm_net command-line tool to import OpenStreetMap data into a Spatialite database. The -m flag enables in-memory processing for faster imports.

```bash
spatialite_osm_net -o toscana.osm -d tuscany.sqlite -T tuscany -m
```

--------------------------------

### Insert with Geometry

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.03.html

Demonstrates inserting data with a POINT geometry. Ensure the SRID is correctly specified if required by constraints.

```sql
INSERT INTO mothers (first_name, last_name, home_location)  
 VALUES('Pilar', 'Fernandez',
 ST_GeomFromText('POINT(4.7 45.6)'));
```

--------------------------------

### Search for Points within Meter Distance using SpatialIndex

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.howto_sql.html

This SQL query searches for points within a specified meter distance from a starting point using SpatialIndex. It calculates distances in degrees and meters, and filters results based on spatial proximity and bounding box checks.

```sql
CvtToIndCh(distance_meters) AS indian_chains
-- aggregate-geometry input: 1 record (as MULTIPOINT) will be created
-- ST_Collect(geom_wsg84)
-- non-aggregate-geometry input: 1 record (as POINT), for each place within area will be created
-- CastToMulti(geom_wsg84)
FROM
(
-- source Sub-Query, bringing together what belongs together
SELECT
-- Project POINT 'distance_search_meters' meters due NORTH [0] of start_point, return distance in wsg84 degrees
ST_Distance(source_data.start_point,ST_Project(source_data.start_point,source_data.distance_search_meters,0)) AS distance_search_degrees,
-- calculate distance in degrees [returned result in CRS units]
(ST_Distance(source_data.source_geometry,source_data.start_point)) AS distance_degrees,
-- calculate distance in meters [precise (but slower) length computed from the Ellipsoid]
(ST_Length(MakeLine(source_data.source_geometry,source_data.start_point),1)) AS distance_meters,
-- include all the columns from 'source_data'
source_data.*
FROM
(
SELECT
-- source: meters and start POINT with alias
-- '20 miles from Dallas, Texas' AS distance_search_name,
-- CvtFromUsMi(20) AS distance_search_meters,
-- Position 31840.733235 meters and 19.784875 miles from Dallas, Texas
-- MakePoint(-96.8785,33.0637,4326) AS start_point
'32 Km from: Foro Romano, Roma' AS distance_search_name,
CvtFromKm(32) AS distance_search_meters,
MakePoint(12.484847,41.892464,4326) AS start_point,
-- '1600 Indian Chains from: Secretariat Building, New Delhi' AS distance_search_name,
-- CvtFromIndCh(1600) AS distance_search_meters,
-- MakePoint(77.205833,28.615,4326) AS start_point,
-- Expose columns to be used in result
source_table.geom_wsg84 AS source_geometry,
source_table.feature_class AS source_feature_class,
source_table.id_rowid AS source_id_rowid,
source_table.id_geoname AS source_id_geoname,
source_table.name AS source_name,
source_table.timezone_id AS source_timezone_id,
source_table.admin1_code AS source_admin1_code,
source_table.country_code AS source_country_code,
source_table.feature_class AS source_query_column,
"P" AS source_query_column_value,
-- source: table and geometry name, with alias: used for for SpatialIndex
-- countries contains: 11.817.314 rows [feature_class='P': 4.697.154]
-- 'countries' AS source_table_name,
'geom_wsg84' AS source_geometry_name,
-- cities1000 contains: 128.743 rows
'cities1000' AS source_table_name
FROM cities1000 AS source_table
) source_data
WHERE
(
-- return only POINTS inside circle created by ST_Buffer
source_data.source_id_rowid IN
(
-- query SpatialIndex: faster than calling a Spatial-Function for each geometry
SELECT ROWID FROM SpatialIndex WHERE
(
-- source table containg POINTs
(f_table_name = source_data.source_table_name) AND
-- geometry-column to use (only mandatory when the TABLE contains more than 1 geometry)
(f_geometry_column = source_data.source_geometry_name) AND
(search_frame =
-- SpatialIndex will search for the BoundingBox of the circle
ST_Buffer
(
-- create a circle with the center being the start-point
source_data.start_point,
-- the distance between the center and the edge in degrees
distance_search_degrees
)
)
) AND
-- needed for countries, unique in cities1000 [P=populated place]
(source_data.source_query_column IN (source_data.source_query_column_value)) AND
-- check if returned result from the SpatialIndex is within the circle (may be inside the BoundingBox, but outside of circle)
(PtDistWithin(source_data.start_point,source_data.source_geometry,source_data.distance_search_meters,1))
)
)
-- result of Sub-Query, with alias
) AS from_result
ORDER BY distance_meters ASC;
```

--------------------------------

### Inspect table structure after adding geometry column

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.03.html

Use PRAGMA table_info to verify the table structure and confirm that the new geometry column has been added correctly.

```sql
PRAGMA table_info(cities_test);
```

--------------------------------

### SQL Query for Spatial Search with Distance Calculation

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.howto_sql.html

This SQL query searches for cities within a specified distance from a starting point. It calculates distances in degrees and meters, and converts them to various units. The query uses CTEs to define search parameters and source data, and selects various calculated and source columns.

```sql
WITH from_result AS (
 SELECT
  -- source_data: search distance point as name
  'Search Cities within 10000 meters' AS distance_search_name,
  -- source_data: search distance in meters
  10000 AS distance_search_meters,
  -- search distance in degrees
  0.1 AS distance_search_degrees,
  -- cities1000: id_geoname
  T1.id_geoname AS source_id_geoname,
  -- cities1000: timezone_id
  T1.timezone_id AS source_timezone_id,
  -- cities1000: name
  T1.name AS source_name,
  -- cities1000: feature_class (as source_query_column)
  T1.feature_class AS source_query_column,
  -- cities1000: admin1_code
  T1.admin1_code AS source_admin1_code,
  -- cities1000: country_code
  T1.country_code AS source_country_code,
  -- distance in degrees
  ST_Distance(ST_Project(MakeLine(MakePoint(4.8857, 52.3779, 4326), T2.geometry), (distance_search_meters * 1000), 0), MakePoint(4.8857, 52.3779, 4326)) AS distance_degrees,
  -- distance in meters
  ST_Distance(T2.geometry, MakePoint(4.8857, 52.3779, 4326)) AS distance_meters,
  -- source_data: geometry-column
  T1.geometry AS source_geometry
 FROM
  cities1000 AS T1,
  cities1000 AS T2
 WHERE
  -- Spatial Index: ST_DWithin
  ST_DWithin(T1.geometry, MakePoint(4.8857, 52.3779, 4326), (distance_search_meters * 1000))
  AND T1.rowid != T2.rowid
),
from_result_with_distance AS (
 SELECT
  from_result.*,
  -- Spatialite specific function: convert to U.S. Statute Miles
  CvtToUsMi(from_result.distance_meters) AS us_statute_miles,
  -- Spatialite specific function: convert to Indian Chains
  CvtToIndCh(from_result.distance_meters) AS indian_chains
 FROM
  from_result
)
SELECT
 distance_search_name,
 distance_search_meters,
 distance_search_degrees,
 source_id_geoname,
 source_timezone_id,
 source_name,
 type,
 source_admin1_code,
 source_country_code,
 distance_degrees,
 distance_meters,
 (distance_meters / 1481) AS roman_miles,
 CvtToKm(distance_meters) AS kilometers,
 us_statute_miles,
 indian_chains
FROM
 from_result_with_distance
WHERE
 distance_meters <= distance_search_meters
ORDER BY
 distance_meters
LIMIT
 1000
```

--------------------------------

### Create admin_countries Table

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.04.html

This SQL script defines the structure for the admin_countries table, including columns for country identification, population, area, and counts of cities, provinces, and regions. It sets up primary keys and default values.

```SQL
BEGIN;  
 -- not being the first version of our masterpiece, remove previous version  
 DROP TABLE IF EXISTS admin_countries; 
 CREATE TABLE admin_countries  
 (  
 -- based on County calling (telephon) code  
 id_country INTEGER NOT NULL PRIMARY KEY,  
 name_country TEXT NOT NULL,  
 -- Population of Region, as whole persons  
 population_country INTEGER DEFAULT 0,  
 -- Area of Country, in meters  
 area_country DOUBLE DEFAULT 0,  
 -- Amount of Cities belonging to the Country,  
 city_count INTEGER DEFAULT 0,  
 -- Amount of Provinces belonging to the Country,  
 province_count INTEGER DEFAULT 0,  
 -- Amount of Regions belonging to the Country,  
 region_count INTEGER DEFAULT 0  
 );
```

--------------------------------

### Drop and Create admin_provinces Table

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.04.html

Initializes a transaction and removes any existing 'admin_provinces' table before creating a new one. This table aggregates city data to represent provinces.

```SQL
BEGIN;
 -- not being the first version of our masterpiece, remove previous version
 DROP TABLE IF EXISTS admin_provinces;
```

```SQL
CREATE TABLE admin_provinces AS
 SELECT
 id_province,
 name_province,
 car_plate_code,
 -- Population of Province, as whole persons
 sum(population_city) AS population_province,
 -- Area of Province, in meters
 ST_Area(ST_Union(geom_city)) AS area_province,
 -- Amount of Cities belonging to the Province,
 count(id_city) AS city_count,
 -- id of Region the Province belongs to id_region,
 --name of Region the Province belongs to name_region,
 -- Build outer Boundry of Province, based on City-Geometries and stored as MULTIPOLYGON
 CastToMultiPolygon(ST_Union(geom_city)) AS geom_province
 FROM admin_cities
 -- needed for aggregate functions: count, sum, ST_Area and ST_Union
 GROUP BY id_province;
```

--------------------------------

### Import DBF to SpatiaLite Table using spatialite_tool

Source: https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.05.html

Import a DBF file into a SpatiaLite database table using the spatialite_tool command-line utility. Specify the input DBF file, database, table name, and character encoding.

```bash
spatialite_tool -i -dbf com2010_s -d db.sqlite -t communities -c CP1252
```
Tokens:
Raw