-- sla 80000
-- sla_for_parallel_query_enforced 80000
-- single_db_mode_expected
EXEC sp_babelfish_configure 'babelfishpg_tsql.escape_hatch_spatial_index', 'ignore';

-- Geometry table with 1000 deterministic points

CREATE TABLE si_geom_tbl (id INT IDENTITY, geom sys.GEOMETRY);

DECLARE @i INT = 0; WHILE @i < 1000 BEGIN INSERT INTO si_geom_tbl (geom) VALUES (geometry::STGeomFromText('POINT(' + CAST((@i*7+13)%1000 AS VARCHAR) + ' ' + CAST((@i*11+17)%1000 AS VARCHAR) + ')', 4326)); SET @i = @i + 1; END;

CREATE SPATIAL INDEX si_geom ON si_geom_tbl(geom) USING GEOMETRY_GRID WITH (BOUNDING_BOX = (0, 0, 1000, 1000));

-- Geography table with 1000 points
CREATE TABLE si_geog_tbl (id INT IDENTITY, geog sys.GEOGRAPHY);

DECLARE @i INT = 0; WHILE @i < 1000 BEGIN INSERT INTO si_geog_tbl (geog) VALUES (geography::STGeomFromText('POINT(' + CAST(((@i*7+13)%360)-180 AS VARCHAR) + ' ' + CAST(((@i*11+17)%180)-90 AS VARCHAR) + ')', 4326)); SET @i = @i + 1; END;

CREATE SPATIAL INDEX si_geog ON si_geog_tbl(geog) USING GEOGRAPHY_GRID WITH (GRIDS = (HIGH, HIGH, HIGH, HIGH));

-- Multi-column table
CREATE TABLE si_multi_tbl (id INT IDENTITY, g1 sys.GEOMETRY, g2 sys.GEOMETRY);
DECLARE @i INT = 0; WHILE @i < 500 BEGIN INSERT INTO si_multi_tbl (g1, g2) VALUES (geometry::STGeomFromText('POINT(' + CAST((@i*7+13)%500 AS VARCHAR) + ' ' + CAST((@i*11+17)%500 AS VARCHAR) + ')', 4326), geometry::STGeomFromText('POINT(' + CAST((@i*3+5)%500+500 AS VARCHAR) + ' ' + CAST((@i*5+7)%500+500 AS VARCHAR) + ')', 4326)); SET @i = @i + 1; END;

CREATE SPATIAL INDEX si_g1 ON si_multi_tbl(g1) USING GEOMETRY_GRID WITH (BOUNDING_BOX = (0,0,500,500));

CREATE SPATIAL INDEX si_g2 ON si_multi_tbl(g2) USING GEOMETRY_GRID WITH (BOUNDING_BOX = (500,500,1000,1000));

-- View test table
CREATE TABLE si_view_tbl (id INT IDENTITY PRIMARY KEY, geom sys.GEOMETRY, geog sys.GEOGRAPHY);

CREATE SPATIAL INDEX si_v_geom ON si_view_tbl(geom) USING GEOMETRY_GRID WITH (BOUNDING_BOX = (0,0,1000,1000));

CREATE SPATIAL INDEX si_v_geog ON si_view_tbl(geog) USING GEOGRAPHY_GRID WITH (GRIDS = (HIGH, HIGH, HIGH, HIGH));

-- DDL error test table
CREATE TABLE si_ddl_tbl (id INT IDENTITY PRIMARY KEY, name VARCHAR(100), geom sys.GEOMETRY);

-- Multi-DB isolation setup for sys.spatial_indexes
CREATE DATABASE si_multidb_a_;

CREATE DATABASE si_multidb_b_;

USE si_multidb_a_;

CREATE TABLE t_multi (id int, geom geometry);

CREATE SPATIAL INDEX sidx_multi_a_ ON t_multi(geom);

USE si_multidb_b_;

CREATE TABLE t_multi (id int, geom geometry);

CREATE SPATIAL INDEX sidx_multi_b_ ON t_multi(geom);

USE master;

