Spatial SQL -> MS-SQL 2008. Find out distribution of geo objects on the the map

Following procedure can help to understand density of geo objects on the the map. Taking example of Milan city center, we will use 30 Cell grid layer and then scan our Points of Interest in order to calculate their distrubution apon this grid.

Our map data is shown here on Grid layer background:



-- first parameter: table name, second: name of spatial column in this table

EXEC dbo.stp_geography_distribution milano_pnt, g;


-- structure of Geog_Grid table:
CREATE TABLE [dbo].[Geog_Grid](
  [Id] [int] NOT NULL,
  [geog] [geography] NOT NULL
) ON [PRIMARY]


Output of density calculation:



procedure script:
-- ====================================================================
-- count number of geographic objects intersecting with grid cells
-- using Geog_Grid table as a background Geographic Grid layer
-- ====================================================================


CREATE PROCEDURE stp_geography_distribution
     (@TableName sysname, @ColumnName sysname)
AS
BEGIN

DECLARE @Sql NVarchar(MAX) = '';

SET NOCOUNT ON;

--
-- Check if specified geography column exists in the table
--
IF NOT EXISTS(   SELECT 1
  FROM Information_Schema.Columns
  WHERE Table_Name = @TableName AND Column_Name = @ColumnName
  AND DATA_TYPE = 'geography'
)
BEGIN
  RAISERROR('Can not find geography column in input table', 16, 1)
  RETURN -1;
END

-- ------------------------------------------------------------------
-- this dynamic SQL statement will
-- count the number of geo objects intersecting with each grid cell
-- ------------------------------------------------------------------
SET @Sql =
  'SELECT gr.Id, gr.geog, cn.InCount
  FROM
   (SELECT b.Id, COUNT(a.' + QUOTENAME(@ColumnName) + ') AS InCount
   FROM ' + QUOTENAME(@TableName) + ' AS a JOIN dbo.Geog_Grid AS b
   ON a.' + QUOTENAME(@ColumnName) + '.STIntersects(b.geog) = 1
   GROUP BY b.Id) AS cn
  INNER JOIN dbo.Geog_Grid AS gr ON cn.Id = gr.Id;';

-- Execute the statement
EXEC sp_executesql @Sql;

END
GO




sqlexamples.info