-- blocks table
CREATE TABLE testing.blocks(
	id serial
);
SELECT AddGeometryColumn ('testing','blocks','geom',-1,'POLYGON',2);
INSERT INTO testing.blocks(geom) SELECT ST_GeomFromText('POLYGON((518340.401248796 3820650.80532367,518350.693588019 3820628.81714442,518355.570889645 3820612.85506637,518360.747754687 3820584.83016526,518360.747754687 3820554.66766526,518359.689421353 3820481.64266527,518456.526921353 3820479.5259986,518462.281608853 3820479.5259986,518467.639421354 3820508.1009986,518471.343588019 3820510.21766527,518475.576921353 3820510.74683193,518558.126921353 3820505.45516527,518565.799838019 3820504.66141527,518573.208171353 3820501.22183193,518612.366504687 3820473.44058193,518635.069825224 3820458.01114079,518631.945671354 3820472.3822486,518640.412338021 3820463.91549952,518649.408171353 3820460.21133285,518657.874838022 3820458.88841618,518665.018588021 3820459.41758285,518677.189421354 3820464.44466618,518684.333171353 3820469.20716618,518689.360254687 3820477.40924952,518695.18108802 3820492.22591618,518697.297754686 3820503.86758285,518697.826921354 3820516.56758285,518697.297754686 3820530.85508285,518695.181088022 3820543.55508285,518689.889421354 3820555.19674951,518679.306088021 3820569.48424951,518675.072754687 3820573.18841618,518667.664421353 3820576.36341618,518659.726921353 3820577.42174951,518650.466504688 3820576.62799952,518642.264421354 3820574.24674951,518635.224656908 3820568.79492094,518630.180412604 3820560.17775268,518635.224656907 3820568.79500336,518634.804303216 3820582.24632151,518661.286585813 3820582.6666752,518666.60608802 3820582.7134986,518670.310254687 3820582.18433193,518674.014421353 3820580.86141527,518681.390925112 3820575.73884321,518682.48108802 3820579.00933193,518692.937153429 3820612.85506637,518701.13421302 3820627.75881109,518708.542546354 3820637.28381108,518720.713379687 3820646.80881109,518754.580046353 3820676.97131109,518756.167546353 3820678.02964442,518757.755046354 3820681.20464442,518757.755046353 3820692.84631108,518757.225879687 3820701.84214442,518756.69671302 3820708.19214442,518755.638379687 3820764.81297775,518755.638379686 3820810.85047775,518755.109213019 3820886.52131109,518755.109213019 3820926.73797775,518751.669629686 3820932.82339442,518747.700879686 3820938.64422775,518737.64671302 3820949.49214442,518735.926921353 3820951.34422775,518730.635254686 3820951.07964442,518727.195671354 3820949.75672775,518716.083171353 3820933.08797775,518696.239421353 3820920.12339442,518664.22483802 3820898.95672775,518662.108171353 3820896.31089442,518662.372754687 3820859.26922775,518627.976921353 3820858.74006108,518627.976921353 3820861.65047775,518655.49358802 3820862.17964442,518655.229004686 3820897.63381108,518656.551921353 3820899.75047775,518667.929004686 3820906.36506109,518705.235254687 3820929.91297775,518711.84983802 3820935.20464442,518716.083171354 3820943.93589442,518717.670671354 3820949.49214442,518719.125879686 3820963.25047775,518706.425879687 3820980.71297775,518687.375879687 3821008.22964442,518664.357129687 3821035.48172775,518635.25296302 3821068.55464442,518627.050879686 3821073.31714442,518621.097754686 3821074.77235275,518617.261296353 3821074.50776942,518596.35921302 3821057.17756109,518565.667546353 3821011.66922775,518537.621713019 3820952.93172775,518531.412937159 3820938.91191129,518569.768588019 3820915.09631108,518585.114421353 3820905.04214442,518589.876921353 3820901.07339442,518590.670671353 3820897.10464442,518589.876921353 3820865.35464442,518588.554004687 3820829.10672775,518587.495671353 3820807.94006108,518584.320671353 3820798.67964442,518581.145671353 3820793.65256109,518573.472754687 3820787.30256109,518569.239421353 3820781.21714442,518566.858171353 3820771.42756109,518564.476921353 3820749.73172775,518562.889421353 3820719.04006108,518561.301921353 3820665.06506108,518556.274838019 3820664.80047775,518560.24358802 3820760.84422775,518564.476921353 3820785.18589442,518569.76858802 3820792.59422775,518578.235254686 3820800.00256108,518581.410254687 3820807.41089442,518583.526921353 3820835.98589442,518585.114421353 3820895.78172775,518583.526921353 3820900.01506108,518573.472754686 3820907.42339442,518529.424088956 3820934.42096374,518504.813379686 3820878.84839442,518469.888379686 3820803.70672775,518432.846713019 3820746.02756109,518413.267546353 3820722.21506109,518382.046713019 3820685.17339442,518348.180046353 3820656.59839442,518340.401248796 3820650.80532367))');

-- buildings table
CREATE TABLE testing.buildings(
	id serial
)
SELECT AddGeometryColumn ('testing','buildings','geom',-1,'POLYGON',2);
INSERT INTO testing.buildings(geom) SELECT ST_GeomFromText('POLYGON((518603.689825253 3820863.05709336,518625.678177319 3820862.68015018,518625.678177319 3820857.90553659,518631.834915897 3820857.90553659,518631.960563623 3820852.12574119,518635.60434768 3820852.12574119,518635.356376888 3820858.85350875,518627.976921353 3820858.73997866,518627.976921353 3820858.74006108,518627.976921353 3820861.65039533,518627.976921353 3820861.65047775,518639.772795288 3820861.87732148,518639.76077524 3820863.05860999,518654.074563415 3820863.18274109,518654.072461231 3820882.50510172,518603.689825253 3820882.53249091,518603.689825253 3820863.05709336))');

------------------------------------------------------------------------------------------------------
---------------------------------------- for testing -------------------------------------------------

-- ST_Covers(geometry geomA, geometry geomB); Returns 1 (TRUE) if no point in Geometry B is outside Geometry A
SELECT ST_Covers(a.geom, b.geom) FROM testing.blocks a, testing.buildings b; -- false

-- ST_Contains(geometry geomA, geometry geomB); Returns true if and only if no points of B lie in the exterior of A, and at least one point of the interior of B lies in the interior of A.
SELECT ST_Contains(a.geom, b.geom) FROM testing.blocks a, testing.buildings b; -- false

-- ST_CoveredBy(geometry geomA, geometry geomB); Returns 1 (TRUE) if no point in Geometry/Geography A is outside Geometry/Geography B
SELECT ST_Coveredby(b.geom, a.geom) FROM testing.blocks a, testing.buildings b; -- false

-- ST_Within(geometry A, geometry B); Returns true if the geometry A is completely inside geometry B
SELECT ST_Within(b.geom, a.geom) FROM testing.blocks a, testing.buildings b; -- false


-- ST_Intersects(  geometry geomA  , geometry geomB  ); Returns TRUE if the Geometries/Geography "spatially intersect"
SELECT ST_Intersects(a.geom, b.geom) FROM testing.blocks a, testing.buildings b; -- true -> because they are intersect

SELECT ST_Covers(ST_Intersection(a.geom, b.geom), b.geom) FROM testing.blocks a, testing.buildings b; -- false

-- "=" operator

	-- "=" This operator is a little more naive, it only tests whether the bounding boxes of two geometries are the same.
	SELECT ST_Intersection(a.geom, b.geom) = b.geom FROM testing.blocks a, testing.buildings b; -- true

	-- convert to geometry and then compare geometry that we can see the different
	SELECT ST_Intersection(b.geom, a.geom) FROM testing.blocks a, testing.buildings b;
	-- => geometry: '0103000000010000001100000038125EE88FA71F414C52BA5E9D264D4138125EE88FA71F41E89EB75E9D264D411C10EE6CADA71F4154C63F6D9D264D41441EDA6AAEA71F418D4918109A264D418AFD9DD79FA71F418D4918109A264D416231F4569FA71F417C9FE8F39C264D41781D74B686A71F417C9FE8F39C264D41781D74B686A71F413E290F579F264D41168D61C22EA71F41D1D54E879F264D41168D61C22EA71F4182A92844A9264D41E546334AF8A71F41542CA740A9264D41145A5A4CF8A71F415F0F64979F264D411EAA080BBFA71F413B8880879F264D41E2A55717BFA71F41FE114CF09E264D4138125EE88FA71F41DCDA42D39E264D4138125EE88FA71F41782740D39E264D4138125EE88FA71F414C52BA5E9D264D41'
	SELECT ST_GeomFromText('POLYGON((518603.689825253 3820863.05709336,518625.678177319 3820862.68015018,518625.678177319 3820857.90553659,518631.834915897 3820857.90553659,518631.960563623 3820852.12574119,518635.60434768 3820852.12574119,518635.356376888 3820858.85350875,518627.976921353 3820858.73997866,518627.976921353 3820858.74006108,518627.976921353 3820861.65039533,518627.976921353 3820861.65047775,518639.772795288 3820861.87732148,518639.76077524 3820863.05860999,518654.074563415 3820863.18274109,518654.072461231 3820882.50510172,518603.689825253 3820882.53249091,518603.689825253 3820863.05709336))');
	-- => geometry: '01030000000100000011000000168D61C22EA71F41D1D54E879F264D41781D74B686A71F413E290F579F264D41781D74B686A71F417C9FE8F39C264D416231F4569FA71F417C9FE8F39C264D418AFD9DD79FA71F418D4918109A264D41441EDA6AAEA71F418D4918109A264D411C10EE6CADA71F4154C63F6D9D264D4138125EE88FA71F41E89EB75E9D264D4138125EE88FA71F414C52BA5E9D264D4138125EE88FA71F41782740D39E264D4138125EE88FA71F41DCDA42D39E264D41E2A55717BFA71F41FC114CF09E264D411EAA080BBFA71F413B8880879F264D41145A5A4CF8A71F415F0F64979F264D41E546334AF8A71F41542CA740A9264D41168D61C22EA71F4182A92844A9264D41168D61C22EA71F41D1D54E879F264D41'

	-- compare two geometries above
	SELECT '0103000000010000001100000038125EE88FA71F41782740D39E264D4138125EE88FA71F414C52BA5E9D264D4138125EE88FA71F41E89EB75E9D264D411C10EE6CADA71F4154C63F6D9D264D41441EDA6AAEA71F418D4918109A264D418AFD9DD79FA71F418D4918109A264D416231F4569FA71F417C9FE8F39C264D41781D74B686A71F417C9FE8F39C264D41781D74B686A71F413E290F579F264D41168D61C22EA71F41D1D54E879F264D41168D61C22EA71F4182A92844A9264D41E546334AF8A71F41542CA740A9264D41145A5A4CF8A71F415F0F64979F264D411EAA080BBFA71F413B8880879F264D41E2A55717BFA71F41FE114CF09E264D4138125EE88FA71F41DCDA42D39E264D4138125EE88FA71F41782740D39E264D41'
	      ='01030000000100000011000000168D61C22EA71F41D1D54E879F264D41781D74B686A71F413E290F579F264D41781D74B686A71F417C9FE8F39C264D416231F4569FA71F417C9FE8F39C264D418AFD9DD79FA71F418D4918109A264D41441EDA6AAEA71F418D4918109A264D411C10EE6CADA71F4154C63F6D9D264D4138125EE88FA71F41E89EB75E9D264D4138125EE88FA71F414C52BA5E9D264D4138125EE88FA71F41782740D39E264D4138125EE88FA71F41DCDA42D39E264D41E2A55717BFA71F41FC114CF09E264D411EAA080BBFA71F413B8880879F264D41145A5A4CF8A71F415F0F64979F264D41E546334AF8A71F41542CA740A9264D41168D61C22EA71F4182A92844A9264D41168D61C22EA71F41D1D54E879F264D41';

-- end "=" operator



-- ST_Difference(geometry geomA, geometry geomB): Returns a geometry that represents that part of geometry A that does not intersect with geometry B
SELECT ST_Difference(a.geom, b.geom) FROM testing.blocks a, testing.buildings b; -- have data => obviously, a part of a.geom does not intesect with b.geom
SELECT ST_Difference(b.geom, a.geom) FROM testing.blocks a, testing.buildings b; -- have data => obviously, a part of b.geom also does not intesect with a.geom

SELECT ST_IsEmpty(ST_Difference(b.geom, a.geom)) FROM testing.blocks a, testing.buildings b; -- false

-- A small test with one polygon is within in the other.
-- 'POLYGON((0 0, 0 3, 3 3, 3 0, 0 0))': big polygon
-- 'POLYGON((1 1, 1 2, 2 2, 2 1, 1 1))': small polygon

SELECT ST_Difference(ST_GeomFromText('POLYGON((0 0, 0 3, 3 3, 3 0, 0 0))'), ST_GeomFromText('POLYGON((1 1, 1 2, 2 2, 2 1, 1 1))')); -- have data
SELECT ST_Difference(ST_GeomFromText('POLYGON((1 1, 1 2, 2 2, 2 1, 1 1))'), ST_GeomFromText('POLYGON((0 0, 0 3, 3 3, 3 0, 0 0))')); -- have data but it is "GEOMETRYCOLLECTION EMPTY" (used: SELECT ST_AsEWKT('0107000020E610000000000000');)
-- check ST_IsEmpty function for one geometry is within the other
SELECT ST_IsEmpty(ST_Difference(ST_GeomFromText('POLYGON((1 1, 1 2, 2 2, 2 1, 1 1))'), ST_GeomFromText('POLYGON((0 0, 0 3, 3 3, 3 0, 0 0))'))); --true


SELECT version();

