-- https://postgis.us/presentations/postgres_vision_2022.sql
-- 
--https://postgis.us/presentations/PostgresVision2022_PostGIS.html
CREATE DATABASE gisdb;
ALTER DATABASE gisdb SET search_path=postgresvision2022,contrib,postgis,tiger,topology,public;
\c gisdb
CREATE SCHEMA IF NOT EXISTS contrib ;
--DROP SCHEMA IF EXISTS postgresvision2022 CASCADE;
CREATE SCHEMA IF NOT EXISTS postgresvision2022;

SET search_path=postgresvision2022,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();

-- New in PostGIS 3.3.
-- https://postgis.net/docs//manual-3.3/ST_Letters.html
-- geometry ST_Letters(text letters, optional json font);

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

SELECT ST_SetSRID(ST_Letters('Hello World'),4326);

-- 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) );

-- 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),
-- srid arg added in PostGIS 3.2
ST_Point(-71.0578344,42.3582699,4326) );

-- 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);

-- convert a whole row to json
SELECT ST_AsGeoJSON(hw)::jsonb
FROM hw;

-- 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 hw.name, sd.geom
FROM hw, ST_Subdivide(geom, 40) AS sd(geom)
WHERE name = 'Hello World';

-- requires geos 3.9+, subdivide on a grid precision
SELECT hw.name, sd.geom
FROM hw, ST_Subdivide(geom, 40, 0.5) AS sd(geom)
WHERE name = 'Hello World';

-- 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, -71.060

, 42.3584)
FROM hw
WHERE name = 'Hello World';

-- https://postgis.net/docs//manual-3.3/ST_ConcaveHull.html (leverages GEOS native support for GEOS 3.11+)
SELECT ST_Collect(geom,ST_ConcaveHull(geom,0.1)), ST_ConcaveHull(geom,0.1)
FROM hw
WHERE name = 'Hello World Start Here';

-- https://postgis.net/docs//manual-3.3/ST_SimplifyPolygonHull.html (requires GEOS 3.11+)
-- outter hull
SELECT ST_Collect(geom,ST_SimplifyPolygonHull(hw.geom,0.5)),
 ST_SimplifyPolygonHull(hw.geom,0.5)
FROM hw 
WHERE name = 'Hello World Start Here';

-- inner hull
SELECT ST_Collect(geom,ST_SimplifyPolygonHull(hw.geom,0.5,false)),
 ST_SimplifyPolygonHull(hw.geom,0.5,false)
FROM hw 
WHERE name = 'Hello World Start Here';


-- https://postgis.net/docs/ST_GeneratePoints.html
SELECT ST_GeneratePoints(geom,1000)
FROM hw
WHERE name = 'Hello World Start Here';

-- https://postgis.net/docs/ST_SquareGrid.html
SELECT g.*
FROM hw INNER JOIN ST_SquareGrid(0.75, geom) AS g On ST_Intersects(g.geom, hw.geom)
WHERE name = 'Hello World Start Here';

-- https://postgis.net/docs/ST_HexagonGrid.html
SELECT g.*
FROM hw INNER JOIN ST_HexagonGrid(0.75, geom) AS g On ST_Intersects(g.geom, hw.geom)
WHERE name = 'Hello World Start Here';

-- https://postgis.net/docs/ST_Subdivide.html
SELECT g.*
FROM hw , ST_Subdivide(geom,10) AS g
WHERE name = 'Hello World Start Here';

-- requires GEOS 3.9+ PostGIS 3.1+
-- https://postgis.net/docs/ST_Subdivide.html
SELECT g.*
FROM hw , ST_Subdivide(geom,10,0.5) AS g
WHERE name = 'Hello World Start Here';


-- 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;

SELECT ST_Collect(geom,ST_MinimumBoundingCircle(hw.geom))
FROM hw 
WHERE name = 'Hello World Start Here';



 
    