-- Indexes for MS SQL Server
create index ssurgo_chorizon_cokey on ssurgo.ssurgo.chorizon (cokey);
create index ssurgo_component_mukey on ssurgo.ssurgo.component (mukey);
create index ssurgo_component_comppct_r on ssurgo.ssurgo.component (comppct_r);

-- need to make fields enforce not null (should do this during table creation)
drop index soilmu_a_idx_areasymbol on ssurgo.ssurgo.soilmu_a;
alter table ssurgo.ssurgo.soilmu_a alter column areasymbol varchar(40) not null;
drop index soilmu_a_idx_mukey on ssurgo.ssurgo.soilmu_a;
alter table ssurgo.ssurgo.soilmu_a alter column mukey varchar(20) not null;
drop index soilmu_a_idx_musym on ssurgo.ssurgo.soilmu_a;
alter table ssurgo.ssurgo.soilmu_a alter column musym varchar(12) not null;

-- these were the original table indexes
-- create nonclustered index -- this may not be required or even legal when a clustered index is present

-- areasymbol original index
CREATE NONCLUSTERED INDEX [soilmu_a_idx_areasymbol] ON [ssurgo].[soilmu_a]
(
	[areasymbol] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

-- mukey original index
CREATE NONCLUSTERED INDEX [soilmu_a_idx_mukey] ON [ssurgo].[soilmu_a]
(
	[mukey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

-- musym original index
/****** Object:  Index [soilmu_a_idx_musym]    Script Date: 10/10/2015 12:07:01 AM ******/
CREATE NONCLUSTERED INDEX [soilmu_a_idx_musym] ON [ssurgo].[soilmu_a]
(
	[musym] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

-- maybe??
create index ssurgo_soilmu_a on ssurgo.ssurgo.soilmu_a (areasymbol);

-- attempt to create a clustered primary key, this IS required for the geospatial index
ALTER TABLE [ssurgo].[soilmu_a] ADD PRIMARY KEY CLUSTERED 
(
	[ID]
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

-- create geospatial index for ssurgo.soilmu_a table
/****** Object:  Index [geom_sidx]    Script Date: 10/8/2015 2:52:47 PM ******/
CREATE SPATIAL INDEX [geom_sidx] ON [ssurgo].[soilmu_a]
(
	[the_geom]
)USING  GEOMETRY_GRID 
WITH (BOUNDING_BOX =(-179.297280709, -14.366031576, 179.847478053, 179.847478053), GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH), 
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

-- identity column for soilmu_a primary key, required for clustered primary key
ALTER TABLE ssurgo.ssurgo.soilmu_a
ADD ID INT IDENTITY(1,1);

-- row count for soil2.dbo.soilmu_aa: 35,902,299 
-- row count for ssurgo.ssurgo.soilmu_a: 35,983,510
-- row count for postgresql ssurgo.ssurgo.soilmu_a: 36,401,593 

-- soil2 is the ogr import
-- ogr created a clustered primary key and applied the 4326 mapping
-- ssurgo is the fme desktop import
