-- Testing PSQL environment
-- $ psql -h <host> -d <db_name> -U <db_user> -W
-- > \i b:/.Bug_sde/Issue774.PG_Geog_features_not_visible/geog_intersect_&&.sql

\echo (1)

\echo (2)
CREATE TABLE geog_intersect (
  oid   serial NOT NULL,
  code  VARCHAR(3),
  geog  Geography (Geometry, 4326)
);

\echo (3)
--  1 | POINT (-110.45 57.54)
--  2 | POINT ( -98.57 54.63)
--  3 | POINT ( -89.84 52.69)
--  4 | POINT (-101.24 62.14)
--
INSERT INTO geog_intersect (geog)
     VALUES (ST_GeogFromText('SRID=4326; Point (-110.45 57.54)'));
INSERT INTO geog_intersect (geog)
     VALUES (ST_GeogFromText('SRID=4326; Point ( -98.57 54.63)'));
INSERT INTO geog_intersect (geog)
     VALUES (ST_GeogFromText('SRID=4326; Point ( -89.84 52.69)'));
INSERT INTO geog_intersect (geog)
     VALUES (ST_GeogFromText('SRID=4326; Point (-101.24 62.14)'));

-- -179.00 -89.00
--  179.00 -89.00
--  179.00  89.00
-- -179.00  89.00
--
-- INSERT INTO geog_intersect (geog)
--      VALUES (ST_GeogFromText('SRID=4326; Point (-179.00 -89.00)'));
-- INSERT INTO geog_intersect (geog)
--      VALUES (ST_GeogFromText('SRID=4326; Point ( 179.00 -89.00)'));
-- INSERT INTO geog_intersect (geog)
--      VALUES (ST_GeogFromText('SRID=4326; Point ( 179.00  89.00)'));
-- INSERT INTO geog_intersect (geog)
--      VALUES (ST_GeogFromText('SRID=4326; Point (-179.00  89.00)'));

\echo (Spatial Index)
create index spa_idx on geog_intersect USING GIST (geog);

\echo (B1 - count)
select count(*) from geog_intersect;

\echo (B2 - ST_AsText)
select oid, ST_AsText(geog) from geog_intersect;

\echo (B3 - ST_AsEWKT)
select oid, ST_AsEWKT(geog) from geog_intersect;


\echo --  
\echo -- Spatial query using search Envelope:
\echo --    minX = -179.99   maxX =  179.99 
\echo --    minY =  -89.99   maxY =   89.99 
\echo --

\echo (S1 - Search Rectangle - 5 points - Full Extent)
select oid, ST_AsText(geog) AS geog from geog_intersect
 where (geog && ST_GeogFromText('SRID=4326; POLYGON ((
-179.99 -89.99, 179.99 -89.99, 179.99 89.99, -179.99 89.99, -179.99 -89.99))') = 't');
\echo       Result: Missing row: 1, 2, 3, 4
\echo

\echo (S2 - Search Rectangle - 37 points - Densified)
select oid, ST_AsText(geog) AS geog from geog_intersect
 where (geog && ST_GeogFromText('SRID=4326; POLYGON ((
-179.99 -89.99, -159.99 -89.99, -139.99 -89.99, -119.99 -89.99, -99.99 -89.99,
 -80.00 -89.99, -60.00 -89.99, -40.00 -89.99, -20.00 -89.99, -0.00 -89.99, 20.00 -89.99,
  40.00 -89.99, 60.00 -89.99, 80.00 -89.99, 99.99 -89.99, 119.99 -89.99, 139.99 -89.99,
 179.99 -89.99,
 179.99  89.99, 159.99 89.99, 139.99 89.99, 119.99 89.99, 99.99 89.99, 80.00 89.99,
  60.00  89.99, 40.00 89.99, 20.00 89.99, 0.00 89.99, -20.00 89.99, -40.00 89.99,
 -60.00  89.99, -80.00 89.99, -99.99 89.99, -119.99 89.99, -139.99 89.99,
-179.99  89.99,
-179.99 -89.99))') = 't');
\echo       Result: Missing row: 3
\echo

\echo (S3 - Search Rectangle - 39 points - Densified)
select oid, ST_AsText(geog) AS geog from geog_intersect
 where (geog && ST_GeogFromText('SRID=4326; POLYGON ((
-179.99 -89.99, -159.99 -89.99, -139.99 -89.99, -119.99 -89.99, -99.99 -89.99,
 -80.00 -89.99, -60.00 -89.99, -40.00 -89.99, -20.00 -89.99, -0.00 -89.99, 20.00 -89.99,
  40.00 -89.99, 60.00 -89.99, 80.00 -89.99, 99.99 -89.99, 119.99 -89.99, 139.99 -89.99,
 179.99 -89.99,
 179.99   0.00,
 179.99  89.99, 159.99 89.99, 139.99 89.99, 119.99 89.99, 99.99 89.99, 80.00 89.99,
  60.00  89.99, 40.00 89.99, 20.00 89.99, 0.00 89.99, -20.00 89.99, -40.00 89.99,
 -60.00  89.99, -80.00 89.99, -99.99 89.99, -119.99 89.99, -139.99 89.99,
-179.99  89.99,
-179.99   0.00,
-179.99 -89.99))') = 't');
\echo       Result: Missing row: 3
\echo

\echo (S4 - Rectangle - 43 points - Densified)
select oid, ST_AsText(geog) AS geog from geog_intersect
 where (geog && ST_GeogFromText('SRID=4326; POLYGON ((
-179.99 -89.99, -159.99 -89.99, -139.99 -89.99, -119.99 -89.99, -99.99 -89.99,
 -80.00 -89.99, -60.00 -89.99, -40.00 -89.99, -20.00 -89.99, -0.00 -89.99, 20.00 -89.99,
  40.00 -89.99, 60.00 -89.99, 80.00 -89.99, 99.99 -89.99, 119.99 -89.99, 139.99 -89.99,
 179.99 -89.99,
 179.99 -45.00,
 179.99   0.00,
 179.99  45.00,
 179.99  89.99, 159.99 89.99, 139.99 89.99, 119.99 89.99, 99.99 89.99, 80.00 89.99,
  60.00  89.99, 40.00 89.99, 20.00 89.99, 0.00 89.99, -20.00 89.99, -40.00 89.99,
 -60.00  89.99, -80.00 89.99, -99.99 89.99, -119.99 89.99, -139.99 89.99,
-179.99  89.99,
-179.99  45.00,
-179.99   0.00,
-179.99 -45.00,
-179.99 -89.99))') = 't');
\echo       Result: Missing row: 3
\echo

\echo (S5 - Rectangle - 43 points - Densified - using ST_Intersects)
select oid, ST_AsText(geog) as geog from geog_intersect
where ST_Intersects(geog, ST_GeogFromText('SRID=4326; POLYGON ((
-179.99 -89.99, -159.99 -89.99, -139.99 -89.99, -119.99 -89.99, -99.99 -89.99,
 -80.00 -89.99, -60.00 -89.99, -40.00 -89.99, -20.00 -89.99, -0.00 -89.99, 20.00 -89.99,
  40.00 -89.99, 60.00 -89.99, 80.00 -89.99, 99.99 -89.99, 119.99 -89.99, 139.99 -89.99,
 179.99 -89.99,
 179.99  89.99, 159.99 89.99, 139.99 89.99, 119.99 89.99, 99.99 89.99, 80.00 89.99,
  60.00  89.99, 40.00 89.99, 20.00 89.99, 0.00 89.99, -20.00 89.99, -40.00 89.99,
 -60.00  89.99, -80.00 89.99, -99.99 89.99, -119.99 89.99, -139.99 89.99,
-179.99  89.99,
-179.99 -89.99))')) = 't';
\echo       Result: Missing row: 1,2,3,4
\echo

\echo (S6 - Rectangle - 43 points - Densified - using ST_Intersects)
select oid, ST_AsText(geog) as geog from geog_intersect
where ST_Intersects(geog, ST_GeogFromText('SRID=4326; POLYGON ((
-179.99 -89.99, 179.99 -89.99, 179.99 89.99, -179.99 89.99, -179.99 -89.99))')) = 't';
\echo       Result: Missing row: 1,2,3,4
\echo

\echo --  
\echo -- Spatial query using search Envelope:
\echo --    minX = -120.64   maxX =  -79.58
\echo --    minY =   41.34   maxY =   73.01
\echo --

\echo (S7 - Search Rectangle - 5 points)
select oid, ST_AsText(geog) AS geog from geog_intersect
 where (geog && ST_GeogFromText('SRID=4326; POLYGON ((
-120.64 41.34, -79.58 41.34, -79.58 73.01, -120.64 73.01, -120.64 41.34))') = 't');
\echo       Result: Successful
\echo
