-- Adding points
-- <start id="my_geographies_adding_points" /> --
set search_path=ch02,public;
CREATE TABLE ch02.my_geographies
(id serial NOT NULL PRIMARY KEY, name varchar(20)
	, my_point geography(POINT));
INSERT INTO my_geographies (name,my_point) 
VALUES ('Home',ST_GeogFromText('POINT(0 0)')); 
INSERT INTO my_geographies (name,my_point) 
VALUES ('Pizza 1',ST_GeogFromText('POINT(1 1)')) ;
INSERT INTO my_geographies (name,my_point) 
VALUES ('Pizza 2',ST_GeogFromText('POINT(1 -1)'));
-- <end id="my_geographies_adding_points" /> --

-- <start id="my_geometries_adding_points" /> --
set search_path=ch02,public;
ALTER TABLE my_geometries ADD COLUMN
  my_point geometry(POINT);
INSERT INTO my_geometries (name,my_point) 
VALUES ('Home',ST_GeomFromText('POINT(0 0)')); 
INSERT INTO my_geometries (name,my_point) 
VALUES ('Pizza 1',ST_GeomFromText('POINT(1 1)')) ;
INSERT INTO my_geometries (name,my_point) 
VALUES ('Pizza 2',ST_GeomFromText('POINT(1 -1)'));
-- <end id="my_geometries_adding_points" /> --

-- our first how far --
-- <start id="my_geographies_how_far_home_pizza" /> --
SELECT h.name As house, p.name As restaurant
  , ST_Distance(h.my_point, p.my_point) As dist
 FROM (SELECT name, my_point 
 		FROM ch02.my_geographies
 			WHERE name = 'Home') As h
 	CROSS JOIN
	(SELECT name, my_point
		FROM ch02.my_geographies
			WHERE name LIKE 'Pizza%') As p;
-- <end id="my_geographies_how_far_home_pizza" /> --

-- <start id="my_points" /> --
CREATE TABLE ch02.my_points 
 (id serial NOT NULL PRIMARY KEY
    , p geometry(POINT)
    , pz geometry(POINTZ)
    , pm geometry(POINTM)
    , pzm geometry(POINTZM)
    , p_srid geometry(POINT,4269) );
INSERT INTO ch02.my_points(p, pz, pm
   , pzm, p_srid)
 VALUES 
    ( ST_GeomFromText('POINT(1 -1)') 
   ,  ST_GeomFromText('POINT Z(1 -1 1)') 
   ,  ST_GeomFromText('POINT M(1 -1 1)') 
   ,  ST_GeomFromText('POINT ZM(1 -1 1 1)') 
   ,  ST_GeomFromText('POINT(1 -1)',4269) )
 ;
-- <end id="my_points" /> --

-- <start id="vw_postgis_reg" /> --
CREATE OR REPLACE VIEW ch02.vw_pois
AS 
SELECT id, name
 , CAST( ST_Transform(geom,2163) AS geometry(POINT,2163) ) As geom
FROM pois;
-- <end id="vw_postgis_reg" /> --

-- geometry_columns mangement functions example --
-- <start id="updategeometrysrid_approach" /> --
SELECT UpdateGeometrySRID('us_states'
	, 'geom', 4326);
-- <end id="updategeometrysrid_approach" /> --

-- <start id="updategeometrysrid_equivalent" /> --
ALTER TABLE us_states
  ALTER COLUMN geom
     TYPE geometry(MULTIPOLYGON,4326) USING ST_SetSRID(geom,4326);
-- <end id="updategeometrysrid_equivalent" /> --

-- <start id="updategeometrysrid_cast" /> --
ALTER TABLE osm_roads
  ALTER COLUMN way
     TYPE geography(MULTIPOLYGON,4326) 
      USING ST_Transform(way,4326)::geography;
-- <end id="updategeometrysrid_cast" /> --

-- Listing: Adding linestrings
-- <start id="my_geometries_add_linestrings" /> --
set search_path=ch02,public;
CREATE TABLE my_geometries
(id serial NOT NULL PRIMARY KEY, name varchar(20)
	,   my_linestrings geometry(LINESTRING));  -- <co id="my_geometries_add_linestrings_1" /> --
	
INSERT INTO my_geometries (name,my_linestrings) -- <co id="my_geometries_add_linestrings_2" /> --
VALUES ('Linestring Open',
  ST_GeomFromText('LINESTRING(0 0,1 1,1 -1)')); 
  
INSERT INTO my_geometries (name,my_linestrings)   -- <co id="my_geometries_add_linestrings_3" /> --
VALUES ('Linestring Closed',
  ST_GeomFromText('LINESTRING(0 0,1 1,1 -1, 0 0)'));
-- <end id="my_geometries_add_linestrings" /> --
-- #1 2D column definition
-- #2 open
-- #3 closed


-- Add a polygon no holes
-- Listing: Adding linestrings
-- <start id="my_geometries_add_polygons" /> --
ALTER TABLE ch02.my_geometries
	ADD COLUMN my_polygons geometry(POLYGON);
INSERT INTO ch02.my_geometries (name,my_polygons)
VALUES ('Triangle',
  ST_GeomFromText('POLYGON((0 0, 1 1, 1 -1, 0 0))'));
-- <end id="my_geometries_add_polygons" /> --
  
  
-- polygon with 2 holes
-- <start id="my_geometries_polygon_holes" /> --
INSERT INTO ch02.my_geometries (name,my_polygons)
VALUES ('Square with 2 holes',
  ST_GeomFromText('POLYGON(
  (-0.25 -1.25,-0.25 1.25,2.5 1.25,2.5 -1.25,-0.25 -1.25),
  (2.25 0,1.25 1,1.25 -1,2.25 0),(1 -1,1 1,0 0,1 -1))'));
-- <end id="my_geometries_polygon_holes" /> --

-- Listing Forming geometrycollections from constituent geometries
-- <start id="code_geometrycollection" /> --
SELECT ST_AsText(ST_Collect(the_geom))
FROM (
SELECT ST_GeomFromText('MULTIPOINT(-1 1, 0 0, 2 3)') As the_geom
UNION ALL
SELECT ST_GeomFromText('MULTILINESTRING((0 0,0 1,1 1),
  (-1 1,-1 -1))') As the_geom
UNION ALL
SELECT ST_GeomFromText('POLYGON((-0.25 -1.25,-0.25 1.25,
  2.5 1.25,2.5 -1.25,-0.25 -1.25), 
  (2.25 0,1.25 1,1.25 -1,2.25 0),
 (1 -1,1 1,0 0,1 -1))') As the_geom) As foo;
-- <end id="code_geometrycollection" /> --


-- <start id="code_geometrycollectionm" /> --
SELECT ST_AsEWKT(ST_Collect(the_geom)) FROM (
SELECT ST_GeomFromEWKT('MULTIPOINTM(-1 1 4, 0 0 2, 2 3 2)') As the_geom
UNION ALL
SELECT ST_GeomFromEWKT('MULTILINESTRINGM((0 0 1,0 1 2,1 1 3),
  (-1 1 1,-1 -1 2))') As the_geom
UNION ALL 
SELECT ST_GeomFromEWKT('POLYGONM((-0.25 -1.25 1,-0.25 1.25 2,
  2.5 1.25 3,2.5 -1.25 1,-0.25 -1.25 1), 
  (2.25 0 2,1.25 1 1,1.25 -1 1,2.25 0 2), 
(1 -1 2,1 1 2,0 0 2,1 -1 2))') As the_geom) As foo;
-- <end id="code_geometrycollectionm" /> --

-- Section Curved Geometries
-- Listing Building circularstrings
-- <start id="code_circularstring2" /> --
ALTER TABLE ch02.my_geometries
 ADD COLUMN ch02.my_circular_strings geometry(CIRCULARSTRING);
INSERT INTO ch02.my_geometries(name,my_circular_strings)
VALUES ('Circle',
 ST_GeomFromText('CIRCULARSTRING(0 0,2 0, 2 2, 0 2, 0 0)')),
('Half Circle',
 ST_GeomFromText('CIRCULARSTRING(2.5 2.5,4.5 2.5, 4.5 4.5)')),
('Several Arcs',
 ST_GeomFromText('CIRCULARSTRING(5 5,6 6,4 8, 7 9, 9.5 9.5,
   11 12, 12 12)'));
-- <end id="code_circularstring2" /> --
   
-- add a compound curve constrained column and insert compound curve
-- <start id="code_compoundcurve" /> --
ALTER TABLE ch02.my_geometries 
	ADD COLUMN my_compound_curves geometry(COMPOUNDCURVE);
INSERT INTO ch02.my_geometries(name,my_compound_curves)
VALUES ('Road with curve', 
  ST_GeomFromText('COMPOUNDCURVE((2 2, 2.5 2.5), 
  CIRCULARSTRING(2.5 2.5,4.5 2.5, 3.5 3.5), (3.5 3.5, 2.5 4.5, 3 5))'));
-- <end id="code_compoundcurve" /> --

--Listing Creating curved polygons
-- <start id="code_curvepolygon" /> --
ALTER TABLE ch02.my_geometries
  ADD COLUMN my_curve_polygons geometry(CURVEPOLYGON);

INSERT INTO ch02.my_geometries(name,my_curve_polygons)
VALUES ('Solid Circle', ST_GeomFromText('CURVEPOLYGON(
  CIRCULARSTRING(0 0,2 0, 2 2, 0 2, 0 0))')), 
  ('Circle t hole', 
  ST_GeomFromText('CURVEPOLYGON(CIRCULARSTRING(2.5 2.5,4.5 2.5, 
  4.5 3.5, 2.5 4.5, 2.5 2.5), 
  (3.5 3.5, 3.25 2.25, 4.25 3.25, 3.5 3.5) )') ), 
('T arcish hole', 
  ST_GeomFromText('CURVEPOLYGON((-0.5 7, -1 5, 3.5 5.25, -0.5 7), 
CIRCULARSTRING(0.25 5.5, -0.25 6.5, -0.5 5.75, 0 5.75, 0.25 5.5))'));
-- <end id="code_curvepolygon" /> --
--

-- making rasters --
-- <start id="code_create_raster" /> --
CREATE TABLE ch02.my_rasters( -- <co id="co_code_create_raster_1" /> 
	rid SERIAL PRIMARY KEY
  , name varchar(150)
  , rast raster);

INSERT INTO ch02.my_rasters(name,rast)
 SELECT 'quad ' || x::text || ' ' || y::text
 	, ST_AddBand(  -- <co id="co_code_create_raster_3" /> --
 	    ST_MakeEmptyRaster(90, 60 -- <co id="co_code_create_raster_2" />
 	       , (x-3)*90, 90 + (1 - y)*60, 1, -1, 0, 0, 4326)
 	   , '16BUI'::text, 0)
 FROM generate_series(1,4) As x 
 	CROSS JOIN generate_series(1,3) As y;
--  <end id="code_create_raster" /> --	
-- 1 create table for rasters
-- 2 add 90x45 pixel rasters wgs 84 longlat
-- 3 temperature band

-- <start id="code_raster_addband" /> --
UPDATE ch02.my_rasters SET rast = ST_AddBand(rast, '8BUI'::text,0);
-- <end id="code_raster_addband" /> --

-- <start id="code_raster_applyconstraints" /> --
SELECT AddRasterConstraints('ch02', 'my_rasters'::name, 'rast'::name);
-- <end id="code_raster_applyconstraints" /> --

-- Listing burn geometry into raster
-- <start id="code_burn_geometry_raster_world" /> --
UPDATE ch02.my_rasters SET rast =     -- <co id="code_burn_geometry_raster_world_1a" />
	ST_AddBand(ST_MapAlgebraExpr(rast, 1, '16BUI', '300 + [rast.y]*' || 0.25::text || '::integer' , NULL), ST_Band(rast,2) )
  WHERE ST_Intersects(ST_MakeEnvelope(-180,-30,180,20,4326), rast );

SELECT ST_Value(rast,ST_SetSRID(ST_Point(1,1),4326))
FROM ch02.my_rasters
WHERE ST_Intersects(ST_SetSRID(ST_Point(1,1),4326), rast );
		
-- <end id="code_burn_geometry_raster_world" /> ---

-- #1 - replace existing raster tile if any part 10 degrees of equator
-- #2 - 2 band raster formed from a new band and original band 2
-- #3 - new raster band where our hot area is set to 315 Kelvin, aligned with table tile and extent same as our table tile
-- #4 -- original band 2

-- <start id="code_query_raster_columns" /> --
SELECT r_table_name As tname
	, r_raster_column As cname, srid
	, scale_x As sx, scale_y As sy
    , blocksize_x As bx
	, blocksize_y As by
	, same_alignment As sa
	, num_bands As nb, pixel_types As ptypes
  FROM raster_columns
  WHERE r_table_schema = 'ch02';
-- <end id="code_query_raster_columns" /> --

-- visualize --
SELECT  geom, rid::text || ' ' || Box2D(geom)::text As display
FROM (
SELECT rid, rast,  (ST_DumpAsPolygons(rast)).geom
 	FROM ch02.my_rasters ) As foo;

-- making a topology and loading data --
-- <start id="code_create_topology_4326"/> --
SELECT CreateTopology('ch02_topology',4326); 
-- <end id="code_create_topology_4326"/> --

-- <start id="query_list_topologies" /> --
SELECT id, name, srid
 , precision, hasz
  FROM topology.topology;
-- <end id="query_list_topologies" /> --

--<start id="code_load_colorado" /> -- 
SELECT TopoGeo_AddLineString('ch02_topology' -- <co id="co_code_load_colorado_1" />
 , ST_GeomFromText('LINESTRING(-109.05304 39.195013
   ,-109.05304 41.000889, -104.897461 40.996484)',4326)
     ,0.001);

SELECT TopoGeo_AddLineString('ch02_topology' 
 , ST_GeomFromText('LINESTRING(-104.897461 40.996484
   ,-102.051744 40.996484, -102.051744 40.003029)',4326)
     ,0.001);

SELECT TopoGeo_AddLineString('ch02_topology' --3
 , ST_GeomFromText('LINESTRING(-102.051744 40.003029
    ,-102.04874 36.992682,-104.48204 36.992682)',4326)
     ,0.001);
     
SELECT TopoGeo_AddLineString('ch02_topology'   --4
 , ST_GeomFromText('LINESTRING(-104.48204 36.992682
   ,-109.045226 36.999077, -109.05304 39.195013)',4326)
     ,0.001);

SELECT ST_GetFaceGeometry('ch02_topology',1); -- <co id="co_code_load_colorado_2" />

--<end id="code_load_colorado" /> --

--<start id="code_load_colorado_highways" /> --
SELECT TopoGeo_AddLineString('ch02_topology' -- <co id="co_code_load_colorado_highways_1" /> --
 , ST_GeomFromText('LINESTRING(-109.05304 39.195013
     ,-108.555908 39.108751, -105.021057 39.717751
     ,-102.051744 40.003029)',4326)
     ,0.001);
-- <co id="co_code_load_colorado_highways_2" /> --
5

-- <co id="co_code_load_colorado_highways_3" /> --
SELECT TopoGeo_AddLineString('ch02_topology'
 , ST_GeomFromText('LINESTRING(-104.897461 40.996484
   , -105.021057 39.717751,-104.798584 38.814031
   , -104.48204 36.992682)',4326) 
   ,0.001);
-- <co id="co_code_load_colorado_highways_4" /> --
7
8

--<end id="code_load_colorado_highways" /> --
--#1 I-70 5
--#2 output edges of I-70
--#3 I-25 (breaks I-70 into edges 5,6)
--#4 output I-25 edges
-- <start id="code_highways_topogeom"/> --
CREATE TABLE ch02.highways_topo(highway varchar(20) PRIMARY KEY); -- <co id="co_code_highways_topogeom_1"/> --

SELECT AddTopoGeometryColumn('ch02_topology', 'ch02'
	, 'highways_topo' -- <co id="co_code_highways_topogeom_2"/> --
	, 'topo', 'LINESTRING');
-- <end id="code_highways_topogeom"/> --
--#1 create table
--#2 add topo column

-- <start id="code_highways_createtopogeom"/> --
INSERT INTO ch02.highways_topo(highway, topo) 
VALUES ( 'I70'
   , CreateTopoGeom('ch02_topology' -- <co id="co_code_highways_createtopogeom_1"/> --
     , 2 -- <co id="co_code_highways_createtopogeom_2"/> --
     , 1 -- <co id="co_code_highways_createtopogeom_3"/> --
     , '{{5,2},{6,2}}'::topoelementarray) ) ; -- <co id="co_code_highways_createtopogeom_4"/> --
-- <end id="code_highways_createtopogeom"/> --
-- #1 topology
-- #2 topogeom type (2 lineal)
-- #3 layer id - 1
-- #4 edges
	
-- <start id="code_highways_totopogeom"/> --
INSERT INTO ch02.highways_topo(highway, topo) 
SELECT 'I25', toTopoGeom( -- <co id="co_code_highways_totopogeom_1"/> --
 ST_GeomFromText('LINESTRING(-104.897461 40.996484
   , -105.021057 39.717751,-104.798584 38.814031
   , -104.48204 36.992682)',4326) -- <co id="co_code_highways_totopogeom_2"/> --
   , 'ch02_topology' -- <co id="co_code_highways_totopogeom_3"/> --
   ,1 -- <co id="co_code_highways_totopogeom_4"/> --
   ,0.001 -- <co id="co_code_highways_totopogeom_5"/> --
  );
-- <end id="code_highways_totopogeom" /> --
-- #1 from geometry
-- #2 geometry 
-- #3 topology
-- #4 layer id
-- #5 tolerance

-- <start id="query_topology_co_topo_elements" /> --
SELECT highway
  , (topo).* -- <co id="co_query_topology_co_topo_elements_1"/> --
  , GetTopoGeomElements(topo) As el -- <co id="co_query_topology_co_topo_elements_2"/> --
 FROM ch02.highways_topo
ORDER BY highway;
-- <end id="query_topology_co_topo_elements" /> --
-- #1 expand topo into subelements topology_id, layer_id, id, type
-- #2 set returning topoelements

-- <start id="output_topology_co_topo_elements" /> --
 highway | topology_id | layer_id | id | type |  el
---------+-------------+----------+----+------+-------
 I25     |           1 |        1 |  2 |    2 | {7,2}
 I25     |           1 |        1 |  2 |    2 | {8,2}
 I70     |           1 |        1 |  1 |    2 | {5,2}
 I70     |           1 |        1 |  1 |    2 | {6,2}
-- <end id="output_topology_co_topo_elements" /> --


-- <start id="query_topology_layers" > --
SELECT topology_id As tid, layer_id
, schema_name, table_name, feature_column
FROM topology.layer;

 tid | layer_id | schema_name | table_name    | feature_column
-----+----------+-------------+---------------+----------------
   1 |        1 | ch02        | highways_topo | topo
-- <end id="query_topology_layers" />
