-- https://postgis.us/presentations/pgcon_2022.sql
-- 
--https://postgis.us/presentations/PGCon2022_PostGISExtensions.html
CREATE DATABASE gisdb;
ALTER DATABASE gisdb SET search_path=pgcon2022,contrib,postgis,tiger,topology,public;
CREATE SCHEMA IF NOT EXISTS contrib ;
--DROP SCHEMA IF EXISTS pgcon2022 CASCADE;
CREATE SCHEMA IF NOT EXISTS pgcon2022;

SET search_path=pgcon2022,public,contrib,postgis,topology,tiger;

-- Install postgis 
CREATE SCHEMA IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS postgis SCHEMA postgis;
-- check version of postgis
SELECT postgis_full_version();
SELECT postgis_extensions_upgrade();

-- install ST_Letters (if not running PostGIS >= 3.3)
-- https://postgis.net/docs//manual-3.3/ST_Letters.html
-- geometry ST_Letters(text letters, optional json font);
-- if running PostGIS < 3.3, grab code from here https://git.osgeo.org/gitea/postgis/postgis/src/branch/master/postgis/postgis_letters.sql

-- Hello World to a world defined in an unknown place
SELECT ST_Letters('Hello World');

SELECT ST_SRID(ST_Letters('Hello World'));

-- Hello World on a flat earth
SELECT * FROM spatial_ref_sys WHERE srid = 4326;
SELECT ST_SetSRID(ST_letters('Hello World'),4326);


-- Location in Hello World
SELECT ST_Intersects(
    ST_SetSRID(ST_Letters('Hello World'),4326),
-- srid arg added in PostGIS 3.2
ST_Point(3.39749,6.44938,4326) );

-- Location in Hello World
SELECT ST_Collect(
    ST_SetSRID(ST_Letters('Hello World'),4326),
-- srid arg added in PostGIS 3.2
ST_Point(3.39749,6.44938,4326) );

-- Location not in Hello World
SELECT ST_Intersects(
    ST_SetSRID(ST_Letters('Hello World'),4326),
-- srid arg added in PostGIS 3.2
ST_Point(-71.057834,42.35827,4326) );

-- Location not in Hello World
SELECT ST_Collect(
    ARRAY[ST_SetSRID(ST_Letters('Hello World'),4326),
         ST_Point(3.39749,6.44938,4326),
-- srid arg added in PostGIS 3.2
ST_Point(-71.057834,42.35827,4326) ] );

-- how far away - 71.95 degrees (how big is a degree?)
SELECT ST_Distance(
    ST_SetSRID(ST_Letters('Hello World'),4326),
-- srid arg added in PostGIS 3.2
ST_Point(-71.0578344,42.35827,4326) );

-- Hello World on a spheroidal earth
SELECT ST_SetSRID(ST_letters('Hello World'),4326)::geography(MULTIPOLYGON,4326);

-- 356794.71918131 meters = 356,794 km
SELECT ST_Distance(
    ST_SetSRID(ST_Letters('Hello World'),4326)::geography,
-- srid arg added in PostGIS 3.2
ST_Point(-71.0578344,42.3582699,4326)::geography );

-- create a table for our worlds
DROP TABLE IF EXISTS hw;
CREATE TABLE hw(name text PRIMARY KEY, geom geometry(MULTIPOLYGON,4326));

-- https://postgis.net/docs/ST_Scale.html
-- https://postgis.net/docs/ST_SetSRID.html
-- Hello World to some place on Earth
-- ST_Scale(geometry geom, geometry factor, geometry origin);
INSERT INTO hw(name, geom)
WITH a AS (
    SELECT ST_Scale(
       -- hello world
        ST_Letters('Hello World'), 
       -- scaling to 10%
        ST_Point(0.1,0.1) 
    ) AS geom
   )
SELECT 'Hello World' AS name,  ST_SetSRID(
       a.geom
    -- on earth wgs 84 long lat
    , 4326)
    -- enforce multipolygon earth
    ::geometry(MULTIPOLYGON, 4326) AS geom
FROM a;

SELECT * FROM hw;
CREATE INDEX ix_hw_geom_gist ON hw USING gist(geom);


-- ST_FlipCoordinates https://postgis.net/docs/ST_FlipCoordinates.html
INSERT INTO hw(name, geom)
SELECT 'Hello World FC', ST_FlipCoordinates(geom)
FROM hw
WHERE name = 'Hello World';

SELECT * FROM hw;

-- ST_Translate https://postgis.net/docs/ST_Translate.html
--  ST_Translate(geometry g1, float deltax, float deltay);
-- our origin started at 0,0 so the below shifts -75.. longitudanal and 45.. latitudinal
INSERT INTO hw(name, geom)
SELECT 'Hello World Start Here', ST_Translate(geom, -75.68252, 45.421647)
FROM hw
WHERE name = 'Hello World';

SELECT * FROM hw;

-- popular aggregate functions
-- ST_Collect aggregates geometries into one value, without dissolving boundaries
-- https://postgis.net/docs/ST_Collect.html
SELECT ST_Collect(geom), ST_GeometryType(ST_Collect(geom) )
FROM hw;

-- ST_Union aggregates geometries into one value, with dissolving boundaries
-- https://postgis.net/docs/ST_Union.html
SELECT ST_Union(geom), ST_GeometryType(ST_Union(geom) )
FROM hw;

-- address standardization https://postgis.net/docs/Extras.html#Address_Standardizer
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch SCHEMA contrib;
CREATE EXTENSION IF NOT EXISTS address_standardizer SCHEMA contrib;

-- uses no data 
CREATE EXTENSION IF NOT EXISTS address_standardizer_data_us SCHEMA contrib;
-- uses gazetteer, lexicon, and rules - https://postgis.net/docs/manual-3.2/standardize_address.html
SELECT * 
FROM json_each_text( to_json ( standardize_address(
    'contrib.us_lex',
    'contrib.us_gaz', 
    'contrib.us_rules', 
    '55 Laurier Ave East, Room 3156, Ottawa, ON K1N 6N5')
                              ) );
SELECT * 
FROM standardize_address(
    'contrib.us_lex',
    'contrib.us_gaz', 
    'contrib.us_rules', 
    '55 Laurier Ave East, Room 3156, Ottawa, ON K1N 6N5')

-- US geocoding, no batteries included, follow instructions to load data
-- refer to https://postgis.net/docs/postgis_installation.html#install_tiger_geocoder_extension for load instructions
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch SCHEMA contrib;
CREATE EXTENSION postgis_tiger_geocoder;

-- postgis_tiger_geocoder ships with it's own set of rules to abbreviate based on tiger data convention
SELECT *  FROM standardize_address('tiger.pagc_lex',
         'tiger.pagc_gaz', 'tiger.pagc_rules', 'One Devonshire Place, PH 301, Boston, MA 02109');

         
-- after loaded data:
SELECT ST_AsText(geomout), (addy).*, ST_SetSRID(geomout,4326)
FROM geocode('1600 Pennsylvania Avenue NW, Washington, DC 20500', 1);

SELECT rg.*, pprint_addy(rg.addy[1]), ST_SetSRID(rg.intpt[1],4326)
FROM reverse_geocode(ST_Point(-77.03511, 38.89867)) AS rg;


                    
-- # postgis topology of Hello world
DROP EXTENSION IF EXISTS postgis_topology CASCADE;
CREATE EXTENSION IF NOT EXISTS postgis_topology CASCADE;
DROP SCHEMA IF EXISTS pgcon2022_topo CASCADE;
-- create a new topology
-- https://postgis.net/docs/CreateTopology.html
-- integer CreateTopology(varchar topology_schema_name, integer srid, double precision prec);
SELECT topology.CreateTopology('pgcon2022_topo', 4326, 0.0001);

-- building topogeoms

-- 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(
	'pgcon2022_topo',
	'public', 'hw', 'topo', 'POLYGON'); 
    
-- https://postgis.net/docs/toTopoGeom.html
-- topogeometry toTopoGeom(geometry geom, varchar toponame, integer layer_id, float8 tolerance);
-- in psql: \d+ hw
-- add the representation of Hello World in terms of topology relations
UPDATE hw SET topo = topology.toTopoGeom(geom, 'pgcon2022_topo',1);

SELECT topo, (topo).*, topo::geometry
FROM hw;

-- lets check our edges
SELECT * FROM pgcon2022_topo.edge;

-- # PostGIS raster
CREATE EXTENSION IF NOT EXISTS postgis_raster SCHEMA postgis;
-- lets look at population  https://data.worldpop.org/GIS/Population_Density/Global_2000_2020_1km/2020/CAN/can_pd_2020_1km.tif
-- 
-- load with: raster2pgsql *.tif -C -t auto -e -Y pgcon2022.can_pop | psql -d gisdb -p 5432
SELECT *
FROM raster_columns;


-- cast rast to geometry to get idea of coverage
SELECT rast::geometry, ST_Width(rast), ST_Height(rast)
FROM can_pop;

-- how many pixels
-- https://postgis.net/docs/RT_ST_Count.html
-- bigint ST_Count(raster rast, boolean exclude_nodata_value);
SELECT ST_Count(rast,1), ST_Width(rast), ST_Height(rast), ST_NumBands(rast)
FROM can_pop;

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

-- revectorize by pixel value
SELECT  gv.*
FROM (SELECT * FROM can_pop LIMIT 1) AS r, 
   ST_DumpAsPolygons(rast,1) AS gv;
   
-- https://postgis.net/docs/RT_ST_Value.html
-- output each pixel as a square
SELECT ST_Value(rast, 1, geom) As rval
FROM 
   can_pop AS r INNER JOIN
    ST_GeomFromText('POINT(-75.6833037 45.423171)',4326)
   As geom
    ON ST_Intersects(r.rast, geom);
    
-- 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();


-- lets do with some real data in comes ogr_fdw to help
-- source at: https://github.com/pramsey/pgsql-ogr-fdw 
CREATE EXTENSION IF NOT EXISTS ogr_fdw SCHEMA postgis;
-- see data sources we support, note GPX and HTTP is listed
SELECT d
FROM unnest(ogr_fdw_drivers()) AS d
ORDER BY d;

DROP SERVER IF EXISTS fds_gpx CASCADE;
CREATE SCHEMA IF NOT EXISTS staging; 

-- grab a trace from here - https://www.openstreetmap.org/traces
CREATE SERVER fds_gpx
    FOREIGN DATA WRAPPER ogr_fdw
    OPTIONS(
        datasource '/gisdata/4381614.gpx'
            , format 'GPX');
-- ogr_all is catchall for give me everything in the data source
IMPORT FOREIGN SCHEMA ogr_all FROM SERVER fds_gpx INTO staging;
SELECT table_name
 FROM information_schema.tables 
 WHERE table_schema = 'staging';
 
SELECT * FROM staging.track_points;


-- mobilitydb 
-- https://mobilitydb.com/
SHOW shared_preload_libraries;
-- ALTER SYSTEM SET shared_preload_libraries = 'postgis-3';
-- need to restart postgresql service for above to take effect
CREATE EXTENSION IF NOT EXISTS mobilitydb SCHEMA postgis;
-- show what you are running -- MobilityDB 1.0.0, PostgreSQL 15beta1 (Ubuntu 15~beta1-1.pgdg22.04+1), PostGIS 3.3.0
SELECT mobilitydb_full_version();

DROP TABLE IF EXISTS gpx_trips;
CREATE TABLE gpx_trips(file_name text, 
                       trip tgeogpoint, 
                       CONSTRAINT  pk_gpx_trips PRIMARY KEY(file_name)  );
                       
-- convert each geom time pair into an instant then aggregate into a geog point                       
INSERT INTO gpx_trips(file_name, trip)
SELECT '4381614.gpx',   tgeogpoint_seq( array_agg( sdp.instant ORDER BY sdp.ts))
FROM 
    (SELECT DISTINCT ON(time) tgeogpoint_inst(geom::geography, (time::text || ' UTC')::timestamptz ) AS instant, time AS ts
     FROM 
         staging.track_points
     ORDER BY time, geom
    ) AS sdp;

CREATE INDEX ix_gpx_trips_trip_gist ON gpx_trips USING gist(trip);
-- cast to a PostGIS LINESTRING M (linestring with measure in epoch) SELECT to_timestamp(1653343114)
SELECT file_name, trip, trip::geography AS path, duration(trip), 
    speed(trip) AS speed, 
    ST_AsText(trip::geography) AS mgpath
FROM gpx_trips
WHERE trip && '[2022-05-23, 2022-05-25)'::period;

-- aggregate functions
SELECT file_name, 
    twavg(speed(trip)) AS weigted_average_speed,
    tcount(trip)
FROM gpx_trips 
GROUP BY file_name;

SELECT *
FROM gpx_trips WHERE trip && '[2022-05-23, 2022-05-25)'::period


-- # pgRouting in words
-- https://docs.pgrouting.org/
CREATE EXTENSION IF NOT EXISTS pgrouting SCHEMA postgis;
-- check version you are running
SELECT pgr_version();
-- full details of what dependencies you are running with
SELECT *
FROM pgr_full_version();

-- https://docs.pgrouting.org/latest/en/pgr_createTopology.html


-- lets make a path
DROP TABLE IF EXISTS pgr_roads2;
DROP TABLE IF EXISTS pgr_roads2_vertices_pgr;
CREATE TABLE pgr_roads2(id bigint GENERATED ALWAYS AS IDENTITY, name text, geom geometry(LINESTRING,4326), source bigint, target bigint, cost float );
-- 210 roads
INSERT INTO pgr_roads2(name, geom, cost)
SELECT 'road ' || dp.path[1] AS name, dp.geom, ST_Length(dp.geom::geography)
FROM hw CROSS JOIN ST_Boundary(hw.geom) AS g(g)
, ST_Union(g.g, ST_LongestLine(g.g,g.g)) AS gp
, ST_Dump(gp) AS dp
WHERE name = 'Hello World Start Here';

SELECT * FROM pgr_roads2;

-- https://docs.pgrouting.org/latest/en/pgr_createTopology.html
-- https://docs.pgrouting.org/latest/en/pgr_createVerticesTable.html
SELECT pgr_createTopology( 'pgr_roads2', 0.005,
                       'geom','id', clean => true);


-- 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 pgr_roads2'
) AS cc INNER JOIN pgr_roads2_vertices_pgr AS n ON cc.node = n.id
)
SELECT cc.component, 
    /**ST_Collect( cc.the_geom ORDER BY cc.seq) AS geom,**/
    array_agg(DISTINCT cc.seq ORDER BY cc.seq) AS nodes
FROM cc 
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.* , n.the_geom AS node_geom, e.geom As edge_geom
FROM pgr_dijkstra(
    'SELECT id, source, target, cost FROM pgr_roads2',
   1, 18, directed => false
) AS d LEFT JOIN pgr_roads2 AS e ON d.edge = e.id
    LEFT JOIN pgr_roads2_vertices_pgr AS n ON d.node = n.id 
   ORDER BY d.seq    )
SELECT 0, ST_Union(edge_geom)
FROM path
UNION ALL
SELECT seq, node_geom
FROM path;



-- STOP HERE --  
-- # 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_Letters('LineInterpolatePoints')))
         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_Letters('Subdivide') 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 );
-- if need set of points generated to be always the same use seed
--  ST_GeneratePoints( g geometry , npoints integer, seed integer );

WITH hw(geom) AS (
    SELECT ST_Letters('Hello World')
  )
SELECT i, gp
FROM hw CROSS JOIN generate_series(10,2000,500) AS i
, ST_GeneratePoints(geom,i) AS gp
UNION ALL 
SELECT 0, hw.geom
FROM hw
ORDER BY 1; 

-- 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_Letters('Buffer')
         As geom
   )
  SELECT ST_Collect(word.geom, ST_Buffer(word.geom, 10) )
FROM word;

    
-- ## Properties that are geometries
-- https://postgis.net/docs/ST_Boundary.html
--  ST_Boundary(geometry geom);
WITH word 
AS (SELECT ST_Letters('Boundary') As geom )
  SELECT ST_Boundary(geom)
FROM word;

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

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


-- https://postgis.net/docs/ST_BoundingDiagonal.html
--  ST_BoundingDiagonal(geometry geom, boolean fits=false);
WITH word 
AS (SELECT ST_Letters('BoundingDiagonal') 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_Letters('OrientedEnvelope') 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_Letters('MinimumBoundingCircle') 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);
-- https://anitagraser.com/2022/05/03/inscribed-and-bounding-circles-in-postgis/
WITH word 
AS (SELECT ST_UnaryUnion(ST_Letters('MaximumInscribedCircle')) 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_Letters('StartPoint') 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_Letters('PointOnSurface') 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_Letters('Triangle') As geom )
  SELECT ST_Collect(geom
     , ST_DelaunayTriangles(geom,10,1) )
FROM word;

-- hexagon grid overlaid with words
WITH word 
AS (SELECT ST_Letters('HexagonGrid') 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_Letters('SquareGrid') 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_Letters('Convex') 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_Letters('Concave') 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);
-- geos 3.9+ required
WITH word 
AS (SELECT ST_Letters('ReducePrecision') As geom )
SELECT i, ST_ReducePrecision(geom,i*0.5)
FROM word, generate_series(10,30,5) AS i; 

-- https://postgis.net/docs/ST_OffsetCurve.html
--  ST_OffsetCurve(geometry line, float signed_distance, text style_parameters='');
WITH word 
AS (SELECT 
        ST_Letters('OffsetCurve')
         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_Letters('LongestLine')
         As geom
   )
  SELECT ST_Collect(word.geom, ST_LongestLine(word.geom, word.geom) )
FROM word;

-- https://postgis.net/docs/ST_ShortestLine.html
--   ST_ShortestLine(geometry g1, geometry g2);
SELECT t1.name AS t1_name, t2.name AS t2_name, t1.geom AS t1_geom, t2.geom AS t2_geom, 
 ST_Collect(ARRAY[t1.geom, t2.geom, ST_ShortestLine(t1.geom, t2.geom)]) 
FROM hw AS t1 INNER JOIN hw AS t2 ON NOT ST_Intersects(t1.geom, t2.geom);


-- https://postgis.net/docs/ST_Rotate.html
-- ST_Rotate(geometry geomA, float rotRadians, geometry pointOrigin);
WITH word 
AS (SELECT 
        ST_Letters('Rotate')
         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_Letters('Translate')
         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_Letters('TransScale')
         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_Letters('VoronoiLines')
         As geom
   )
  SELECT ST_Collect(word.geom, ST_VoronoiLines(word.geom, 1) )
FROM word;

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


--ST_SnapToGrid(geometry g, float8 gridsize);
WITH word 
AS (SELECT ST_Letters('SnapToGrid') 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_Letters('SimplifyPreserveTopology') 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_Letters('Difference'))
         As geom1,
        ST_UnaryUnion(ST_Letters('Diff'))
         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_Letters('SymDifference'))
         As geom1,
        ST_UnaryUnion(ST_Letters('SymDiff'))
         As geom2
   )
  SELECT ST_SymDifference(word.geom1,word.geom2)
FROM word;


 
    