ST_Cutter(): a quick intro

About ST_Cutter()

1. The Input table is always expected to declare a Primary Key and must contain a Geometry column of the POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING or MULTIPOLYGON type.
2. The Blade table is always expected to declare a Primary Key and must contain a Geometry column of the POLYGON or MULTIPOLYGON type.
3. All individual cut fragments will be stored into the Output table:
   • the Output Geometry will always preserve the name and SRID declared by the corresponding Input column.
   • the Geometry type will be always be one between POINT, LINESTRING or POLYGON because all cut fragments will be stored as simple features on distinct rows.
   • all mother-child relationships will be fully preserved by saving the Primary Key values allowing to trace back Input and Blade pairs giving birth to each single fragment.
   • any fragment eventually falling outside any Blade will conventionally reference a NULL Blade.
4. Both Input and Blade datasets can indifferently be located either on the MAIN database or on any other attached DB.
5. The Output dataset should not exists and will always be created into the MAIN database.

An example of how to use ST_Cutter()

You can download a sample DB-file used in this example. This sample DB was created by rearranging the original datasets published by Tuscany Region as Open Data and contains two tables: railways: railway lines of Tuscany Region. Linestring geometries, SRID=3003, released on CC-BY-SA license terms. comuni: administrative boundaries of Tuscan Municipalities. MultiPolygon type, SRID=3003, released on CC-BY license terms. The problem There are several railway lines, each one of them possibly traversing many Municipalities. Few portions of the Railway Networks fall outside Tuscany. We are required to split / cut all railway lines in such a way that each fragment will exactly fit in just a single Municipality and carefully preserving the overall network topology. As a side condition we are required to keep an exact trace for every fragment of the belonging Railway and Municipality (if any).

ATTACH DATABASE 'c:/users/myname/downloads/cutter-test.sqlite' AS test;

SELECT ST_Cutter('test', 'railways', 'geom', 'test', 'comuni', 'geom', 'out_railways', 1, 1);

1. we'll start this example by connecting a new DB-file named cutter-out.sqlite
2. then we'll attach the sample DB (cutter-test.sqlite).
3. and finally we'll invoke the ST_Cutter() SQL function:
4. we'll use test.railways as the Input table; all Geometries to be cut are expected to be found into a column of this table named geom.
5. test.coumi will act as the Blade table: all blade Geometries are expected to be found into a column of this table named geom.
6. a new MAIN.out_railways table will be created in order to store all individual cut fragments.
7. the first (optional) 1 argument requests to handle the whole operation as a single monolithic transaction (default value is 0, but this will cause an extreme slowness).
8. the second (optional) 1 argument requests to allocate directly in RAM all Temporary Tables required by the intermediate processing steps (not entirely safe, but really faster). The default value is 0 (Temporary Tables will be created on the file system).

SELECT PK_UID, input_railways_fid, blade_comuni_fid, n_geom, res_prog, geom
FROM out_railways
ORDER BY input_railways_fid, n_geom, res_prog;

• the PK_UID column is a Primary Key uniquely identifying each fragment.
• the input_railways_fid column relationally references the corresponding Primary Key of the Input table (that is it identifies the mother Railway Line giving birth to each child fragment).
• the blade_comuni_fid column relationally references the corresponding Primary Key of the Blade table (that is it identifies the Municipality giving birth to each child fragment).
  This column can actually contain NULL values for all fragments falling outside any Blade.
• the n_geom column contains an index with identical meaning of the one supported by the ST_GeometryN() SQL function (that is the progressive number of the elementary item into a multi-part Geometry).
• the res_prog column simply contains a progressive index intended to reaggreate all fragments in the right sequence so to get back again the initial (uncut) Geometry.

CREATE TABLE firenze AS
SELECT * 
FROM test.comuni
WHERE name = 'FIRENZE';

SELECT RecoverGeometryColumn('firenze', 'geom', 3003, 'MULTIPOLYGON', 'XY');

DETACH DATABASE test;

CREATE TABLE firenze_railways AS
SELECT r.pk_uid, r.input_railways_fid, r.blade_comuni_fid, r.n_geom, r.res_prog, r.geom
FROM firenze AS c
JOIN out_railways AS r ON (r.blade_comuni_fid = c.fid);

SELECT RecoverGeometryColumn('firenze_railways', 'geom', 3003, 'LINESTRING', 'XY');

1. we'll create a firenze table just containing the administrative boundaries of the Municipality of Firenze; and we'll then recover a genuine Geometry for this table.
2. we'll then detach the test DB.
3. and finally we'll create a firenze_railways table containing all Railway Line fragments covering the Municipality of Firenze; then we'll recover a genuine Geometry for this table.

The map shown on the right visually represents the results returned by ST_Cutter(). As you can see all Railway Lines covering Firenze have been precisely cut exactly where they intersect the administrative boundary. The overall topological consistency of the Railway Network has been fully preserved; all matching fragments located on adjacent Municipalities will precisely snap each other.