-- Example 30.
1
USE sample;
CREATE TABLE beverage_markets
(id INTEGER IDENTITY(1,1),
name VARCHAR(25),
shape GEOMETRY);
INSERT INTO beverage_markets
VALUES ('Coke', GEOMETRY::STGeomFromText
('POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))', 0));
INSERT INTO beverage_markets
VALUES ('Pepsi', GEOMETRY::STGeomFromText
('POLYGON ((300 0, 150 0, 150 150, 300 150, 300 0))', 0));
INSERT INTO beverage_markets
VALUES ('7UP', GEOMETRY::STGeomFromText
('POLYGON ((300 0, 150 0, 150 150, 300 150, 300 0))', 0));
INSERT INTO beverage_markets
VALUES ('Almdudler', GEOMETRY::STGeomFromText('POINT (50 0)', 0));
-- Example 30.2
USE sample;
DECLARE @g geometry;
DECLARE @h geometry;
SELECT @h = shape FROM beverage_markets WHERE name ='Almdudler';
SELECT @g = shape FROM beverage_markets WHERE name = 'Coke';
SELECT @g.STContains(@h);
-- Example 30.3
USE sample;
SELECT id, shape.ToString() AS wkt, shape.STLength() AS length
FROM beverage_markets
WHERE name = 'Almdudler' ;
-- Example 30.4
USE sample;
DECLARE @g geometry;
DECLARE @h geometry;
SELECT @h = shape FROM beverage_markets WHERE name = 'Coke';
SELECT @g = shape FROM beverage_markets WHERE name = 'Pepsi';
SELECT @g.STIntersects(@h);
-- Example 30.5
USE sample;
DECLARE @poly1 GEOMETRY = 'POLYGON ((1 1, 1 4, 4 4, 4 1, 1 1))';
DECLARE @poly2 GEOMETRY = 'POLYGON ((2 2, 2 6, 6 6, 6 2, 2 2))';
DECLARE @result GEOMETRY;
SELECT @result = @poly1.STIntersection(@poly2);
SELECT @result.STAsText();
-- Example 30.6
USE AdventureWorks;
SELECT SpatialLocation, City
FROM Person.Address
WHERE City = 'Dallas';
-- Example 30.7
USE sample;
GO
ALTER TABLE beverage_markets
ADD CONSTRAINT prim_key PRIMARY KEY(id);
GO
CREATE SPATIAL INDEX i_spatial_shape
ON beverage_markets(shape)
USING GEOMETRY_GRID
WITH (BOUNDING_BOX = ( xmin=0, ymin=0, xmax=500, ymax=200 ),
GRIDS = (LOW, LOW, MEDIUM, HIGH), PAD_INDEX = ON );
-- Example 30.8
CREATE SPATIAL INDEX auto_grid_index
ON beverage_markets(shape)
USING GEOMETRY_AUTO_GRID
WITH (BOUNDING_BOX = (xmin=0, ymin=0, xmax=500, ymax=200 ),
CELLS_PER_OBJECT = 32, DATA_COMPRESSION = page);
-- Example 30.9
USE sample;
SELECT object_id, name, type_desc
FROM sys.spatial_indexes;
-- Example 30.10
USE sample;
DECLARE @rectangle1 GEOMETRY = 'POLYGON((1 1, 1 4, 4 4, 4 1, 1 1))';
DECLARE @line GEOMETRY = 'LINESTRING (0 2, 4 4)';
SELECT @rectangle1
UNION ALL
SELECT @line
-- Example 30.11
DECLARE @g1 geometry =
'MultiPolygon(((2 0, 3 1, 2 2, 1.5 1.5, 2 1, 1.5 0.5, 2 0)),
((1 0, 1.5 0.5, 1 1, 1.5 1.5, 1 2, 0 1, 1 0)))';
select @g1
-- Example 30.12
DECLARE @query geometry
='POLYGON((-90.0 -180.0, -90.0 180.0, 90.0 180.0, 90.0 -180.0, -90.0 -180.0))';
EXEC sp_help_spatial_geometry_index
'beverage_markets', 'auto_grid_index', 0, @query;
-- Example 30.13
USE sample;
CREATE TABLE dept_temp
(dept_no CHAR(4) NOT NULL PRIMARY KEY CLUSTERED,
dept_name CHAR(25) NOT NULL,
location CHAR(30) NULL,
start_date DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN,
end_date DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN,
PERIOD FOR SYSTEM_TIME (start_date, end_date))
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Dept_History));
-- Example 30.14
INSERT INTO dept_temp (dept_no, dept_name, location)
VALUES ('d1', 'Research', 'Dallas' );
INSERT INTO dept_temp (dept_no, dept_name, location)
VALUES ('d2', 'Accounting', 'Seattle');
INSERT INTO dept_temp (dept_no, dept_name, location)
VALUES ('d3', 'Marketing', 'Dallas');
-- Example 30.15
UPDATE dept_temp
SET location = 'Houston'
WHERE dept_no = 'd1';
-- Example 30.16
USE sample;
DELETE FROM dept_temp
WHERE dept_no = 'd2';
-- Example 30.17
USE sample;
SELECT * FROM dept_temp
FOR SYSTEM_TIME AS OF '2015-06-20 06:41:07.2902041';
-- Example 30.18
USE sample;
SELECT * FROM dept_temp FOR SYSTEM_TIME
FROM '2015-06-19 06:41:07.2902041' TO '2015-06-20 06:41:07.2902041' ;
-- Example 30.19
USE sample;
SELECT name, type_desc FROM sys.tables
WHERE object_id IN (SELECT object_id FROM sys.periods);
--Example 30.20
SELECT temporal_type
FROM sys.tables
WHERE object_id = OBJECT_ID('dbo.dept_temp', 'U');
-- Example 30.21
USE sample;
ALTER TABLE department ADD PRIMARY KEY (dept_no);
GO
ALTER TABLE department ADD
SysStartTime datetime2 NOT NULL DEFAULT GETUTCDATE(),
SysEndTime datetime2 NOT NULL DEFAULT CONVERT(DATETIME2, '9999-12-31
23:59:59.99999999')
GO
ALTER TABLE department
ADD PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);
GO
ALTER TABLE department
alter column SysStartTime ADD HIDDEN;
GO
ALTER TABLE department
alter column SysEndTime ADD HIDDEN;
ALTER TABLE department
SET (SYSTEM_VERSIONING = ON
(HISTORY_TABLE = dbo.department_history, DATA_CONSISTENCY_CHECK = ON));
-- Example 30.22
USE sample;
ALTER TABLE department set (SYSTEM_VERSIONING = OFF);
GO
DROP TABLE dbo.department;
GO
DROP TABLE dbo.department_history;
