-- https://postgis.us/presentations/postgis_day_2021.sql
-- https://postgis.us/presentations/PostGISDay2021_PostGISExtensions.html
ALTER DATABASE gisdb SET search_path=postgisday2021,public,contrib,postgis,tiger,topology;

-- download extension files http://www.bostongis.com/postgisstuff/postgis_letters_extension.zip
-- https://www.postgresonline.com/article_pfriendly/302.html

-- extract and copy sql/* files into your postgresql/share/extension folder
CREATE EXTENSION IF NOT EXISTS postgis_letters SCHEMA postgis;

SET search_path=postgisday2021,public,contrib,postgis,topology;

-- # postgis_topology in words
DROP EXTENSION IF EXISTS postgis_topology CASCADE;
CREATE EXTENSION IF NOT EXISTS postgis_topology CASCADE;
DROP SCHEMA IF EXISTS postgisday2021_topo CASCADE;
-- create a new topology
-- https://postgis.net/docs/CreateTopology.html
-- integer CreateTopology(varchar topology_schema_name);
SELECT topology.CreateTopology('postgisday2021_topo');
SELECT * FROM postgisday2021_topo.face;
-- https://postgis.net/docs/ST_CreateTopoGeo.html
-- text ST_CreateTopoGeo(varchar atopology, geometry acollection);
-- initialize with data (converts geometry to relation)
SELECT topology.ST_CreateTopoGeo('postgisday2021_topo',
                    ST_LettersAsGeometry('Topology', 'kankin')
                                );
-- get faces geometries
-- https://postgis.net/docs/ST_GetFaceGeometry.html
-- geometry ST_GetFaceGeometry(varchar atopology, integer aface);
SELECT face_id, topology.ST_GetFaceGeometry('postgisday2021_topo',face_id)
FROM postgisday2021_topo.face
WHERE face_id > 0;


-- get edges
SELECT *
FROM postgisday2021_topo.edge;

SELECT node_id, geom
FROM postgisday2021_topo.node;

-- get nodes
SELECT geom
FROM postgisday2021_topo.node
UNION ALL
-- faces
SELECT topology.ST_GetFaceGeometry('postgisday2021_topo',face_id)
FROM postgisday2021_topo.face
WHERE face_id > 0;

SELECT 'node' As type, node_id AS el_id, geom
FROM postgisday2021_topo.node
UNION ALL
SELECT 'edge' As type, edge_id AS el_id, geom
FROM postgisday2021_topo.edge;
-- https://postgis.net/docs/manual-dev/ST_NewEdgesSplit.html
-- ST_NewEdgesSplit(varchar atopology, integer anedge, geometry apoint);

-- https://postgis.net/docs/ST_NewEdgesSplit.html
-- integer ST_NewEdgesSplit(varchar atopology, integer anedge, geometry apoint);
-- split the first edge at the second point
SELECT ST_NewEdgesSplit('postgisday2021_topo', edge_id, ST_PointN(geom,2))
FROM postgisday2021_topo.edge
WHERE edge_id = 1;

-- check again should see 2 node now for T
SELECT 'node' As type, node_id AS el_id, geom
FROM postgisday2021_topo.node
UNION ALL
SELECT 'edge' As type, edge_id AS el_id, geom
FROM postgisday2021_topo.edge;

-- building topogeoms
DROP TABLE IF EXISTS postgisday2021.topo_test;
CREATE TABLE postgisday2021.topo_test(id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, geom geometry(POLYGON,4326));

-- https://postgis.net/docs/AddTopoGeometryColumn.html
-- integer AddTopoGeometryColumn(varchar topology_name, varchar schema_name, varchar table_name, varchar column_name, varchar feature_type);
-- returns layer id of new topology
SELECT topology.AddTopoGeometryColumn(
	'postgisday2021_topo',
	'postgisday2021', 'topo_test', 'topo', 'POLYGON'); 
    
-- https://postgis.net/docs/toTopoGeom.html
-- topogeometry toTopoGeom(geometry geom, varchar toponame, integer layer_id, float8 tolerance);
-- \d+ postgisday2021.topo_test
INSERT INTO postgisday2021.topo_test(geom,topo)
SELECT dp.geom, topology.toTopoGeom(dp.geom, 'postgisday2021_topo', 1)
FROM ST_LettersAsGeometry('T', 'kankin') AS geom, 
ST_Dump(geom) AS dp;

SELECT (topo).*, topo::geometry--, i, ST_Simplify(topo,i)
FROM postgisday2021.topo_test;

INSERT INTO postgisday2021.topo_test(geom,topo)
SELECT dp.geom, toTopoGeom(dp.geom, 'postgisday2021_topo', 1)
FROM ST_LettersAsGeometry('To', 'kankin') AS geom, 
ST_Dump(geom) AS dp;

SELECT tt.id As row_id, (tt.topo).*, topo::geometry--, i, ST_Simplify(topo,i)
FROM postgisday2021.topo_test AS tt;

SELECT tt.id As row_id, tge.*
FROM postgisday2021.topo_test AS tt, topology.GetTopoGeomElements(tt.topo) AS tge;

SELECT t1.id As t1_id, t2.id AS t2_id, t1.topo::geometry
FROM postgisday2021.topo_test AS t1 
    INNER JOIN postgisday2021.topo_test AS t2
    ON topology.intersects(t1.topo, t2.topo)
WHERE t1.id <> t2.id;


-- # pgRouting in words
-- https://docs.pgrouting.org/
CREATE EXTENSION IF NOT EXISTS pgrouting SCHEMA postgis;
DROP TABLE IF EXISTS postgisday2021.pgr_roads;
DROP TABLE IF EXISTS postgisday2021.pgr_roads_vertices_pgr;
CREATE TABLE postgisday2021.pgr_roads(id bigint GENERATED ALWAYS AS IDENTITY, name text, geom geometry(LINESTRING), source bigint, target bigint );
INSERT INTO postgisday2021.pgr_roads(name, geom)
SELECT 'road ' || dp.path[1] AS name, dp.geom
FROM  ST_Boundary(ST_UnaryUnion(ST_LettersAsGeometry('pgRouting', 'kankin'))) AS g(g)
, ST_Dump(g.g) AS dp;

-- https://docs.pgrouting.org/latest/en/pgr_createTopology.html
/** varchar pgr_createTopology(text edge_table, double precision tolerance, text the_geom:='the_geom', text id:='id',
  text source:='source',text target:='target',
  text rows_where:='true', boolean clean:=false) **/
SELECT pgr_createTopology( 'postgisday2021.pgr_roads', 1,
                       'geom','id', clean => true);
                       
-- https://docs.pgrouting.org/latest/en/pgr_analyzeGraph.html /** varchar pgr_analyzeGraph(text edge_table, double precision tolerance, text the_geom => 'the_geom', text id => 'id', text source => 'source', text target =>'target',text rows_where:='true') **/                    
-- says we have 15 ring geometries                        
SELECT pgr_analyzegraph('postgisday2021.pgr_roads', 1, the_geom => 'geom');

-- inspect overlay nodes with edges
SELECT id, 'node' AS type, the_geom
FROM postgisday2021.pgr_roads_vertices_pgr
UNION ALL
SELECT id, 'edge' AS type, geom
FROM postgisday2021.pgr_roads;

-- lets make a path
DROP TABLE IF EXISTS postgisday2021.pgr_roads2;
DROP TABLE IF EXISTS postgisday2021.pgr_roads2_vertices_pgr;
CREATE TABLE postgisday2021.pgr_roads2(id bigint GENERATED ALWAYS AS IDENTITY, name text, geom geometry(LINESTRING), source bigint, target bigint, cost float );
-- 58 roads
INSERT INTO postgisday2021.pgr_roads2(name, geom, cost)
SELECT 'road ' || dp.path[1] AS name, dp.geom, ST_Length(geom)
FROM  ST_Boundary(ST_UnaryUnion(ST_LettersAsGeometry('pgRouting', 'kankin'))) AS g(g)
, ST_Union(g.g, ST_LongestLine(g.g,g.g)) AS gp
, ST_Dump(gp) AS dp;

-- https://docs.pgrouting.org/latest/en/pgr_createTopology.html
SELECT pgr_createTopology( 'postgisday2021.pgr_roads2', 1,
                       'geom','id', clean => true);
-- says we have 6 ring geometries                        
SELECT pgr_analyzegraph('postgisday2021.pgr_roads2', 1, the_geom => 'geom');

-- inspect overlay nodes with edges
SELECT id, 'node' AS type, the_geom, null AS cost, id AS source, null AS target
FROM postgisday2021.pgr_roads2_vertices_pgr
UNION ALL
SELECT id, 'edge' AS type, geom, cost, source, target
FROM postgisday2021.pgr_roads2;



-- https://docs.pgrouting.org/3.2/en/pgr_connectedComponents.html
-- pgr_connectedComponents(edges_sql)
-- lists all components reachable from each other
WITH cc AS (SELECT cc.*, n.the_geom 
FROM pgr_connectedComponents(
    'SELECT id, source, target, cost FROM postgisday2021.pgr_roads2'
) AS cc INNER JOIN postgisday2021.pgr_roads2_vertices_pgr AS n ON cc.node = n.id
)
SELECT cc.component, 
    ST_Collect( ST_Collect(r.geom, cc.the_geom) ORDER BY cc.seq) AS geom,
    array_agg(DISTINCT cc.seq ORDER BY cc.seq) AS nodes
FROM cc INNER JOIN postgisday2021.pgr_roads2 AS r ON cc.node IN(r.source,r.target)
GROUP BY cc.component;
-- https://docs.pgrouting.org/3.2/en/pgr_dijkstra.html
-- pgr_dijkstra(Edges SQL, start_vid,  end_vid  [, directed])
-- route 
WITH path AS (SELECT d.*, ST_Union( COALESCE(ST_Buffer(e.geom,0.4), n.the_geom) , ST_Buffer(n.the_geom,d.seq*0.7) ) AS geom 
FROM pgr_dijkstra(
    'SELECT id, source, target, cost FROM postgisday2021.pgr_roads2',
   1, 7
) AS d LEFT JOIN postgisday2021.pgr_roads2 AS e ON d.edge = e.id
    LEFT JOIN postgisday2021.pgr_roads2_vertices_pgr AS n ON d.node = n.id 
   ORDER BY d.seq    )
SELECT path.seq, geom
FROM path
UNION ALL
SELECT id, geom
FROM postgisday2021.pgr_roads2;

-- # PostGIS raster
CREATE EXTENSION IF NOT EXISTS postgis_raster SCHEMA postgis;
DROP TABLE IF EXISTS happy_raster;

-- https://postgis.net/docs/RT_ST_MakeEmptyRaster.html raster ST_MakeEmptyRaster(integer width, integer height, float8 upperleftx, float8 upperlefty, float8 scalex, float8 scaley, float8 skewx, float8 skewy, integer srid=unknown);
-- https://postgis.net/docs/RT_ST_AsRaster.html raster ST_AsRaster(geometry geom, raster ref, text[] pixeltype=ARRAY['8BUI'], double precision[] value=ARRAY[1], double precision[] nodataval=ARRAY[0], boolean touched=false);
-- https://postgis.net/docs/RT_ST_Union.html raster ST_Union(setof raster rast)
-- converting geometry to raster
WITH the_words AS
        (SELECT pos, ST_Translate(ST_LettersAsGeometry(word, 'kankin'), 0, -pos*100) As geom
                FROM  (VALUES (0,'HAPPY'),(1,'POSTGIS'),(2,'DAY')  ) AS my(pos, word)
        )
 , canvas
    AS (SELECT ST_MakeEmptyRaster((ST_XMAX(ext) - ST_XMin(ext))::integer, 
        (ST_YMAx(ext) - ST_YMin(ext))::integer,0, 0, 1, -1, 0, 0, 0) As rast
        FROM (SELECT ST_Extent(geom) As ext FROM the_words) As foo
            )
SELECT 1 AS id, ST_Union(ST_AsRaster(geom
     , canvas.rast
     , ARRAY['8BUI','8BUI', '8BUI']::text[]
     , ARRAY[pos*75 + 1,6,0], ARRAY[0,0,0]) 
        ) AS rast
    INTO happy_raster
     FROM  the_words CROSS JOIN canvas;
-- how many pixels
-- https://postgis.net/docs/RT_ST_Count.html
-- bigint ST_Count(raster rast, boolean exclude_nodata_value);
SELECT ST_Count(rast)
FROM happy_raster;

-- summary stats
-- https://postgis.net/docs/RT_ST_SummaryStats.html
-- summarystats ST_SummaryStats(raster rast, boolean exclude_nodata_value);
SELECT r.id, (st).*
FROM happy_raster AS r, ST_Histogram(rast,1) AS st;

-- revectorize by pixel value
SELECT  gv.*
FROM happy_raster, ST_DumpAsPolygons(rast,1) AS gv;

-- https://postgis.net/docs/RT_ST_PixelAsPolygons.html
-- output each pixel as a square
SELECT gv.*
FROM happy_raster, ST_PixelAsPolygons(rast,1) AS gv;

-- http://postgis.net/docs/postgis_gdal_enabled_drivers.html
SET postgis.gdal_enabled_drivers = 'ENABLE_ALL';
ALTER DATABASE gisdb SET postgis.gdal_enabled_drivers = 'ENABLE_ALL';
-- list of available output raster formats
SELECT *
FROM ST_GDALDrivers();

-- output as a PNG (there are many types of outputs)
-- paste in address bar of web browser
SELECT 'data:image/png;base64,' || encode(ST_AsPNG(
    ST_Resize(rast, 0.75,0.75)),'base64') 
     FROM  happy_raster;
     
-- # postgis extension in words
-- https://postgis.net/docs/ST_LineInterpolatePoints.html
-- geometry ST_LineInterpolatePoints(geometry a_linestring, float8 a_fraction, boolean repeat);
WITH word 
AS (SELECT 
        ST_Boundary(ST_Union(ST_LettersAsGeometry('LineInterpolatePoints', 'kankin')))
         As geom
   )
  SELECT ST_Collect(dp.geom, ST_LineInterpolatePoints(dp.geom, 0.25, true ) )
FROM word, ST_Dump(word.geom) AS dp;

-- https://postgis.net/docs/ST_Subdivide.html
-- ST_Subdivide(geometry geom, integer max_vertices=256, float8 gridSize = -1);
-- no need to overlay this one with original words
WITH word 
AS (SELECT ST_LettersAsGeometry('Subdivide', 'kankin') As geom)
  SELECT ST_Collect(sd)
FROM word 
    INNER JOIN 
    LATERAL ST_Subdivide(word.geom,20) AS sd ON ST_Intersects(word.geom, sd)
    GROUP BY word.geom;

-- https://postgis.net/docs/ST_GeneratePoints.html
--  ST_GeneratePoints( g geometry , npoints integer );
WITH word 
AS (SELECT ST_UnaryUnion(ST_LettersAsGeometry('GeneratePoints', 'kankin')) As geom )
SELECT i, ST_Collect(word.geom,ST_Collect(gp) ) AS gp_overlay
FROM word CROSS JOIN generate_series(100,1500,500) AS i
CROSS JOIN LATERAL ST_GeneratePoints(geom,i) AS gp
GROUP BY i, word.geom
ORDER BY i; 

-- https://postgis.net/docs/ST_Buffer.html
-- ST_Buffer(geometry g1, float radius_of_buffer, integer num_seg_quarter_circle);
WITH word 
AS (SELECT 
        ST_LettersAsGeometry('Buffer', 'kankin')
         As geom
   )
  SELECT ST_Collect(word.geom, ST_Buffer(word.geom, 10) )
FROM word;



-- https://postgis.net/docs/ST_FlipCoordinates.html
-- ST_FlipCoordinates(geometry geom, cstring ords);
WITH word 
AS (SELECT 
        ST_LettersAsGeometry('FlipCoordinates', 'kankin')
         As geom
   )
  SELECT ST_FlipCoordinates(word.geom)
FROM word;
    
-- ## Properties that are geometries
-- https://postgis.net/docs/ST_Boundary.html
--  ST_Boundary(geometry geom);
WITH word 
AS (SELECT ST_LettersAsGeometry('Boundary', 'kankin') As geom )
  SELECT ST_Boundary(geom)
FROM word;

-- https://postgis.net/docs/ST_Centroid.html
--  ST_Centroid(geometry geom);
WITH word 
AS (SELECT ST_LettersAsGeometry('Centroid', 'kankin') As geom )
  SELECT ST_Collect(geom,ST_Centroid(geom))
FROM word;

-- https://postgis.net/docs/ST_Envelope.html
--  ST_Envelope(geometry geom);
WITH word 
AS (SELECT ST_LettersAsGeometry('Envelope', 'kankin') As geom )
  SELECT ST_Collect(geom,ST_Envelope(geom))
FROM word;

-- Things that bound
-- https://postgis.net/docs/ST_BoundingDiagonal.html
--  ST_BoundingDiagonal(geometry geom, boolean fits=false);
WITH word 
AS (SELECT ST_LettersAsGeometry('BoundingDiagonal', 'kankin') As geom )
  SELECT ST_Collect(geom,ST_BoundingDiagonal(geom))
FROM word;

-- https://postgis.net/docs/ST_OrientedEnvelope.html
--  ST_OrientedEnvelope(geometry geom);
WITH word 
AS (SELECT ST_LettersAsGeometry('OrientedEnvelope', 'kankin') As geom )
  SELECT ST_Collect(geom,ST_OrientedEnvelope(geom))
FROM word;

-- https://postgis.net/docs/ST_MinimumBoundingCircle.html
--  ST_MinimumBoundingCircle(geometry geom);
WITH word 
AS (SELECT ST_LettersAsGeometry('MinimumBoundingCircle', 'kankin') As geom )
  SELECT ST_Collect(geom,ST_MinimumBoundingCircle(geom))
FROM word;

-- https://postgis.net/docs/ST_MaximumInscribedCircle.html
-- requires GEOS 3.9+
-- (geometry, geometry, double precision) ST_MaximumInscribedCircle(geometry geom);
WITH word 
AS (SELECT ST_UnaryUnion(ST_LettersAsGeometry('MaximumInscribedCircle', 'kankin')) As geom )
  SELECT ic.radius, ic.center, ic.nearest, ST_Collect(ARRAY[word.geom,  ST_Buffer(ic.center, ic.radius), ST_MakeLine(ic.center, ic.nearest)] )
FROM word, ST_MaximumInscribedCircle(word.geom) AS ic;

-- https://postgis.net/docs/ST_StartPoint.html
--  ST_StartPoint(geometry geom);
-- somebody forgot to document this works on geometry collections
WITH word 
AS (SELECT ST_LettersAsGeometry('StartPoint', 'kankin') As geom )
  SELECT ST_Collect(geom,ST_StartPoint(geom))
FROM word;

-- https://postgis.net/docs/ST_PointOnSurface.html
--  ST_PointOnSurface(geometry geom);
WITH word 
AS (SELECT ST_LettersAsGeometry('PointOnSurface', 'kankin') As geom )
  SELECT ST_Collect(geom,ST_PointOnSurface(geom))
FROM word;


-- https://postgis.net/docs/ST_DelaunayTriangles.html
-- DelaunayTriangles tolerance=10, flags = 1 (meaning collection of linestring)
-- collect is to overlay on original words
WITH word 
AS (SELECT ST_LettersAsGeometry('Triangle', 'kankin') As geom )
  SELECT ST_Collect(geom
     , ST_DelaunayTriangles(geom,10,1) )
FROM word;

-- hexagon grid overlaid with words
WITH word 
AS (SELECT ST_LettersAsGeometry('HexagonGrid', 'kankin') As geom)
  SELECT ST_Collect(word.geom, ST_Collect(hg.geom) )
FROM word 
    INNER JOIN 
    LATERAL ST_HexagonGrid(5,word.geom) AS hg ON ST_Intersects(word.geom, hg.geom)
    GROUP BY word.geom;
    
-- square grid overlaid with words 
-- https://postgis.net/docs/ST_SquareGrid.html
WITH word 
AS (SELECT ST_LettersAsGeometry('SquareGrid', 'kankin') As geom)
  SELECT ST_Collect(word.geom, ST_Collect(hg.geom) )
FROM word 
    INNER JOIN 
    LATERAL ST_SquareGrid(5,word.geom) AS hg ON ST_Intersects(word.geom, hg.geom)
    GROUP BY word.geom;
    


-- https://postgis.net/docs/ST_ConvexHull.html
WITH word 
AS (SELECT ST_LettersAsGeometry('Convex', 'kankin') As geom )
  SELECT ST_Collect(geom
     , ST_ConvexHull(geom) )
FROM word;
    
-- https://postgis.net/docs/ST_ConcaveHull.html
--ST_ConcaveHull(geometry geom, float target_percent, boolean allow_holes = false);
WITH word 
AS (SELECT ST_LettersAsGeometry('Concave', 'kankin') As geom )
  SELECT ST_Collect(geom
     , ST_ConcaveHull(geom,0.7) )
FROM word;

-- simplifying and smoothing functions
-- https://postgis.net/docs/ST_ReducePrecision.html
--ST_ReducePrecision(geometry g, float8 gridsize);
WITH word 
AS (SELECT ST_LettersAsGeometry('ReducePrecision', 'kankin') As geom )
SELECT i, ST_ReducePrecision(geom,i*0.5)
FROM word, generate_series(10,30,5) AS i; 



-- https://postgis.net/docs/ST_Split.html
--  ST_Split(geometry input, geometry blade);
WITH word 
AS (SELECT ST_UnaryUnion(ST_LettersAsGeometry('Split', 'kankin')) As geom,
        ST_Boundary(ST_LettersAsGeometry('Blade', 'kankin') ) As blade
   )
SELECT ST_Split(word.geom,word.blade ) AS gp_overlay
FROM word;

-- https://postgis.net/docs/ST_OffsetCurve.html
--  ST_OffsetCurve(geometry line, float signed_distance, text style_parameters='');
WITH word 
AS (SELECT 
        ST_LettersAsGeometry('OffsetCurve', 'kankin')
         As geom 
   )
  SELECT i, ST_Collect(word.geom, ST_Collect(oc) )
FROM word CROSS JOIN LATERAL  ST_Dump(word.geom) AS gd
    CROSS JOIN generate_series(-10,10,10) AS i
    CROSS JOIN LATERAL ST_OffsetCurve(ST_Boundary(gd.geom), i) AS oc
GROUP BY i, word.geom;


-- https://postgis.net/docs/ST_LongestLine.html
--   ST_LongestLine(geometry g1, geometry g2);
WITH word 
AS (SELECT 
        ST_LettersAsGeometry('LongestLine', 'kankin')
         As geom
   )
  SELECT ST_Collect(word.geom, ST_LongestLine(word.geom, word.geom) )
FROM word;

-- https://postgis.net/docs/ST_Rotate.html
-- ST_Rotate(geometry geomA, float rotRadians, geometry pointOrigin);
WITH word 
AS (SELECT 
        ST_LettersAsGeometry('Rotate', 'kankin')
         As geom
   )
  SELECT ST_Collect(word.geom, ST_Rotate(word.geom,pi()/2, ST_Centroid(word.geom) ) )
FROM word;

-- https://postgis.net/docs/ST_Translate.html
-- ST_Translate(geometry g1, float deltax, float deltay);
WITH word 
AS (SELECT 
        ST_LettersAsGeometry('Translate', 'kankin')
         As geom
   )
  SELECT ST_Collect(word.geom, ST_Translate(word.geom, 10,5) )
FROM word;

-- https://postgis.net/docs/ST_TransScale.html
-- ST_TransScale(geometry geomA, float deltaX, float deltaY, float XFactor, float YFactor);
WITH word 
AS (SELECT 
        ST_LettersAsGeometry('TransScale', 'kankin')
         As geom
   )
  SELECT i, ST_Collect(word.geom, ST_TransScale(word.geom, 10,5,i,i) )
FROM word, generate_series(0,2,0.5) As i;

-- https://postgis.net/docs/ST_VoronoiLines.html
--   ST_VoronoiLines( g1 geometry , tolerance float8 , extend_to geometry );
WITH word 
AS (SELECT 
        ST_LettersAsGeometry('VoronoiLines', 'kankin')
         As geom
   )
  SELECT ST_Collect(word.geom, ST_VoronoiLines(word.geom, 10) )
FROM word;

-- https://postgis.net/docs/ST_VoronoiLines.html
--   ST_VoronoiPolygons( g1 geometry , tolerance float8 , extend_to geometry );
WITH word 
AS (SELECT 
        ST_LettersAsGeometry('VoronoiPolygons', 'kankin')
         As geom
   )
  SELECT ST_Collect(word.geom, ST_VoronoiPolygons(word.geom, 10) )
FROM word;


--ST_SnapToGrid(geometry g, float8 gridsize);
WITH word 
AS (SELECT ST_LettersAsGeometry('SnapToGrid', 'kankin') As geom )
SELECT i, ST_SnapToGrid(geom,i*0.5)
FROM word, generate_series(20,30,5) AS i; 

-- https://postgis.net/docs/ST_SimplifyPreserveTopology.html
--  ST_SimplifyPreserveTopology(geometry geomA, float tolerance);
WITH word 
AS (SELECT ST_LettersAsGeometry('SimplifyPreserveTopology', 'kankin') As geom )
SELECT i, ST_SimplifyPreserveTopology(geom,i*0.5)
FROM word, generate_series(10,30,5) AS i; 

-- https://postgis.net/docs/ST_Difference.html
-- ST_Difference(geometry geomA, geometry geomB, float8 gridSize = -1);
WITH word 
AS (SELECT 
        ST_UnaryUnion(ST_LettersAsGeometry('Difference', 'kankin'))
         As geom1,
        ST_UnaryUnion(ST_LettersAsGeometry('Diff', 'kankin'))
         As geom2
   )
  SELECT ST_Difference(word.geom1,word.geom2)
FROM word;

-- https://postgis.net/docs/ST_SymDifference.html
-- ST_SymDifference(geometry geomA, geometry geomB, float8 gridSize = -1);
WITH word 
AS (SELECT 
        ST_UnaryUnion(ST_LettersAsGeometry('SymDifference', 'kankin'))
         As geom1,
        ST_UnaryUnion(ST_LettersAsGeometry('SymDiff', 'kankin'))
         As geom2
   )
  SELECT ST_SymDifference(word.geom1,word.geom2)
FROM word;

