/**
 * Creating High Performance Spatial Databases with SQL Server 2008
 * Alastair Aitchison
 *
 * Presented at SQLBits Conference: 21/11/2009
 */

-- Create the "2-column" spatial model
CREATE TABLE Customers (
  Name varchar(32),
  Address varchar(255),
  Lat float,
  Long float
);
GO


-- Insert some sample data
INSERT INTO Customers VALUES
('Bob', 'New York', 40.75, -74),
('Alan', 'London', 51.5, -0.5);
GO


-- Find out which customers lie in a polygon
SELECT * FROM Customers
WHERE Lat BETWEEN 50 AND 60
AND Long BETWEEN -10 AND 10;


-- Find out the distance between two points
-- Result: 5,549,239 metres
DECLARE 
  @Lat1 float = RADIANS(40.75),
  @Lat2 float = RADIANS(51.5),
  @Lon1 float = RADIANS(-74.0),
  @Lon2 float = RADIANS(-0.5);
  
SELECT 6378137.0 * ACOS(
 SIN(@Lat1) * SIN(@Lat2) +
 COS(@Lat1) * COS(@Lat2) * COS(@Lon2 - @Lon1)
)


-- SQL Server 2008 - work out distance between two points
-- Result: 5,557,975 metres
DECLARE @London geography = 'POINT(-0.5 51.5)';
DECLARE @NewYork geography = 'POINT(-74 40.75)';
SELECT @London.STDistance(@NewYork);



-- Create a table to demonstrate spatial index
-- Note the clustered primary key
CREATE TABLE Grid (
  id char(1),
  shape geometry,
  CONSTRAINT [idxGridCluster] 
    PRIMARY KEY CLUSTERED ( id ASC )
);


-- Insert some points into the table
INSERT INTO Grid VALUES 
('A', geometry::Point(0.5, 2.5, 0)),
('B', geometry::Point(2.5, 1.5, 0)),
('C', geometry::Point(3.25, 0.75, 0)),
('D', geometry::Point(3.75, 2.75, 0));


-- Create a basic spatial index
CREATE SPATIAL INDEX idxGrid ON Grid(shape)
USING GEOMETRY_GRID WITH (
  BOUNDING_BOX = (0, 0, 4096, 4096),
  GRIDS = (	
    LEVEL_1 = MEDIUM,
    LEVEL_2 = MEDIUM, 
    LEVEL_3 = MEDIUM, 
    LEVEL_4 = MEDIUM),
  CELLS_PER_OBJECT = 16);


-- Find points that intersect the red polygon
-- Note the spatial index hint
DECLARE @Polygon geometry = 
'POLYGON ((1.5 0.5, 3.5 0.5, 3.5 2.5, 1.5 2.5, 1.5 0.5)) ';
SELECT *
FROM Grid WITH(INDEX(idxGrid))
WHERE shape.STIntersects(@Polygon) = 1;


-- Obtain descriptive statistics about the index
-- Note primary filter of 50% and internal filter efficiency 66%
EXEC sp_help_spatial_geometry_index
  @tabname = Grid,
  @indexname = idxGrid,
  @verboseoutput = 1,
  @query_sample = 'POLYGON ((1.5 0.5, 3.5 0.5, 3.5 2.5, 1.5 2.5, 1.5 0.5))';


-- Create another spatial index, which has HIGH resolution at grid level 4
-- Each level 4 grid cell is now half width and half height as previous
CREATE SPATIAL INDEX idxGrid2 ON Grid(shape)
USING GEOMETRY_GRID WITH (
  BOUNDING_BOX = (0, 0, 4096, 4096),
  GRIDS = (	
    LEVEL_1 = MEDIUM,
    LEVEL_2 = MEDIUM, 
    LEVEL_3 = MEDIUM, 
    LEVEL_4 = HIGH),
  CELLS_PER_OBJECT = 16);
  
-- Examine the efficiency of the new index
-- Even though table contains only points, setting HIGH resolution has
-- reduced internal filtering efficiency to zero
  EXEC sp_help_spatial_geometry_index
  @tabname = Grid,
  @indexname = idxGrid2,
  @verboseoutput = 1,
  @query_sample = 'POLYGON ((1.5 0.5, 3.5 0.5, 3.5 2.5, 1.5 2.5, 1.5 0.5))';