Let's say we have a table that contains two terrain datasets defined by TerrainModelID like below:
CREATE TABLE [dbo].[DigitalTerrain]( [DigitalTerrainID] [uniqueidentifier] NOT NULL, [Longitude] [float] NULL, [Latitude] [float] NULL, [ZCoordinate] [float] NULL, [Tile] [nvarchar](10) NULL, [TerrainModelID] [uniqueidentifier] NULL, [Location] [geography] NULL, CONSTRAINT [PK__DigitalTerrainID] PRIMARY KEY CLUSTERED(DigitalTerrainID))
I have a spatial index, Location_IX and also an index on the TerrainModelID column. In a query like the below is having the two datasets in the same table going to cause the spatial index issues/slowdowns?
SELECT Location, ZCoordinate, TerrainModelID FROM TerrainData.dbo.DigitalTerrain WITH(INDEX(Location_IX) NOLOCK) WHERE @line.STDistance(DigitalTerrain.Location) < (@margin) AND TerrainModelID = @TerModID
Is it still able to filter out the terrain model dataset we want and use the spatial index efficiently? Or should we differ the design or query here? Should this have been split into two tables, one for each terrain dataset?