SpatiaLite Cookbook Chapter 03: Family cooking

Note:
  The TABLEs created will be used in later recipes: (recipe #11, 12, 13, 14)

---

---

Step 1a) we'll start by creating the regions. table (i.e. hierarchical level 1).

CREATE TABLE regions
(
 region_id INTEGER NOT NULL PRIMARY KEY,
 region_name TEXT NOT NULL
);

Please note: we have defined a PRIMARY KEY, i.e. a unique (not duplicable), absolutely unambiguous identifier for each Region.

---

Step 1b) then we'll populate the regions. table.

INSERT INTO regions
(region_id, region_name)
 SELECT
  cod_reg AS region_id,
  regione AS region_name
 FROM reg2011_s
 ORDER BY region_name;

Using the INSERT INTO ... SELECT ... is more or less like performing a copy:
rows are extracted from the input table and immediately inserted into the output table.
As you can see, corresponding columns are explicitly identified by order.

---

Step 2a) Now the same for provinces, first creating (i.e. hierarchical level 2). ....

CREATE TABLE provinces
(
 province_id INTEGER NOT NULL PRIMARY KEY,
 province_name TEXT NOT NULL,
 car_plate_code TEXT NOT NULL,
 region_id INTEGER NOT NULL,
 CONSTRAINT fk_province_region
  FOREIGN KEY (region_id)
  REFERENCES regions (region_id)
);

---

... then filling....

INSERT INTO provinces
(province_id, province_name,car_plate_code, region_id)
 SELECT
  cod_pro AS province_id,
  provincia AS province_name,
  sigla AS car_plate_code,
  cod_reg AS region_id
 FROM prov2011_s
 ORDER BY province_name;

Note: Due to the defined CONSTRAINT

this command would fail if regions had not been filled beforhand.

---

Step 2b) create an INDEX for the relation between the province and region tables.
Please note: The relation exists through the definition of the FOREIGN KEY..

CREATE INDEX idx_province_region ON provinces (region_id);

Short explanation:

An index is used to support a fast direct access to each single row.

• for a PRIMARY KEY, this is done automaticly in SQLite
• for a FOREIGN KEY, this is not done automaticly
  

---

What is the default use of a FOREIGN KEY, you may ask ...

It is a form of constraint, preventing a record to be INSERTed, when a condition is not fulfilled .

Sample: The Province of Firenze.in the Region of Toscana, cannot be INSERTed if the Region of Toscana does not exist.

---

So when a fast direct access for a FOREIGN KEY is needed

You must create and index from the FOREIGN KEY in the source TABLE (provinces), which reflect the values of the PRIMARY KEY of the referenced TABLE (regions) .

---

Step 3a) we'll now create the communities. table (i.e. hierarchical level 3).

CREATE TABLE communities
(
 community_id INTEGER NOT NULL PRIMARY KEY,
 community_name TEXT NOT NULL,
 population INTEGER NOT NULL,
 province_id INTEGER NOT NULL,
 CONSTRAINT fk_community_province
  FOREIGN KEY (province_id)
  REFERENCES provinces (province_id)
);

Since this will be used often to retrive the province name from the the referenced TABLE

this must be fast, so we will create an INDEX.

CREATE INDEX idx_community_province ON communities (province_id);

---

Step 3b) creating a Geometry column for the communities TABLE.

Placeholder: TODO: Proper explanadion of admin tasks for creation of geometry-column Please note: we haven't defined any Geometry column, although one is required for communities.; this is not a mistake, this is absolutely intentional.

SELECT
 AddGeometryColumn
 (
  -- the table-name
  'communities',
  -- the geometry column-name
  'geometry',
  -- the SRID to be used
  32632,
  -- the geometry class
  'MULTIPOLYGON',
  -- the geometry dimension (simple 2D)
  'XY'
 );

---

-- Add support for SpatialIndex-Queries
SELECT
 CreateSpatialIndex
 (
  -- the table-name
  'communities',
  -- the geometry column-name
  'geometry'
 );

Step 3b) creating a Geometry column isn't the same as creating any other ordinary column.
We have to use the AddGeometryColumn() spatial function, specifying:

1. the geometry column name
2. the SRID to be used
3. the expected geometry class
4. the dimension model
   (in this case, simple 2D)

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;

Step 3c) after all this can populate the communities. table as usual.

---

Step 4) you have now to perform the last step: creating (and populating) the populated_places. table.

DROP TABLE IF EXISTS populated_places;
CREATE TABLE IF NOT EXISTS populated_places
(
 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 name TEXT NOT NULL,
 -- latitude in decimal degrees (wgs84) [Y-Position]
 latitude DOUBLE DEFAULT 0,
 --longitude in decimal degrees (wgs84) [X-Position]
  longitude DOUBLE DEFAULT 0
);

SELECT
 AddGeometryColumn('populated_places', 'geometry',32632, 'POINT', 2);

---

-- Add support for SpatialIndex-Queries
SELECT
 CreateSpatialIndex
 (
  -- the table-name
  'populated_places',
  -- the geometry column-name
  'geometry'
 );

INSERT INTO populated_places
(id,latitude, longitude,name, geometry)
 SELECT
  NULL,latitude, longitude, name, ST_Transform(MakePoint(longitude, latitude, 4326),32632)
 FROM cities1000
 WHERE
 (country_code = 'IT');

Placeholder: TODO: Expand why lat/long to UTM zone 32N is being done here Goals: Avoid ST_Transform 'WSG84' to 'UTM zone 32N'     [ST_Transform(MakePoint(longitude, latitude, 4326),32632)] Retain lat/log as extra columns Explain when: An imported geometry in one SRID should be permanently / one-time or temporarily / on-the-fly Transformed. When the same data is often / always being transformed: permanently / one-time should be done When the same data is seldom / one a year: temporarily / on-the-fly should be done

Several interesting points to be noted:

• we have used an AUTOINCREMENT. clause for the PRIMARY KEY
• this practically means that SQLite can automatically generate an appropriate unique value for this PRIMARY KEY, when no explicit value has been already set.
• accordingly to this, in the INSERT INTO. statement a NULL. value was set for the PRIMARY KEY:
  and this explicitly solicited SQLite to assign automatic values.
• the original cities1000. dataset shipped two numeric columns for longitude [COL006.] and latitude [COL005.]:
  so we have to use the MakePoint(). Spatial function in order to build a point-like Geometry.
• the original latitude/ longitude (SRID 4326) points will be transformed to SRID 32632. WGS 84 / UTM zone 32N [Geographic System] SRS to match the other geometries.

---

DROP TABLE com2011_s;
DROP TABLE prov2011_s;
DROP TABLE reg2011_s;
DROP TABLE cities1000;

Step 5) and finally you can drop any Virtual Table, because they aren't any longer useful.
Please note: dropping a Virtual Shapefile or Virtual CSV/TXT doesn't removes the corresponding external data-source, but simply removes the connection with the current database.

---

SELECT
 *
FROM provinces, regions;

Apparently this query immediately works;
but once you get a quick glance at the result-set you'll immediately discover something really puzzling:

• an unexpected huge number of rows has been returned.
• and each single Province seems to every possible Region.

---

SELECT
 *
FROM provinces, regions
WHERE
 (provinces.region_id = regions.region_id);

This query is exactly the same of the previous one: but this time we introduced an appropriate JOIN condition.
Some points to be noted:

• using two (or more) tables can easily lead to name ambiguity:
  e.g. in this case we have two different columns named region_id., one in the provinces. table, the other in the regions. table.
• we must use fully qualified names to avoid any possible ambiguity:
  e.g. provinces.region_id. identifies the region_id. column belonging to the provinces. table, in an absolutely unambiguous way.
• defining the WHERE provinces.region_id = regions.region_id. clause we impose an appropriate JOIN condition.
  After this the Cartesian Product will be accordingly filtered, so to insert into the result-set only the rows actually satisfying the imposed condition, ignoring any other.

---

SELECT
 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
FROM provinces AS p,
 regions AS r
WHERE
 (p.region_id = r.region_id);

And this one always is the same as above, simply written adopting a most polite syntax:

• using extensively the AS. clause so to define alias names for both columns and tables make JOIN. queries to be much more concise and readable, and easiest to understand.

---

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
FROM communities AS c,
 provinces AS p,
 regions AS r
WHERE
(
 (c.province_id = p.province_id) AND
 (p.region_id = r.region_id)
);

Joining three (or even more) tables isn't much more difficult:
you simply have to apply any required JOIN condition as appropriate.

---

---

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
FROM communities AS c,
 provinces AS p,
 regions AS r
WHERE
(
 (c.province_id = p.province_id) AND
 (p.region_id = r.region_id)
);

You now feel a strong deja vu sensation: and that's more than appropriate, because you have already encountered this query in the previous example.

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
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);

All right, this one is the same identical query rewritten accordingly to alternative syntax rules:

• using the JOIN ... ON (...). clause makes more explicit what's going on.
• and JOIN. conditions are now directly expressed within the ON (...). term:
  this way the query statement is better structured and more readable.
• anyway, all this simply is syntactic sugar:
  there is no difference at all between the above two queries in functional terms.

---

SELECT
 r.region_name AS region,
 p.province_name AS province,
 c.community_name AS community,
 c.population AS population
FROM regions AS r
-- JOIN county to region, based on common .region_id
JOIN provinces AS p ON (p.region_id = r.region_id)
-- JOIN communities to county, based on common .province_id
-- - but only when communities > 100000
JOIN communities AS c ON ((p.province_id = c.province_id) AND (c.population > 100000))
ORDER BY r.region_name, province_name;

There is nothing strange or new in this query:

• we simply introduced a further ON (... AND c.population < 100000). clause, so to exclude any small populated Local Council.

SELECT
 r.region_name AS region,
 p.province_name AS province,
 c.community_name AS community,
 c.population AS population
FROM regions AS r
JOIN provinces AS p ON (p.region_id = r.region_id)
LEFT JOIN communities AS c ON ((p.province_id = c.province_id) AND (c.population > 100000))
ORDER BY r.region_name, province_name;

Apparently this query is the same as the latest one.
But a remarkable difference exists:

• this time we've used a LEFT JOIN. clause:
  and the result-set now looks very different from the previous one.
• a plain JOIN. clause will include into the result-set only rows for which both the left-sided and the right-sided terms has been positively resolved.
• but the most sophisticated LEFT JOIN. clause will include into the result-set any row having an unresolved right-sided term:
  and in this case any right-sided member assumes a NULL. value.

There is a striking difference between a plain JOIN. and a LEFT JOIN..
Coming back to previous example, using a LEFT JOIN. clause ensures that any Region and any County will now be inserted into the result-set, even the ones failing to satisfy the imposed population limit for Communities.

---

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);

Et voila, here is your first VIEW:

• basically, this looks exactly as any query you've already seen since now.
• except in this; this time the first line is: CREATE VIEW ... AS.
• and this one is unique syntactical difference transforming a plain query into a VIEW definition.

---

SELECT
 community_name, population, province_name
FROM view_community
WHERE (region_name = 'Lazio')
ORDER BY community_name

You can actually query this VIEW.

SELECT
 region_name,
 Sum(population) AS population,
 (Sum(ST_Area(geometry)) / 1000000.0) AS "area (sq.Km)",
 (Sum(population) / (Sum(ST_Area(geometry)) / 1000000.0)) AS "popDensity (people/sq.Km)"
FROM view_community
GROUP BY region_id
-- '4'= 4th column (called 'popDensity (people/sq.Km)')
ORDER BY 4;

You can really perform any arbitrary complex query using a VIEW.

SELECT
 v.community_name AS Community,
 v.province_name AS Province,
 v.region_name AS Region
FROM view_community AS v
JOIN communities AS c ON
(
 -- with: (Upper(c.community_name) = 'NORCIA') : 4 min 22.883 seconds ; without 1.427 seconds
 (c.community_name = 'Norcia') AND
 (ST_Touches(v.geometry, c.geometry))
)
ORDER BY v.community_name, v.province_name, v.region_name;

You can JOIN. a VIEW and a TABLE. (or two VIEWs, and so on ...)
Just a simple explanation: this JOIN. actually is one based on Spatial relationships:
the result-set represents the list of Communities sharing a common boundary with the Norcia one.
You can get a much more complete example here (Haute cuisine recipes).

---

A VIEW is one of the many powerful and wonderful features supported by SQL.
And SQLite's own implementation for VIEW surely is a first class one.
You should use VIEW as often as you can: and you'll soon discover that following this way handling really complex DB layouts will become a piece of cake.

Please note: querying a VIEW can actually be as fast and efficient as querying a TABLE..
But a VIEW cannot anyway be more efficient than the underlying query is; any poorly designed and badly optimized query surely will translate into a very slow VIEW.

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

This statement will create a very simple table named people.:

• each individual column definition must at least specify the corresponding data-type, such as TEXT. or INTEGER.
• please note: data-type handling in SQLite strongly differs from others DMBS implementations:
  but we'll see this in more detail later.

---

CREATE TABLE people2
(
 id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 first_name TEXT NOT NULL,
 last_name TEXT NOT NULL,
 age INTEGER CONSTRAINT age_verify CHECK (age BETWEEN 18 AND 90),
 gender TEXT CONSTRAINT gender_verify CHECK (gender IN ('M', 'F')),
 phone TEXT
);

This one is more sophisticated version of the same table:

• we have added an id. column, declared as PRIMARY KEY AUTOINCREMENT.
  • inserting a PRIMARY KEY on each table always is a very good idea.
  • declaring an AUTOINCREMENT. clause we'll request SQLite to automatically generate unique values for this key.
• we have added a NOT NULL. clause for first_name. and last_name. columns.
  • this will declare a first kind of constraint: NULL. values will be rejected for these columns.
  • in other words, first_name. and last_name. absolutely have to contain some explicitly set value.
• we have added a CONSTRAINT ... CHECK (...). for age. and gender. columns.
  • this will declare a second kind of constraint: values failing to satisfy the CHECK (...). criterion will be rejected.
  • the age. column will now accept only reasonable adult ages.
  • and the gender. column will now accept only 'M'. or 'F'. values.
  • please note: we have not declared the NOT NULL. clause, so age = NULL. and gender = NULL. are still considered to be valid values.

---

You can add any further column after the initial table creation with ...

ALTER TABLE people2
 ADD COLUMN cell_phone TEXT;

---

You can change the table name with ...

ALTER TABLE people2
 RENAME TO people_ok;

---

Or remove the table (and its whole content) from the Database with ...

DROP TABLE people;

---

You can create an Index with ...

CREATE INDEX idx_people_phone ON people_ok (phone);

---

Or destroy a Index with ...

DROP INDEX idx_people_phone;

---

To create an Index that support more than one column with ...

CREATE UNIQUE INDEX idx_people_name ON people_ok (last_name, first_name);

• declaring the UNIQUE. clause implements a further constraint:
  once some value is already defined, any further attempt to insert the same value will then fail.

---

In order to query a table layout you can use

PRAGMA table_info(people_ok);

---

You can easily query the corresponding Index layout by

using

PRAGMA index_list(people_ok);

and

PRAGMA index_info(idx_people_name);

---

You must first create the table, with the 'normal'-columns ...

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
);

Then add the Geometry-column, as a separate step ...

SELECT
 AddGeometryColumn('cities_test', 'geom_wsg84',4326, 'POINT', 'XY');

This is the proper way to insure that a completely valid / usable Geometry-colulmn will be created.
Any different approach will lead incorrect and unreliable Geometry.

---

Although the previous command more commonly used, another form, supported by AddGeometryColumn(), exists:

SELECT AddGeometryColumn
(
  -- table-name
 'cities_test',
  -- geometry column-name
  'geom_wsg84',
  -- srid of geometry
 4326,
  -- geometry-type
  'POINT',
  -- permit NULL values for geometry [0=yes ; 1=no]
  0
 );

The last (optional) argument actually means: NOT NULL.

• setting a value 0. (the default value, if omitted) then the Geometry column will accept NULL. values.
• otherwise only NOT NULL. geometries will be accepted.

---

Supported SRIDs:

• any possible SRID defined within the spatial_ref_sys. metadata table.

  including the 2 Undefined shown below

The standard OGC-SFS defines 2 forms of an Undefined SRIDs:

• -1: 'Undefined - Cartesian' [default]
  

  • pixels: of non-georeferenced images
  • unknown: with values that are not in degrees

•  0: 'Undefined - Geographic Long/Lat'
  

  • old maps: with no projection information, but using degrees
  • unknown: but with degrees values

    Y/latitude (+90 to -90) or X/longitude (+180 to -180)

In most cases Spatialite assumes -1: 'Undefined - Cartesian'

when a SRID is not defined.

---

Supported Geometry-types:
Supported Dimension-models:

---

Please note well: this one is a very frequent pitfall.
Many developers, GIS professionals and alike obviously feel to be much smarter than this, so they often tend to invent some highly imaginative alternative way to create their own Geometries.
e.g. bungling someway the geometry_columns table seems to be a very popular practice.

May well be that such creative methods will actually work with some very specific SpatiaLite's version; but for sure some severe incompatibility will raise before or after ...

Be warned: only Geometries created using AddGeometryColumn() are fully legitimate.
Any different approach is completely unsafe (and unsupported ..)

I suppose that directly checking how AddGeometryColumn() affects the database may help you to understand better.

PRAGMA table_info(cities_test);

---

step 1: a new geom_wsg84. column has been added to the corresponding table.

SELECT
 *
FROM geometry_columns
WHERE
 (f_table_name LIKE 'cities_test');

---

step 2: a corresponding row has been inserted into the geometry_columns metadata table.

SELECT
 *
FROM sqlite_master
WHERE
(
 (type = 'trigger') AND
 (tbl_name LIKE 'cities_test')
);

---

step 3: the sqlite_master. is the main metadata table used by SQLite to store internal objects.
As you can easily notice, each Geometry requires some triggers to be fully supported and well integrated into the DBMS workflow.
Not at all surprisingly, all this has to be defined in a strongly self-consistent way in order to let SpatiaLite work as expected.
If some element is missing or badly defined, the obvious consequence will be a defective and unreliable Spatial DBMS.

---

SELECT
 DiscardGeometryColumn('cities_test', 'geom_wsg84');

This will remove any metadata and any trigger related to the given Geometry.
Please note: anyway this will leave any geometry-value stored within the corresponding table absolutely untouched.
Simply, after calling DiscardGeometryColumn(...). they aren't any longer fully qualified geometries, but anonymous and generic BLOB values.

SELECT
 RecoverGeometryColumn('cities_test', 'geom_wsg84',4326, 'POINT', 2);

This will attempt to recreate any metadata and any trigger related to the given Geometry.
If the operation successfully completes, then the Geometry column is fully qualified.
In other words, there is absolutely no difference between a Geometry created by AddGeometryColumn() and another created by RecoverGeometryColumn().
Very simply explained:

• AddGeometryColumn() is intended to create a new, empty column.
• RecoverGeometryColumn() is intended to recover in a second time an already existing (and populated) column.

Sometimes circumventing version-related issues is inherently impossible: e.g. there is absolutely no way to use 3D geometries on obsolescent versions, because the required support was introduced in more recent times.
But in many other cases such issues are simply caused by some incompatible binary function required by triggers.

Useful hint

To resolve any trigger-related incompatibility you can simply try to:

• remove first any trigger: the best way you can follow is using DiscardGeometryColumn().
• and then recreate again the triggers using AddGeometryColumn()

This will ensure that any metadata info and trigger will surely match expectations of your binary library current version.

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
);

---

SELECT
 AddGeometryColumn('cities_test', 'geom_wsg84', 4326, 'POINT', 'XY');

Nothing new in this: it's exactly the same table we've already created in the previous example.

INSERT INTO cities_test
(id_geoname, name, latitude, longitude, geom_wsg84)
VALUES(NULL, 'first point', 1.02, 1.01, GeomFromText('POINT(1.01 1.02)', 4326));

---

INSERT INTO cities_test
VALUES(NULL, 'second point', 2.02, 2.01,GeomFromText('POINT(2.01 2.02)', 4326));

---

INSERT INTO cities_test
(id_geoname, name, latitude, longitude, geom_wsg84)
VALUES(10, 'tenth point', 10.02, 10.01,GeomFromText ('POINT(10.01 10.02)', 4326));

---

INSERT INTO cities_test
(geom_wsg84, latitude, longitude, name, id_geoname)
VALUES(GeomFromText('POINT(11.01 11.02)', 4326), 11.02, 11.01, 'eleventh point', NULL);

---

INSERT INTO cities_test
(id_geoname, latitude, longitude, geom_wsg84, name)
VALUES(NULL, 12.02, 12.01, NULL, 'twelfth point');

The INSERT INTO (...) VALUES (...). statement does exactly what its name states:

• the first list enumerates the column names
• and the second list contains the values to be inserted: correspondences between columns and values are established by position.
• you can simply suppress the column-names list (please, see the second INSERT. statement):
  anyway this one isn't a good practice, because such statement implicitly relies upon some default column order, and that's not at all a safe assumption.
• another interesting point; this table declares a PRIMARY KEY AUTOINCREMENT.:
  • as a general rule we've passed a corresponding NULL. value, so to allow SQLite autogenerating a unique value.
  • but in the third INSERT. we've set an explicit value (please, check in the following paragraph what really happens in this case).

SELECT
 *
FROM cities_test;

id_geoname	name	latitude	longitude	geom_wsg84
1	first point	1.02	1.01	BLOB sz=60 GEOMETRY
2	second point	2.02	2.01	BLOB sz=60 GEOMETRY
10	tenth point	10.02	10.01	BLOB sz=60 GEOMETRY
11	eleventh point	11.02	11.01	BLOB sz=60 GEOMETRY
12	twelfth point	12.02	12.01	NULL

Just a quick check before going further on ...

---

INSERT INTO cities_test
VALUES(2, 'POINT #2', 2.02, 2.01, GeomFromText('POINT(2.01 2.02)', 4326));

This further INSERT. will loudly fail, raising a constraint failed exception.
Accounting for this isn't too much difficult: a PRIMARY KEY always enforces a uniqueness constraint.
And actually one row of id = 2 already exists into this table.

INSERT OR IGNORE INTO cities_test
VALUES(2, 'POINT #2', 2.02, 2.01,,GeomFromText('POINT(2.01 2.02)', 4326));

By specifying an OR IGNORE. clause this statement will now silently fail (same reason as before).

INSERT OR REPLACE INTO cities_test
VALUES(2, 'POINT #2', 2.02, 2.01,,GeomFromText('POINT(2.01 2.02)', 4326));

There is a further variant: i.e. specifying an OR REPLACE. clause this statement will actually act like an UPDATE.

REPLACE INTO cities_test
(id_geoname, name, latitude, longitude, geom_wsg84)
VALUES(3, 'POINT #3', 3.02, 3.01, GeomFromText('POINT(3.01 3.02)', 4326));

---

REPLACE INTO cities_test
(id_geoname, name, latitude, longitude, geom_wsg84)
VALUES(11, 'POINT #11', 11.32, 11.31,GeomFromText('POINT(11.31 11.32)', 4326));

And yet another syntactic alternative is supported, i.e. simply using REPLACE INTO.:
but this latter simply is an alias for INSERT OR REPLACE..

SELECT
 *
FROM cities_test;

id_geoname	name	latitude	longitude	geom_wsg84
1	first point	1.02	1.01	BLOB sz=60 GEOMETRY
2	POINT #2	2.02	2.01	BLOB sz=60 GEOMETRY
3	POINT #3	3.02	3.01	BLOB sz=60 GEOMETRY
10	tenth point	10.02	10.01	BLOB sz=60 GEOMETRY
11	POINT #11	11.32	11.31	BLOB sz=60 GEOMETRY
12	twelfth point	12.02	12.01	NULL

Just another quick check ...

---

UPDATE cities_test SET
 name = 'point-3',
 latitude = 3.320000,
 longitude = 3.310000
WHERE (id_geoname = 3);

---

UPDATE cities_test SET
 latitude = latitude + 45.0,
 longitude = (longitude + 90.0)
WHERE (id_geoname > 10);

updating values isn't much more complex ...

DELETE FROM cities_test
WHERE ((id_geoname % 2) = 0);

and the same is for deleting rows.
i.e. this DELETE. statement will affect every even id value.

SELECT
 *
FROM cities_test;

id_geoname	name	latitude	longitude	geom_wsg84
1	first point	1.02	1.01	BLOB sz=60 GEOMETRY
3	point-3	3.320000	3.310000	BLOB sz=60 GEOMETRY
11	POINT #11	56.320000	101.310000	BLOB sz=60 GEOMETRY

A last final quick check ...

---

Very important notice

Be warned: calling an UPDATE. or DELETE. statement without specifying any corresponding WHERE. clause is a full legal operation in SQL.
Anyway SQL intends that the corresponding change must indiscriminately affect any row within the table: and sometimes this is exactly what you intended to do.

But (much more often) this is a wonderful way allowing to unintentionally destroy or to irreversibly corrupt your data: beginner, pay careful attention.

Important notice:
in order to preserve your main sample database untouched,
creating a different database for this session is strongly suggested.

---

GETTING STARTED

CREATE TABLE mothers
(
 first_name TEXT NOT NULL,
 last_name TEXT NOT NULL,
 CONSTRAINT pk_mothers
   PRIMARY KEY (last_name, first_name)
);

---

SELECT
 AddGeometryColumn('mothers', 'home_location',4326, 'POINT', 'XY', 1);

---

CREATE TABLE children
(
 first_name TEXT NOT NULL,
 last_name TEXT NOT NULL,
 mom_first_nm TEXT NOT NULL,
 mom_last_nm TEXT NOT NULL,
 gender TEXT NOT NULL
   CONSTRAINT sex CHECK (
     gender IN ('M', 'F')),
 CONSTRAINT pk_childs
   PRIMARY KEY (last_name, first_name),
 CONSTRAINT fk_childs
   FOREIGN KEY (mom_last_nm, mom_first_nm)
     REFERENCES mothers (last_name, first_name)
);

---

INSERT INTO mothers (first_name, last_name, home_location)
VALUES('Stephanie', 'Smith', ST_GeomFromText('POINT(0.8 52.1)', 4326));

---

INSERT INTO mothers (first_name, last_name, home_location)
VALUES('Antoinette', 'Dupont', ST_GeomFromText('POINT(4.7 45.6)', 4326));

---

INSERT INTO mothers (first_name, last_name, home_location)
VALUES('Maria', 'Rossi', ST_GeomFromText('POINT(11.2 43.2)', 4326));

---

INSERT INTO children
(first_name, last_name, mom_first_nm, mom_last_nm, gender)
VALUES('George', 'Brown', 'Stephanie', 'Smith', 'M');

---

INSERT INTO children
(first_name, last_name, mom_first_nm, mom_last_nm, gender)
VALUES('Janet', 'Brown', 'Stephanie', 'Smith', 'F');

---

INSERT INTO children
(first_name, last_name, mom_first_nm, mom_last_nm, gender)
VALUES('Chantal', 'Petit', 'Antoinette', 'Dupont', 'F');

---

INSERT INTO children
(first_name, last_name, mom_first_nm, mom_last_nm, gender)
VALUES('Henry', 'Petit', 'Antoinette', 'Dupont', 'M');

---

INSERT INTO children
(first_name, last_name, mom_first_nm, mom_last_nm, gender)
VALUES('Luigi', 'Bianchi', 'Maria', 'Rossi', 'M');

Nothing too much complex: we simply have created two tables:

• the mothers. table contains a Geometry column
• a relation exists between mothers. and children.: and consequently a FOREIGN KEY. has been defined.
• just a last point to be noted: in this example we'll use PRIMARY KEYs spanning across two columns:
  but there isn't nothing odd in this ... it's a fully legitimate SQL option.

And then we have inserted very few rows into these tables.

SELECT
 m.last_name AS MomLastName,
 m.first_name AS MomFirstName,
 ST_X(m.home_location) AS HomeLongitude,
 ST_Y(m.home_location) AS HomeLatitude,
 p.last_name AS ChildLastName,
 p.first_name AS ChildFirstName,
 p.gender AS ChildGender
FROM mothers AS m
JOIN children AS c ON
( -- JOIN on First/Last Name of Mother and Child
 ((m.first_name = p.mom_first_nm) AND
  (m.last_name = p.mom_last_nm))
);

Just a simple check; and then you are now ready to start.

---

QUESTIONS

Q1: why this SQL statement will actually fail, raising a constraint failed exception ?

INSERT INTO children
(first_name, last_name, mom_first_nm, mom_last_nm)
VALUES('Silvia', 'Bianchi', 'Maria', 'Rossi');

Q2: ... same question ...

INSERT INTO children
(first_name, last_name, mom_first_nm, mom_last_nm, gender)
VALUES('Silvia', 'Bianchi', 'Maria', 'Rossi', 'f');

Q3: ... same question ...

INSERT INTO children
(first_name, last_name, mom_first_nm, mom_last_nm, gender)
VALUES('Silvia', 'Bianchi', 'Giovanna', 'Rossi', 'F');

Q4: ... same question ...

INSERT INTO children
(first_name, last_name, mom_first_nm, mom_last_nm, gender)
VALUES('Henry', 'Petit', 'Stephanie', 'Smith', 'M');

Q5: ... same question ...

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

Q6: ... same question ...

INSERT INTO mothers (first_name, last_name, home_location)
VALUES('Pilar', 'Fernandez',
   ST_GeomFromText('MULTIPOINT(4.7 45.6, 4.75 45.32)', 4326));

Q7: ... same question ...

INSERT INTO mothers (first_name, last_name)
VALUES('Pilar', 'Fernandez');

Q8: ... same question ...

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

Q9: ... same question ...

DELETE FROM mothers
WHERE last_name = 'Dupont';

Q10: ... same question ...

UPDATE mothers SET first_name = 'Marianne'
WHERE last_name = 'Dupont';

---

ANSWERS

A1: missing/undefined gender: so a NULL. value is implicitly assumed.
But a NOT NULL. constraint has been defined for the gender. column.

A2: wrong gender. value ('f'.): SQLite text strings are case-sensitive.
The sex. constraint can only validate 'M'. or 'F'. values: 'f'. isn't an acceptable value.

A3: FOREIGN KEY failure.
No matching entry {'Rossi','Giovanna'}. was found into the corresponding table [mothers.].

A4: PRIMARY KEY failure.
An entry {'Petit','Henry'}. is already stored into the children. table.

A5: missing/undefined SRID: so a -1. value is implicitly assumed.
But a Geometry constraint has been defined for the corresponding column; an explicitly set 4326 SRID. value is expected anyway for home_location. geometries.

A6: wrong Geometry-type: only POINT-type Geometries will pass validation for the home_location. column.

A7: missing/undefined home_location.: so a NULL. value is implicitly assumed.
But a NOT NULL. constraint has been defined for the home_location. column.

A8: malformed WKT expression: ST_GeomFromText(). will return NULL. (same as above).

A9: FOREIGN KEY failure: yes, the mothers. table has no FOREIGN KEY.
But the children. table instead has a corresponding FOREIGN KEY.
Deleting this entry from mothers. will break referential integrity, so this one isn't an allowed operation.

A10: FOREIGN KEY failure: more or less, the same of before.
Modifying a PRIMARY KEY entry into the mothers. table will break referential integrity, so this operation as well isn't admissible.

---

Lesson to learn #1:
An appropriate use of SQL constraints strongly helps to fully preserve your data in a well checked and absolutely consistent state.
Anyway, defining too much constraints may easily transform your database into a kind of inexpugnable fortress surrounded by trenches, pillboxes, barbed wire and minefields.
i.e. into something that surely nobody will define as user friendly.
Use sound common sense, and possibly avoid any excess.

Lesson to lean #2:
Each time the SQL-engine detects some constraint violation, a constraint failed exception will be immediately raised.
But this one is an absolutely generic error condition: so you have to use your experience and skilled knowledge in order to correctly understand (and possibly resolve) any possible glitch.

---

BEGIN;

---

CREATE TABLE test
(
 num INTEGER,
 string TEXT
);

---

INSERT INTO test
(num, string)
VALUES(1, 'aaaa');

---

INSERT INTO test
(num, string)
VALUES(2, 'bbbb');

---

INSERT INTO test
(num, string)
VALUES(3, 'cccc');

The BEGIN. statement will start a TRANSACTION.:

• after this declaration, any subsequent statement will be handled within the current TRANSACTION. context.
• you can also use the BEGIN TRANSACTION. alias, but this is redundantly verbose, and not often used.
• SQLite forbids multiple nested transactions: you can simply declare an unique pending TRANSACTION. at each time.

SELECT
 *
FROM test;

You can now check your work: there is nothing odd in this, isn't ?
Absolutely anything looks as expected.

---

COMMIT;

ROLLBACK;

Once you BEGIN. a TRANSACTION., any subsequent statement will be left in a pending (uncommitted) state.
Before or after you are expected to:

• close positively the TRANSACTION. (confirm), by declaring a COMMIT. statement.
  • any change applied to the DB will be confirmed and consolidated in a definitive way.
  • such changes will become immediately visible to other connections.
• close negatively the TRANSACTION. (invalidate), by declaring a ROLLBACK. statement.
  • any change applied to the DB will be rejected: the DB will be reverted to the previous state.
• if you omit declaring neither COMMIT. nor ROLLBACK., then SQLite prudentially assumes that the still pending TRANSACTION. is an invalid one, and a ROLLBACK. will be implicitly performed.
• if any error or exception is encountered within a TRANSACTION. context, than the whole TRANSACTION. is invalidated, and a ROLLBACK. is implicitly performed.

---

---

---

Several algorithms supporting a Spatial Index has been defined during the past years.
SQLite's Spatial Index is based on the R*Tree algorithm.

Very shortly said, an R*Tree defines a tree-like structure based on rectangles (the R. in R*Tree stands exactly for Rectangle).

Every arbitrary Geometry can be represented as a rectangle, irrelevantly of its actual shape: we can simply use the MBR. (Minimum Bounding Rectangle) corresponding to such Geometry.
May well be the term BBOX. (Bounding Box) is more familiar to you: both terms are exact synonyms.

It's now quite intuitive understanding how the R*Tree does actually works:

• Spatial query defines an arbitrary search frame (this too being a rectangle)
• the R*Tree is quickly scanned identifying any overlapping index rectangle
• and finally any individual Geometry falling withing the search frame will be identified.>

Think of the well known needle in the haystack problem: using an R*Tree is an excellent solution allowing to find the needle in a very short time, even when the haystack actually is an impressively huge one.

---

---

---

SELECT
 CreateSpatialIndex('communities', 'geometry');

---

 -- Add support for SpatialIndex-Queries SELECT
 CreateSpatialIndex('populated_places', 'geometry');

This simple declaration is all you are required to specify in order to set a Spatial Index corresponding to some Geometry column. And that's all.

SELECT
 DisableSpatialIndex('communities', 'geometry');

And this will remove a Spatial Index:

• please note: this will not DROP. the Spatial Index (you must perform this operation in a separate step).
• anyway related metadata are set so to discard the Spatial Index, and any related TRIGGER. will be immediately removed.

SpatiaLite supports a second alternitive Spatial Index based on MBR-caching.
This one simply is a historical legacy, so using MBR-caching is strongly discouraged.