SpatiaLite Cookbook: How to Sql samples

• admin_cities: Creation of the VIEW admin_cities

The final result should be stored as a MULTIPOLYGON ...

SELECT
 -- create a MULTIPOLYGON from a POLYGON
 CastToMulti
 (

---

geom_city (non-aggregate-geometry input)  returns: 1 POLYGON, which will be converted to a MULTIPOLYGON
ST_InteriorRingN result:   1 LINESTRING, which is closed	ST_MakePolygon result:   1 POLYGON (input must be a closed LINESTRING)

To combine the individual geometries into 1 geometry, removing the internal boundries

  -- create a POLYGON from a closed LINESTRING, non-aggregate-geometry input
  ST_MakePolygon
  (
   -- Interior-Ring (closed LINESTRING), non-aggregate-geometry input
   ST_InteriorRingN
   (
    -- which Interior-Ring number, 1-based
    ST_GeometryN
    (
     -- non-aggregate-geometry input

---

geom_city (aggregate-geometry input)  returns: 6 POLYGONs and 2 MULTIPOLYGONs (each with 1 POLYGON)
ST_Union result:   1 POLYGON with 1 Interior-Ring	ST_Collect result:   1 MULTIPOLYGON, with 10 POLYGONs

To combine the individual geometries into 1 geometry, removing the internal boundries

     ST_Union
     (
      -- aggregate-geometry input
      geom_city
     ),

... or ...

To collect the individual geometries into 1 geometry, retaining the internal boundries

     ST_Collect
     (
      -- aggregate-geometry input
      geom_city
     ),

---

(continuation of ST_GeometryN, after ST_Union or ST_Collect)

     -- which geometry from collection, 1-based
     1
    ),
   -- which Interior-Ring number, 1-based
   1
  )
 )
)

---

geom_city (WHERE condition)  returns: 6 POLYGONs and 2 MULTIPOLYGONs (each with 1 POLYGON)

The yellow lines show the Communities boundries.   The center area shows the outline of San Marino.

-- source TABLE
FROM admin_cities
WHERE
(
 -- condition for aggregate-geometry input [8 cities]
 (id_city IN (41033,41035,41060,99003,99010,99014,99020,99025))
);

---

the complete Sql-Query can be found here INSERT San Marino.

• admin_countries: Creation of the TABLE admin_countries

---

 Area of a POLYGON is: (Exterior-Ring minus Interior-Ring(s))
  area_bbox_geom_country = (area_bbox_country - area_country)

---

id_country	name_country	area_bbox_country	area_country	area_bbox_geom_country
39	Italy	1284985011570.000000	302065977244.666687	982919034325.346191
378	Republic of San Marino	100437486.093750	61072552.870600	39364933.223150
377	Vatican City State	862840.000000	528986.602061	333853.397939

Notes:
 Of the area the SpatialIndex entry (BoundingBox) of Italy:

• 69.2684782 %: (Exterior-Ring): is outside the boundries of Italy
    (Corsica, Slovenia, portions of Austria, Bosnia-Herzegovina, Croatia, Hungry, Metropolitan France and Switzerland)
• 30.7315218 % (Interior-Ring(s)) : is inside the boundries of Italy

Conclusion:
 Only the combination of : SpatialIndex query first, then when true ('AND') call to ST_Contains as second condition
  should be used to retrieve the correct results.

---

SpatialIndex entry (BoundingBox) and ST_Contains results for:

City	Italy result	ST_Contains result	Croatia result	ST_Contains result
Triest, Italy	true	true	true	false
Zagreb, Croatia	true	false	true	true
Bastia, Corsica	true	false	false	not called
Athens, Greece	false	not called	false	not called

---

SELECT
 id_country,
 name_country,
 ST_Area
 (
  BuildMbr
  (
   idx_SpatialIndex.xmin,
   idx_SpatialIndex.ymax,
   idx_SpatialIndex.xmax,
   idx_SpatialIndex.ymin,
   32632
  )
 ) AS area_bbox_country,
 ST_Area(geom_country) AS area_country,
 ST_Area
 (
  ST_Difference
  (
   -- SpatialIndex of geometry of the country
   BuildMbr
   (
    idx_SpatialIndex.xmin,
    idx_SpatialIndex.ymax,
    idx_SpatialIndex.xmax,
    idx_SpatialIndex.ymin,
    32632
   ),
   -- geometry of the boundries of the country
   geom_country
  )
 ) AS area_bbox_geom_country,
 CastToMulti
 (

---

SpatialIndex entry (BoundingBox) of Geometry: Area of Exterior-Ring Geometry: Area of Interior-Ring(s)
Italian Republic	San Marino	Vatican City

• Italian Republic: 4 Exterior-Rings, 1 (Nr. 1) with 401 Interior-Rings
• San Marino: 1 Exterior-Ring, 1 (Nr. 1) with 1 Interior-Ring
• Vatican City: 1 Exterior-Ring, 1 (Nr. 1) with 1 Interior-Ring

ST_Difference result:
  the 2nd Parameter Cut Geometry will be removed/cut/subtracted from the 1st Parameter: Source Geometry

  ST_Difference
  (

When the 2nd Parameter Cut Geometry is is a country
  and the Source Geometry are :

• POINTs: All Cities in Europe
    result: only those Cities outside the 2nd Parameter Cut Geometry will remain
• LINESTRINGs: All Railroad-Lines in Europe
    result: only those Railroad-Lines outside the 2nd Parameter Cut Geometry will remain
• POLYGONs: All Countries in Europe
    result: only those Countries outside the 2nd Parameter Cut Geometry will remain

ST_SymDifference result:
  portions outside the 2nd Parameter Cut Geometry and the 1st Source Geometry will remain

  ST_SymDifference
  (

When the Source Geometry is the SpatialIndex entry (BoundingBox) of Geometry of the Italian Republic
  and the 2nd Parameter Cut Geometry is:

• POINTs: All the Cities in Croatia
    result: only the portion of Cities outside the 1st Parameter Source Geometry will remain
• LINESTRINGs: All the Railroad-Lines in Croatia
    result: only the portion of Railroad-Lines outside the 1st Parameter Source Geometry will remain
• POLYGONs: The Geometry of Croatia
    result: only the portion of Croatia outside the 1st Parameter Source Geometry will remain

---

SpatialIndex entry (BoundingBox) of Geometry: Area of Exterior-Ring
Italian Republic	San Marino	Vatican City

ST_Difference 1st Parameter: Source Geometry
   (a portion of this Geometry to be removed, corresponding to the 2nd Parameter Geometry)

   -- SpatialIndex of geometry of the boundries of the country
   BuildMbr
   (
    idx_SpatialIndex.xmin,
    idx_SpatialIndex.ymax,
    idx_SpatialIndex.xmax,
    idx_SpatialIndex.ymin,
    32632
   ),

• each : 1 Exterior-Ring, with 5 POINTs (closed LINESTRING)

Note:

The CreateSpatialIndex command will create a TABLE based on the following syntax:

• 'idx_' static (GeoPackage: 'rtree_')
• table-name+'_'
• geometry-column-name

Containing the columns pkid [GeoPackage: id], as Primary Key of the original TABLE, and xmin,xmax,ymin,ymax
An Alias is used to simplify the usage: idx_admin_countries_geom_country AS idx_SpatialIndex

---

Geometry: Area of (Exterior-Ring minus Interior-Ring(s))
Italian Republic	San Marino	Vatican City

ST_Difference 2nd Parameter Cut Geometry
   (the portion of 1st Parameter Geometry to be removed)

   geom_country

• Italian Republic: 401 Exterior-Rings, 1 (Nr. 232) with 3 Interior-Rings
• San Marino: 1 Exterior-Ring
• Vatican City: 1 Exterior-Ring

---

  )
 ) AS bbox_geom_country
-- source TABLE with JOIN to SpatialIndex TABLE
FROM admin_countries
-- SpatialIndex syntax: "idx" + table-name + geometry-column-name
JOIN idx_admin_countries_geom_country AS idx_SpatialIndex ON
 (idx_SpatialIndex.pkid=admin_countries.id_country);

---

the complete Sql-Query can be found here UPDATE bbox_geom_country.

• cities1000: Creation of the TABLE cities1000

The Goal of the Sql

Is to a general purpose Sql to create list POINTs, based on a Geographic (Degrees, such as Wsg84) based SRID, and:

• a starting POINT, again given in the used SRID (4326)
• distance given in meters

---

Based on these 2 values, the meters distance in degrees will be calculated

• using ST_Project to CREATE a POINT due north of the given start POINT, usng the meters distance
• using MakeLine: a LINESTRING will be created using both POINTs, in the used SRID (4326)
• with ST_Distance the distance in degrees of the LINESTRING will be returned

---

The SpatialIndex will be used to first retrieve likley candidates using

• ST_Buffer, creating a circle around the starting POINT, created the distance in degrees

PtDistWithin will the check if the candidates are within the circle

• since a candidate may be inside the BoundingBox of the SpatialIndex entry, but outside of the circle

---

The first part of the results will show:

• the title of the Query
• the given distance in meters
• the calculated distance degrees

The second part of the results will show:

• 5 columns of the the main TABLE
• the given distance in meters
• the calculated distance degrees

The third part of the results will show:

• calculated distance from the starting POINT to the found city as:
  • degrees using ST_Distance and ST_Project
  • meters using ST_Distance
  • mille passus (Roman Miles) calculated as (meters/1481)
  • Kilometers using CvtToKm
  • U.S. Statute Miles using CvtToUsMi
  • Indian Chains using CvtToIndCh

---

The returned results, should primaraly, be used to determine a realistic distance in degrees for use with
  ST_Buffer, that needs Geographic distances for a degrees based SRID.

SELECT
 -- source_data: search distance point as name
 from_result.distance_search_name,
 -- source_data: search distance in meters
 from_result.distance_search_meters,
 -- search distance in degrees
 from_result.distance_search_degrees,
 -- cities1000: id_geoname
 from_result.source_id_geoname,
 -- cities1000: timezone_id
 from_result.source_timezone_id,
 -- cities1000: name
 from_result.source_name,
 -- cities1000: feature_class (as source_query_column)
 from_result.source_query_column AS type,
 -- cities1000: admin1_code
 from_result.source_admin1_code,
 -- cities1000: country_code
 from_result.source_country_code,
 -- distance in degrees
 from_result.distance_degrees,
 -- distance in meters
 from_result.distance_meters,
 -- distance in mille passus
 (from_result.distance_meters/1481) AS roman_miles,
 -- Spatialite specific function: convert to Kilometers
 CvtToKm(distance_meters) AS kilometers,
 -- Spatialite specific function: convert to U.S. Statute Miles
 CvtToUsMi(distance_meters) AS us_statute_miles,
 -- Spatialite specific function: convert to Indian Chains
 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;

91 rows returned (first, second and last shown here):

distance_search_name	distance_search_meters	distance_search	id_geoname	timezone_id	name	type	admin1_code	country_code	distance_degrees	distance_meters	roman_miles	kilometers	us_statute_miles	indian_chains
32 Km from: Foro Romano, Roma	32000.000000	0.288096	3169070	Europe/Rome	Rome	P	07	IT	0.026488	2198.636331	1.484562	2.198636	1.366167	109.294112
32 Km from: Foro Romano, Roma	32000.000000	0.288096	6691831	Europe/Vatican	Vatican City	P		VA	0.032362	2789.398776	1.883456	2.789399	1.733249	138.660887
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
32 Km from: Foro Romano, Roma	32000.000000	0.288096	8949146	Europe/Rome	Casalazzara	P	07	IT	0.284583	30882.136227	20.852219	30.882136	19.189231	1535.149593

---

Roman mile Zones (1 mille passus = 1481 meters) :

570-620: outer ring 470-520: 1st inner ring 370-420: 2nd inner ring 270-320: 3rd inner ring 170-220: final inner ring 0-170: area of 170 mille passus from the Foro Romano

Changing the distance_search_meters conditiona to:

• '620 mille passus from: Foro Romano, Roma' AS distance_search_name,
• CvtFromKm((620*1.481)) AS distance_search_meters,

and adding a WHERE condition (before the ORDER BY statement):

WHERE
(
 -- BETWEEN both numbers (inclusive)
 (roman_miles BETWEEN 170 AND 220) OR
 (roman_miles BETWEEN 270 AND 320) OR
 (roman_miles BETWEEN 370 AND 420) OR
 (roman_miles BETWEEN 470 AND 520) OR
 (roman_miles BETWEEN 570 AND 620)
)

  creates a Zone Map of populated places outside of Italy (49 B.C.) with 50 mille passus (Roman Miles) intervals.

---

A quick check to insure that the law on imperium is being respected,   shows that we (almost) got it right:

Area of Arno and Rubicon Rivers:   Boundary between the Roman province of Cisalpine Gaul and Italy proper   The crossing of the Rubicon was probely near the rightmost point, on the Adriatic.

So, as you can see, it is very easy to waste a lot of time, creating something (otherwise compleatly useless),
  just to satisfy one's curiosity (or just to see if it can be done).