Spatial SQL -> MS-SQL 2012. Find out distribution of geo objects using geography histogram

Following procedure helps to understand distribution of geo points on the the map. Taking example of some Milan city interest points, we will use SQLserver 2012 build in stored procedure in order to calculate their distrubution on the map.

Our sample map data is shown here:



-- create temp table to store analitical results

CREATE TABLE [dbo].[tmp_distrib](
  [cellid] [int] NOT NULL,
  [count_no] [int] NULL,
  [cell] [geography] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

TRUNCATE TABLE tmp_distrib
go

/* parameters of procedure sp_help_spatial_geography_histogram:
table name, name of spatial column in this table, resolution of the grid */


INSERT INTO tmp_distrib (cellid, cell, count_no)
EXEC sp_help_spatial_geography_histogram
  @tabname=milano_pnt,
  @colname=g,
  @resolution=4000
GO


SELECT cellid, cell, count_no
FROM tmp_distrib
   UNION ALL
SELECT [id], [g].STBuffer(90) AS geog, '' AS count_no
FROM [dbo].[milano_pnt]
GO

DROP TABLE tmp_distrib


Output of distribution analysis:




sqlexamples.info