Spatial SQL -> MS-SQL. Build geography grid with given cell size

In this example we will create stored procedure that makes geography grid with given cell size. Grid creation starts from low left corner (from smallest Lat/Long values). This grid layer can be used afterwards in spatial analysis that aims to calculate distribution or density of geo objects on the map.

USE milano
GO

-- create grid (LL) with given cell size and coordinates of bounding box

CREATE PROCEDURE stp_make_geog_grid
  (@from_x FLOAT, @to_x FLOAT, @from_y FLOAT, @to_y FLOAT, @cell_size FLOAT)
AS
BEGIN


DECLARE @n INT;
DECLARE @Long1 FLOAT, @Long2 FLOAT, @Lat1 FLOAT, @Lat2 FLOAT;
DECLARE @Long3 FLOAT, @Long4 FLOAT, @Lat3 FLOAT, @Lat4 FLOAT;
DECLARE @g GEOGRAPHY;

SET @n = 1;
SET @Long1 = @from_x;
SET @Lat1 = @from_y;
SET @Long3 = @from_x + @cell_size;
SET @Lat3 = @from_y + @cell_size;

SET @Long2 = @Long3;
SET @Lat2 = @Lat1;

SET @Long4 = @Long1;
SET @Lat4 = @Lat3;

SET NOCOUNT ON;

-- create result table --------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects
  WHERE object_id = OBJECT_ID(N'[dbo].[geog_grid_test]') AND type in (N'U'))
DROP TABLE [dbo].[geog_grid_test];

CREATE TABLE [dbo].[geog_grid_test]
(
  id INT NOT NULL,
  in_flag TINYINT Default(0) NOT NULL,
  geog GEOGRAPHY NOT NULL
) ON [PRIMARY];
-- ----------------------------------------------------------------------------

WHILE (@Lat1 < @to_Y)
BEGIN
  INSERT INTO [dbo].[geog_grid_test] (id, geog)
  SELECT @n,
  GEOGRAPHY::STGeomFromText('POLYGON((' +
  CONVERT(Varchar(30), @Long1, 2) + ' ' + CONVERT(Varchar(30),@Lat1, 2) + ',' +
  CONVERT(Varchar(30), @Long2, 2) + ' ' + CONVERT(Varchar(30),@Lat2, 2) + ',' +
  CONVERT(Varchar(30), @Long3, 2) + ' ' + CONVERT(Varchar(30),@Lat3, 2) + ',' +
  CONVERT(Varchar(30), @Long4, 2) + ' ' + CONVERT(Varchar(30),@Lat4, 2) + ',' +
  CONVERT(Varchar(30), @Long1, 2) + ' ' + CONVERT(Varchar(30),@Lat1, 2) + ' ' +
  '))',4326);

  -- Set Next Values
  SET @n = @n + 1;

  -- move X
  SET @Long1 = @Long1 + @cell_size;
  SET @Long3 = @Long1 + @cell_size;

  -- do nothing with Y
  SET @Long2 = @Long3;
  SET @Lat2 = @Lat1;

  SET @Long4 = @Long1;
  SET @Lat4 = @Lat3;

  -- reset values when line is finished
  IF (@Long1 >= @to_X)
  BEGIN
  -- same X
  SET @Long1 = @from_x;
  SET @Long3 = @from_x + @cell_size;
  -- move Y Upward
  SET @Lat1 = @Lat1+ @cell_size;
  SET @Lat3 = @Lat1+ @cell_size;

  SET @Long2 = @Long3;
  SET @Lat2 = @Lat1;

  SET @Long4 = @Long1;
  SET @Lat4 = @Lat3;
  END
END

-- add Primary Key
ALTER TABLE dbo.geog_grid_test ADD PRIMARY KEY CLUSTERED ([id] ASC) ON [PRIMARY];

-- add spatial index
CREATE SPATIAL INDEX [geog_sidx] ON dbo.geog_grid_test
([geog]) USING GEOGRAPHY_GRID
WITH (
  GRIDS =(LEVEL_1 = MEDIUM, LEVEL_2 = MEDIUM, LEVEL_3 = HIGH, LEVEL_4 = HIGH),
  CELLS_PER_OBJECT = 32, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,
  ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];

END;


Following two screens showes Milano city center map with some geographic objects on it and than result of inersection of those objects with the grid. Intersecting cells are in red color:



-- [1] Build grid
EXEC dbo.stp_make_geog_grid
  @from_x =9.11, @to_x=9.24,
  @from_y=45.44, @to_y=45.51,
  @cell_size=0.001;

-- [2] Mark intersecting cells
UPDATE a
SET a.in_flag = 1
FROM [dbo].[geog_grid_test] AS a INNER JOIN [dbo].[milano_str] AS b
  ON a.geog.STIntersects(b.g)=1;

UPDATE a
SET a.in_flag = 1
FROM [dbo].[geog_grid_test] AS a INNER JOIN [dbo].[milano_reg] AS b
  ON a.geog.STIntersects(b.g)=1;

UPDATE a
SET a.in_flag = 1
FROM [dbo].[geog_grid_test] AS a INNER JOIN [dbo].[milano_pnt] AS b
  ON a.geog.STIntersects(b.g)=1;

-- [3] View Map
select '' AS name, geog
from [dbo].[geog_grid_test]
where in_flag = 1
   union all
select [name], [g]
from [dbo].[milano_str]
   union all
select [name], [g]
from [dbo].[milano_reg]
   union all
select [name], [g].STBuffer(25)
from [dbo].[milano_pnt];


sqlexamples.info