Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_help_spatial_geometry_index_helper

  No additional text.


Syntax
CREATE PROC sys.sp_help_spatial_geometry_index_helper
(
	@tabname		NVARCHAR(776),		-- the TABLE to check for indexes
	@indexname		SYSNAME,		-- the INDEX name
	@outputxml		TINYINT,		-- OUTPUT report in XML form rather than AS a rowset
	@xml_output		XML OUTPUT,		-- XML variable to OUTPUT
	@verboseoutput	TINYINT,		-- OUTPUT all properties
	@window			geometry		-- query window object
)
AS
BEGIN
	SET NOCOUNT ON
	SET ARITHIGNORE ON
	SET ARITHABORT OFF
	SET ANSI_WARNINGS OFF

	DECLARE @objid			INT,	-- the object id of the TABLE
			@indid			INT,	-- the INDEX id of an INDEX
			@groupid		INT,	-- the filegroup id of an INDEX
			@internaltab_id INT,	-- internal TABLE id
			@dbname			SYSNAME,-- the dbname
			@minx			INT,	-- minx
			@miny			INT,	-- miny
			@maxx			INT,	-- maxx
			@maxy			INT,	-- maxy
			@gridsize1		INT,	-- level1 cells
			@gridsize2		INT,	-- level2 cells
			@gridsize3		INT,	-- level3 cells
			@gridsize4		INT,	-- level4 cells
			@cells_per_object INT,	-- cells per object
			@results_card	BIGINT,	-- number of rows selected by the secondary filter
			@index_total_pages BIGINT,	-- total pages used by the INDEX
			@base_table_rows BIGINT,	-- total number of rows in the basetable
			@execstr		NVARCHAR(2000), -- for execution stuff
			@param_def NVARCHAR(500),  -- param def
			@colname	SYSNAME,	-- column name
			@internaltabname	SYSNAME, -- the internal TABLE used by the INDEX
			@quoted_table_name nvarchar(900);
			
	-- Check to see that the object names are local to the current database.
	SELECT @dbname = parsename(@tabname,3)
	IF @dbname is null
		SELECT @dbname = db_name()
	ELSE IF @dbname <> db_name()
		BEGIN
			raiserror(15250,-1,-1)
			return (1)
		END

	-- Check to see the the TABLE exists and initialize @objid.
	SELECT @objid = object_id(@tabname)
	IF @objid is NULL
	BEGIN
		raiserror(15009,-1,-1,@tabname,@dbname)
		return (1)
	END

	-- Locate the spatal INDEX
	SELECT @indid=index_id FROM sys.spatial_indexes WHERE name = @indexname and object_id = @objid
	IF @indid is NULL
	BEGIN
		raiserror(15009,-1,-1,@indexname,@dbname)
		return (1)
	END

	set @quoted_table_name = QUOTENAME(@dbname, N']')
		+ N'.'
		+ QUOTENAME(OBJECT_SCHEMA_NAME(@objid), N']')
		+ N'.'
		+ QUOTENAME(OBJECT_NAME(@objid), N']');

	-- figure out the grid sizes
	SELECT @minx = bounding_box_xmin,
			@miny = bounding_box_ymin,
			@maxx = bounding_box_xmax,
			@maxy = bounding_box_ymax,
			@cells_per_object = cells_per_object,
			@gridsize1 = level_1_grid,
			@gridsize2 = level_2_grid,
			@gridsize3 = level_3_grid,
			@gridsize4 = level_4_grid
	FROM sys.spatial_index_tessellations WHERE
		object_id = @objid and index_id = @indid;

	-- obtain the internal TABLE name
	SELECT @internaltabname = 'sys.' + name from sys.internal_tables where
		parent_object_id = @objid
		AND parent_minor_id = @indid

	-- also find the internal object id
	SELECT @internaltab_id=object_id FROM sys.internal_tables
		WHERE parent_id = @objid and parent_minor_id = @indid;

	SELECT @colname = c.name FROM sys.columns c,  sys.index_columns ic
	WHERE ic.index_id = @indid and ic.object_id = @objid and c.column_id = ic.column_id and
	ic.object_id = c.object_id
	
	-- compute total rows and pages used by the INDEX and the number of rows in the basetable
	SELECT  @index_total_pages = sum(used_page_count)
		FROM sys.dm_db_partition_stats
		WHERE object_id = @internaltab_id;

	SELECT  @base_table_rows = sum(row_count)
		FROM sys.dm_db_partition_stats
		WHERE object_id = @objid;

	SET @execstr = N'SELECT @results_card=count(*) FROM ' +
		@quoted_table_name +
		N' t WITH(INDEX='+
		QUOTENAME(@indexname, N']')+
		+ ') WHERE t.' + QUOTENAME(@colname, N']') + '.STIntersects(@window)=1';

	SET @param_def = '@window geometry, @results_card BIGINT OUTPUT';
	EXEC sp_executesql @execstr, @param_def, @window, @results_card OUTPUT;

	-- tesellate the query window object now.

	DECLARE @gridtranslater TABLE(gridsize INT, gridcode INT);

	INSERT INTO @gridtranslater values (16, 2)
	INSERT INTO @gridtranslater values (64, 3)
	INSERT INTO @gridtranslater values (256, 4)

	DECLARE @gridcode1		INT,	-- level1 grid code
		@gridcode2		INT,	-- level2 grid code
		@gridcode3		INT,	-- level3 grid code
		@gridcode4		INT;	-- level4 grid code

	SELECT @gridcode1 =  gridcode FROM @gridtranslater WHERE gridsize = @gridsize1
	SELECT @gridcode2 =  gridcode FROM @gridtranslater WHERE gridsize = @gridsize2
	SELECT @gridcode3 =  gridcode FROM @gridtranslater WHERE gridsize = @gridsize3
	SELECT @gridcode4 =  gridcode FROM @gridtranslater WHERE gridsize = @gridsize4

	CREATE TABLE #temp_window_object_tessellation(cell_id BINARY(5), cell_attributes INT, srid INT);

	INSERT INTO #temp_window_object_tessellation
	SELECT * FROM Sys.GetPlanarGeometryTessellation_VarBinary(@window,
		@minx, @miny, @maxx, @maxy,
		@gridcode1, @gridcode2, @gridcode3, @gridcode4,
		@cells_per_object,
		1,
		0.0);


	EXEC sys.sp_help_spatial_index_internal
		@minx, @miny, @maxx, @maxy,
		@gridsize1, @gridsize2, @gridsize3, @gridsize4,
		@cells_per_object,
		@base_table_rows,
		@index_total_pages,
		@results_card,
		@internaltabname,
		@outputxml,
		@xml_output OUTPUT,
		@verboseoutput;
END

 
Last revision 2008RTM
See also

  sp_help_spatial_geometry_index (Procedure)
sp_help_spatial_geometry_index_xml (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash