
-- Segment slicing
-- <start id="code_segment_linestring_polygon" /> --
SELECT ST_Intersects(g1.geom1,g1.geom2) As they_intersect, 
  GeometryType(
  ST_Intersection(g1.geom1, g1.geom2)
   ) As intersect_geom_type
FROM (SELECT ST_GeomFromText('POLYGON((2 4.5,3 2.6,3 1.8,2 0,
  -1.5 2.2,0.056 3.222,-1.5 4.2,2 6.5,2 4.5))') As geom1, 
ST_GeomFromText('LINESTRING(-0.62 5.84,-0.8 0.59)') As geom2) AS g1;
-- <end id="code_segment_linestring_polygon" /> --

-- Listing Return our sales region diced up
-- <start id="code_sales_region_dicing" /> --
SELECT x || ' ' || y As grid_x_y                     -- <co id="co_code_sales_region_dicing_1" /> --
   , CAST(ST_MakeBox2d(
        ST_Point(-1.5 + x, 0 + y), 
        ST_Point(-1.5 + x + 2, 0 + y + 2)) As geometry) As geom2
FROM generate_series(0,3,2) As x 
    CROSS JOIN generate_series(0,6,2) As y;

SELECT ST_GeomFromText('POLYGON((2 4.5,3 2.6,3 1.8,2 0,-1.5 2.2, 
0.056 3.222,-1.5 4.2,2 6.5,2 4.5))') As geom1;                 -- <co id="co_code_sales_region_dicing_2" /> --

SELECT CAST(x AS text) || ' ' || CAST(y As text) As grid_xy  -- <co id="co_code_sales_region_dicing_3" /> --
 , ST_AsText(ST_Intersection(g1.geom1, g2.geom2)) As intersect_geom
FROM (SELECT ST_GeomFromText('POLYGON((2 4.5,3 2.6,3 1.8,2 0,
    -1.5 2.2,0.056 3.222,-1.5 4.2,2 6.5,2 4.5))') As geom1) As g1
INNER JOIN (SELECT x, y, 
  ST_MakeEnvelope(-1.5 + x, 0 + y, 
  -1.5 + x + 2, 0 + y + 2)  As geom2
FROM generate_series(0,3,2) As x 
    CROSS JOIN generate_series(0,6,2) As y) As g2 
ON ST_Intersects(g1.geom1,g2.geom2) ;
-- <end id="code_sales_region_dicing" /> --

--[1] squares to dice with
--[2] region
--[3] Dicing yields multiple records 

-- Section  Interior, exterior, and boundary of a geometry

-- Listing Create sample geometries to exercise intersect relationships
-- <start id="code_house_walkway_greeters" /> --
CREATE TABLE example_set(ex_name varchar(150) PRIMARY KEY, 
    the_geom geometry);
INSERT INTO example_set(ex_name, the_geom)
VALUES 
('A polygon with hole' 
  ,  ST_GeomFromText('POLYGON ((110 180, 110 335, 
    184 316, 260 335, 260 180, 209 212.51, 110 180), 
(160 280, 200 240, 220 280, 160 280))') )
, ('A point',ST_GeomFromText('POINT(110 245)')) 
, ('A linestring'
  , ST_GeomFromText('LINESTRING(110 245,200 260, 227 309)')) ,
('A multipoint'
   , ST_GeomFromText('MULTIPOINT(110 245,200 260)')) ;
-- <end id="code_house_walkway_greeters" /> --

-- Section  Contains and intersects -
-- <start id="code_contains_intersects" /> --
SELECT A.ex_name As a_name, B.ex_name As b_name, 
    ST_Contains(A.the_geom, B.the_geom) As a_co_b, 
    ST_Intersects(A.the_geom, B.the_geom) As a_in_b
FROM example_set As A CROSS JOIN example_set As B;
-- <end id="code_contains_intersects" /> --

-- Section  Covers and Covered By
-- Listing How is ST_Covers different from ST_Contains?
-- <start id="code_covers_contains" /> --
SELECT A.ex_name As a_name, B.ex_name As b_name, 
    ST_Covers(A.the_geom, B.the_geom) As a_co_b,
    ST_Intersects(A.the_geom, B.the_geom) As a_in_b
FROM example_set As A CROSS JOIN example_set As B
WHERE NOT (ST_Covers(A.the_geom, B.the_geom) = 
  ST_Contains(A.the_geom, B.the_geom));
-- <end id="code_covers_contains" /> --


-- Section Contains properly
-- Listing How is ST_ContainsProperly different from ST_Contains
-- <start id="code_containsproperly_contains" /> --
SELECT A.ex_name As a_name, B.ex_name As b_name
  , ST_ContainsProperly(A.the_geom, B.the_geom) As a_co_b
  , ST_Intersects(A.the_geom, B.the_geom) As a_in_b
FROM example_set As A CROSS JOIN example_set As B
WHERE NOT (ST_ContainsProperly(A.the_geom, B.the_geom) = 
   ST_Contains(A.the_geom, B.the_geom));
-- <end id="code_containsproperly_contains" /> --


-- Section Touching geometries
-- Which pairs of these touch?
-- <start id="code_touches_contains" /> --
SELECT A.ex_name As a_name, B.ex_name As b_name 
  , ST_Touches(A.the_geom, B.the_geom) As a_tou_b
  , ST_Contains(A.the_geom, B.the_geom) As a_co_b
FROM example_set As A CROSS JOIN example_set As B
WHERE ST_Touches(A.the_geom, B.the_geom) ;
-- <end id="code_touches_contains" /> --

-- Section Crossing geometries
-- which pairs cross?
-- <start id="code_crosses" /> --
SELECT A.ex_name As a_name, B.ex_name As b_name 
 , ST_Touches(A.the_geom, B.the_geom) As a_tou_b
 , ST_Contains(A.the_geom, B.the_geom) As a_co_b
FROM example_set As A CROSS JOIN example_set As B
WHERE ST_Touches(A.the_geom, B.the_geom) ;
-- <end id="code_crosses" /> --

-- Section  Bounding box and geometry comparators
-- <start id="code_box2d_geometry" /> --
SELECT ex_name, ST_Box2D(the_geom) As bbox2d , the_geom
FROM (
VALUES
 ('A line', ST_GeomFromEWKT('LINESTRING (3 5, 3.4 4.5, 4 5)'))
 , ('A multipoint',ST_GeomFromText('MULTIPOINT (4.4 4.75, 5 5)'))
 , ('A triangle'
 , ST_GeomFromText('POLYGON ((2 5, 1.5 4.5, 2.5 4.5, 2 5))') )
)
AS foo(ex_name, the_geom);
-- <end id="code_box2d_geometry" /> --




-- ST_OrderingEquals equality vs. Spatial Equality
-- <start id="code_orderingequals_equals" /> --
SELECT ex_name, ST_OrderingEquals(the_geom, the_geom) As g_oeq_g 
 , ST_OrderingEquals(the_geom, ST_Reverse(the_geom)) As g_oeq_rev
 , ST_OrderingEquals(the_geom, ST_Multi(the_geom)) AS g_oeq_m 
 , ST_Equals(the_geom, the_geom) As g_seq_g 
 , ST_EQuals(the_geom, ST_Multi(the_geom)) As g_seq_m
FROM (
VALUES 
('A 2d line', ST_GeomFromText('LINESTRING(3 5, 2 4, 2 5)') )
 , ('A point',ST_GeomFromText( 'POINT(2 5)'))  ,
 , ('A triangle'
     , ST_GeomFromText('POLYGON((3 5, 2.5 4.5
          , 2 5, 3 5))') )
, ('poly with self-inter'
   , ST_GeomFromText('POLYGON((2 0,0 0,1 1,1 -1, 2 0))')
)
)
AS foo(ex_name, the_geom);
-- <end id="code_orderingequals_equals" /> --


-- Section Bounding box equality
-- <start id="code_box_equals" /> --
SELECT ST_GeomFromText('LINESTRING (3 5, 3.4 4.5, 4 5)') 
  = ST_GeomFromText('POLYGON ((3 5, 3 4.5, 4 4.5, 3 5))') As op_eq ;
  
-- Result is t
-- <end id="code_box_equals" /> --

-- <start id="code_orderingequals" /> --
SELECT ST_OrderingEquals(
  ST_GeomFromText('LINESTRING (3 5, 3.4 4.5, 4 5)') 
 , ST_GeomFromText('POLYGON ((3 5, 3 4.5, 4 4.5, 3 5))') ) As op_same;
 
-- Result is f
-- <end id="code_orderingequals" /> --

-- Listing DISTINCT is not always DISTINCT
-- <start id="code_boundingbox_distinct" /> --
SELECT ST_AsText(the_geom) 	-- <co id="co_code_boundingbox_distinct_1" /> --
	FROM (SELECT ST_GeomFromEWKT('LINESTRING (3 5, 3.4 4.5, 4 5)')
UNION ALL
SELECT ST_GeomFromText('POLYGON ((3 5, 3 4.5, 4 4.5, 3 5))')

) As foo(the_geom);

SELECT ST_AsText(the_geom)	-- <co id="co_code_boundingbox_distinct_2" /> --
FROM (SELECT ST_GeomFromEWKT('LINESTRING (3 5, 3.4 4.5, 4 5)')
UNION
SELECT ST_GeomFromText('POLYGON ((3 5, 3 4.5, 4 4.5, 3 5))')

) As foo(the_geom);

SELECT DISTINCT the_geom -- <co id="co_code_boundingbox_distinct_3" /> --
FROM (SELECT ST_GeomFromEWKT('LINESTRING (3 5, 3.4 4.5, 4 5)')
UNION ALL
SELECT ST_GeomFromText('POLYGON ((3 5, 3 4.5, 4 4.5, 3 5))')

) As foo(the_geom);

SELECT DISTINCT ST_AsText(the_geom)	-- <co id="co_code_boundingbox_distinct_4" /> --
FROM (SELECT ST_GeomFromEWKT('LINESTRING (3 5, 3.4 4.5, 4 5)')
UNION ALL
SELECT ST_GeomFromText('POLYGON ((3 5, 3 4.5, 4 4.5, 3 5))')

) As foo(the_geom);
-- <end id="code_boundingbox_distinct" /> --
--[1] 2 records
--[2] 1 record
--[3] 1 record
--[4] 2 records

-- Listing: A count DISTINCT is not always a DISTINCT count
-- <start id="code_countdistinct_notdistinct" /> --
SELECT COUNT(DISTINCT the_geom)	-- <co id="co_code_countdistinct_notdistinct_1" /> --
 FROM (SELECT ST_GeomFromEWKT('LINESTRING (3 5, 3.4 4.5, 4 5)')
UNION ALL
SELECT ST_GeomFromText('POLYGON ((3 5, 3 4.5, 4 4.5, 3 5))')

) As foo(the_geom);
SELECT COUNT(DISTINCT the_geom)	-- <co id="co_code_countdistinct_notdistinct_2" /> --
 FROM (SELECT ST_GeomFromEWKT('LINESTRING (3 6, 3.4 4.5, 4 5)')
UNION ALL
SELECT ST_GeomFromText('POLYGON ((3 5, 3 4.5, 4 4.5, 3 5))')

) As foo(the_geom);

SELECT the_geom		-- <co id="co_code_countdistinct_notdistinct_3" /> --
 FROM (SELECT ST_GeomFromEWKT('LINESTRING (3 5, 3.4 4.5, 4 5)')
UNION ALL
SELECT ST_GeomFromText('POLYGON ((3 5, 3 4.5, 4 4.5, 3 5))')

) As foo(the_geom)
GROUP BY the_geom;

SELECT the_geom		-- <co id="co_code_countdistinct_notdistinct_4" /> --
 FROM (SELECT ST_GeomFromEWKT('LINESTRING (3 6, 3.4 4.5, 4 5)')
UNION ALL
SELECT ST_GeomFromText('POLYGON ((3 5, 3 4.5, 4 4.5, 3 5))')

) As foo(the_geom)
GROUP BY the_geom;
-- <end id="code_countdistinct_notdistinct" /> --
--[1] Gives 1 as answer
--[2] Gives 2 as answer
--[3] Returns 1 geometry
--[4] Returns 2 geometries



-- Listing 5.13 Guaranteeing unique geometries
CREATE TABLE mygeom_unique(the_geom geometry);
INSERT INTO mygeom_unique(the_geom)
SELECT CAST(the_geom As text)
FROM (SELECT ST_GeomFromEWKT('LINESTRING (3 5, 3.4 4.5, 4 5)')
UNION ALL
SELECT ST_GeomFromText('POLYGON ((3 5, 3 4.5, 4 4.5, 3 5))')
UNION ALL
SELECT ST_GeomFromText('POLYGON ((3 5, 3 4.5, 4 4.5, 3 5))')
) As foo(the_geom)
GROUP BY CAST(the_geom As text);


-- Listing ST_Equals testing – a  self-intersecting polygon is not equal to itself
-- <start id="code_st_equals_relate" /> --
SELECT ex_name, ST_Equals(the_geom, ST_Reverse(the_geom)) As g_eq_rev, 
	ST_Equals(the_geom, the_geom) As g_eq_g, 
	ST_AsText(ST_Reverse(the_geom)) As g_rev,
	ST_Relate(the_geom, ST_Reverse(the_geom)) As g_rel_rev, 
	ST_Equals(the_geom, ST_Multi(the_geom)) AS g_eq_m
FROM (
VALUES 
('A 2d line', ST_GeomFromText('LINESTRING(3 5, 2 4, 2 5)') ),
('A point',ST_GeomFromText( 'POINT(2 5)'))  ,
('A triangle', ST_GeomFromText('POLYGON((3 5, 2.5 4.5, 2 5, 3 5))') ),
('poly with self-inter'
 , ST_GeomFromText('POLYGON((2 0,0 0,1 1,1 -1, 2 0))')
	)
)
AS foo(ex_name, the_geom);
-- <end id="code_st_equals_relate" /> --


-- ST_Relate In action
-- <start id="code_st_relate1" /> --
WITH example_set(ex_name,the_geom) AS 	-- <co id="co_code_st_relate1_1" /> --
(
SELECT ex_name, the_geom
	FROM (
VALUES 
('A 2d line'
  , ST_GeomFromText('LINESTRING(3 5, 2.5 4.25, 1.6 5)') ),
('A point'
   ,ST_GeomFromText( 'POINT(1.6 5)'))  ,
('A triangle'
  , ST_GeomFromText('POLYGON((3 5, 2.5 4.25, 1.9 4.9, 3 5))') )
	)
	AS foo(ex_name, the_geom)
)

SELECT A.ex_name As a_name, B.ex_name As b_name, -- <co id="co_code_st_relate1_2" /> --
 ST_Relate(A.the_geom, B.the_geom) As DE9IM,
 ST_Intersects(A.the_geom, B.the_geom) As inter, 
 ST_Relate(A.the_geom, B.the_geom, 'FF*FF****') As relate_disjoint,
 NOT ST_Relate(A.the_geom
   , B.the_geom, 'FF*FF****') As relate_intersect
FROM example_set As A 
	CROSS JOIN example_set As B;	
-- <end id="code_st_relate1" /> --
--[1] CTE example_set
--[2] Relate sample set
