1

I'm looking into optimizing the spatial indexes on some of our tables (stored in SQL Server 2008 R2), so I ran

exec sp_help_spatial_geometry_index 
    @tabname='myTable',
    @indexname='IX_myTable_Shape',
    @verboseoutput=1,
    @query_sample=@geomString

and that returned (among other stuff) Number_Of_SRIDs_Found: 2. Troubled by that, I immediately did a select count(*) from myTable where Shape.STSrid <> @mySrid and saw 0. Everything in the table is apparently using the correct SRID, so where could sp_help_spatial_geometry_index be finding another SRID?

I ran other spatial indexes through the same sproc and found 2 SRIDs for them as well. Getting paranoid, I threw this together to look through all spatial columns in the entire database...

declare @tablename varchar(50)
declare @fieldname varchar(10)

DECLARE cSpatialColumns CURSOR STATIC LOCAL FOR
    SELECT  table_name [Table Name], column_name  [Column Name]
    FROM information_schema.columns c 
    where data_type = 'geometry' and TABLE_NAME NOT LIKE 'vw%'

OPEN cSpatialColumns

FETCH FROM cSpatialColumns INTO @tablename, @fieldname
WHILE @@FETCH_STATUS = 0
BEGIN 
    declare @myquery nvarchar(1000)
    set @myquery = 'select count(*) as ' + @tablename + 'Count from ' + @tablename + ' where ' + @fieldname + '.STSrid <> 102605'
    exec sp_executesql @myquery
    FETCH FROM cSpatialColumns INTO @tablename, @fieldname
END

... and found no spatial objects at all that don't use our SRID of choice. So I'm at a loss. Could it be a matter of this being a bug in 2008 R2?

I tried looking at the SQL for that sproc but it simply executes another sproc to which I apparently don't have access.

1

<facepalm>

I didn't specify the SRID in my input geometry. This change yielded but one SRID in the sproc's results.

declare @geom geometry
set @geom = geometry::STGeomFromText(@geomString,@mySrid)


exec sp_help_spatial_geometry_index 
    @tabname='locPLSTRS', 
    @indexname='IX_locPLSTRS_Shape',  
    @verboseoutput=1, 
    @query_sample=@geom
  • In my defense, why did the procedure accept my WKT without yelling!?!? – id est laborum Feb 7 '18 at 23:17
  • 1
    It happens to us all. Chalk it up to I won't do that again – MickyT Feb 8 '18 at 0:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.