SpatiaLite Cookbook: How to Sql samples |
Back to the SpatiaLite home page Previous Chapter Adminstration: Introduction Back to the Cookbook home page. Next Chapter Spatialite Tools (installing)
Extract the external boundry of San Marino from the surrounding Community boundries.
The original ISTAT-Data only contains data inside the Italian Republic
- the Community boundries surrounding San Marino form a common boundry with San Marino
- with ST_Union one Geometry will be created of the surrounding Communities
- the single Interior-Ring of this Geometry forms the external boundry of San Marino
- using ST_InteriorRingN this Interior-Ring will be extracted to create a POLYGON of San Marino
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 MULTIPOLYGONST_InteriorRingN result:
1 LINESTRING, which is closedST_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-RingST_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 Goal of the Sql
Is to demonstrate the use of:ST_Difference remove a portion of a Geometry (1st Parameter), with the area of another Geometry (2nd Parameter)
- the 1st Parameter Geometry is created with BuildMbr from the SpatialIndex entry of the second Geometry
- the 2nd Parameter Geometry of a country
- area data will be created for the 2 Parameter Geometries and the final result to compare with
SpatialIndex and St_Contains results, with a list of Cities, will be shown
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
),
Note:
- each : 1 Exterior-Ring, with 5 POINTs (closed LINESTRING)
The CreateSpatialIndex command will create a TABLE based on the following syntax:Containing the columns pkid [GeoPackage: id], as Primary Key of the original TABLE, and xmin,xmax,ymin,ymax
- 'idx_' static (GeoPackage: 'rtree_')
- table-name+'_'
- geometry-column-name
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 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 usingPtDistWithin will the check if the candidates are within the circle
- ST_Buffer, creating a circle around the starting POINT, created the distance in degrees
- 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 second part of the results will show:
- the title of the Query
- the given distance in meters
- the calculated distance degrees
The third part of the results will show:
- 5 columns of the the main TABLE
- the given distance in meters
- the calculated distance degrees
- 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.
from_result defines;
- distance_search_degrees: Calculates the search area from meters to degrees
- distance_degrees: Calculates the distance in degrees from the starting POINT
- distance_meters: Calculates the distance in meters from the starting POINT
- Exposes all columns defined in
source_data, that is defined as :
- distance_search_name: Some useful text describing the Query
- distance_search_meters: sets the value to be used for the calulations
- start_point: sets the value to be used as the starting POINT
- Exposes columns, with an alias of the source table to be used in the result output
- source_geometry_name: sets the value used in the SpatialIndex for f_geometry_column
- source_geometry: sets the value source table geometry-column to be used for the calulations f_geometry_column
- source_table_name: sets the value used in the SpatialIndex for f_table_name
- source_table: sets the source table to be used
This query has been designed to work with both:both of which the same structure.
- cities1000.txt: as TABLE 'cities1000'
- allCountries.txt: as TABLE 'countries'
For use with other tables, most of the needed changes can be done in from_result and source_data.
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:
and adding a WHERE condition (before the ORDER BY statement):
- '620 mille passus from: Foro Romano, Roma' AS distance_search_name,
- CvtFromKm((620*1.481)) AS distance_search_meters,
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).
Back to the SpatiaLite home page Previous Chapter Adminstration: Introduction Back to the Cookbook home page. Next Chapter Spatialite Tools (installing)