0

I have the following code that allows me to define the bounding box of a spatial database:

with TEMP as(
select 
MIN(geometry.STEnvelope().STPointN(1).STX) as X1
,MIN(geometry.STEnvelope().STPointN(1).STY) as Y1 
,MAX(geometry.STEnvelope().STPointN(3).STX) as X2 
,MAX(geometry.STEnvelope().STPointN(3).STY) as Y2 
from ANCWOODLAND_SRID)

What I'd like to be able to do, is take these values and use them in a create spatial index script, like the one below:

CREATE SPATIAL INDEX [spat_ANCWOODLAND_SRID] ON [dbo].[ANCWOODLAND_SRID] 
(
[GEOMETRY]
)USING  GEOMETRY_GRID 
WITH (
BOUNDING_BOX =(X1,Y1,X2,Y2), GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH), 
CELLS_PER_OBJECT = 128, PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

But I don't know how to do this, any help would be greatly appreciated.

Thanks in advance

SteveC81
  • 35
  • 1
  • 8
  • Hmm… why do you need to do that dynamically? That is, why not figure out your bounding box once and create the index once? – Ben Thul Sep 17 '14 at 19:06
  • It's mainly because I'm giving the scripts to my IT dept to run, and the tables need to be rebuilt on a regular basis (as the source information is coming from an external source), so I don't want to have to keep supplying this information to them when I was hoping it could be automated... – SteveC81 Sep 18 '14 at 11:04
  • 1
    At the risk of turning this into a conversation, you seem to be looking for a smallest bounding box for your data as it exists at any given time. Is there a slightly larger bounding box that you can guarantee will subsume any of your data that would give the same performance characteristics? That is, if all of your data will be in North America, find the bounding box for that one time and then know that your data will be covered by the index. Alternatively, you can specify no bounding box (i.e. whole globe). Try it out... you may be surprised that it works just as well with less maintenance. – Ben Thul Sep 18 '14 at 12:19
  • Hi Ben Thanks for your comments, i think I'll identify a standard bounding box for our area and use that going forward. – SteveC81 Sep 19 '14 at 11:51

0 Answers0