-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathCART_SoilsQuery_20240925SSMS.sql
2880 lines (2654 loc) · 111 KB
/
CART_SoilsQuery_20240925SSMS.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
-- 2021-09-27 T15:35:41 Edited: 04/30/2024
/** SDA Query application='CART' rule='Generalized Resource Assessment - September 2021' version='FY22' **/
-- Fixed insert into #AoiSoils2 - added MakeValid ; Susan McGlasson ; April 30, 2024
-- Fixed 'Farm Land Class' query - return 0 instead on 'not rated' ; Susan McGlasson ; 05/31/2023
-- Updated Soil Organic Carbon Stock Domain and added Hydric Easements to Air Quality 09/27/2021
-- Includes Air Quality 09/23/2021
-- Limitations for Aerobic Soil Organisms 8/2021
-- BEGIN CREATING AOI QUERY
--
USE sdm
GO
DROP TABLE IF EXISTS #AoiTable
DROP TABLE IF EXISTS #AoiAcres
DROP TABLE IF EXISTS #AoiSoils
DROP TABLE IF EXISTS #AoiSoils2
DROP TABLE IF EXISTS #M2
DROP TABLE IF EXISTS #M5
DROP TABLE IF EXISTS #M4
DROP TABLE IF EXISTS #M6
DROP TABLE IF EXISTS #M8
DROP TABLE IF EXISTS #M10
DROP TABLE IF EXISTS #InterpTable
DROP TABLE IF EXISTS #LandunitRatingsDetailed
DROP TABLE IF EXISTS #LandunitRatingsCART
DROP TABLE IF EXISTS #SDV
DROP TABLE IF EXISTS #RatingClasses
DROP TABLE IF EXISTS #RatingDomain
DROP TABLE IF EXISTS #DateStamps
DROP TABLE IF EXISTS #LandunitMetadata
DROP TABLE IF EXISTS #LandunitRatingsDetailed1
DROP TABLE IF EXISTS #LandunitRatingsDetailed2
DROP TABLE IF EXISTS #LandunitRatingsCART2
DROP TABLE IF EXISTS #interp_dcd
DROP TABLE IF EXISTS #Hydric2
DROP TABLE IF EXISTS #Hydric3
DROP TABLE IF EXISTS #Hydric_A
DROP TABLE IF EXISTS #Hydric_B
DROP TABLE IF EXISTS #Hydric1
DROP TABLE IF EXISTS #Hydric3
DROP TABLE IF EXISTS #easHydric3
DROP TABLE IF EXISTS #drain3
DROP TABLE IF EXISTS #drain4
DROP TABLE IF EXISTS #FC
DROP TABLE IF EXISTS #FC2
DROP TABLE IF EXISTS #wet
DROP TABLE IF EXISTS #wet1
DROP TABLE IF EXISTS #wet2
DROP TABLE IF EXISTS #pf
DROP TABLE IF EXISTS #pf1
DROP TABLE IF EXISTS #pf2
DROP TABLE IF EXISTS #agg1
DROP TABLE IF EXISTS #agg2
DROP TABLE IF EXISTS #agg3
DROP TABLE IF EXISTS #agg4
DROP TABLE IF EXISTS #agg5
DROP TABLE IF EXISTS #agg6
DROP TABLE IF EXISTS #agg7
DROP TABLE IF EXISTS #agg7a
DROP TABLE IF EXISTS #agg8
DROP TABLE IF EXISTS #acpf
DROP TABLE IF EXISTS #muacpf
DROP TABLE IF EXISTS #SOC5
DROP TABLE IF EXISTS #hortopdepth
DROP TABLE IF EXISTS #acpf2
DROP TABLE IF EXISTS #acpfhzn
DROP TABLE IF EXISTS #SOC
DROP TABLE IF EXISTS #SOC2
DROP TABLE IF EXISTS #SOC3
DROP TABLE IF EXISTS #SOC4
DROP TABLE IF EXISTS #SOC5
DROP TABLE IF EXISTS #SOC6
DROP TABLE IF EXISTS #acpfaws
DROP TABLE IF EXISTS #hortopdepthaws
DROP TABLE IF EXISTS #acpf2aws
DROP TABLE IF EXISTS #acpfhznaws
DROP TABLE IF EXISTS #aws
DROP TABLE IF EXISTS #aws150
DROP TABLE IF EXISTS #acpf3aws
DROP TABLE IF EXISTS #acpf4aws
DROP TABLE IF EXISTS #depthtestaws
DROP TABLE IF EXISTS #acpfwtavgaws
DROP TABLE IF EXISTS #alldata
DROP TABLE IF EXISTS #alldata2
DROP TABLE IF EXISTS #aws1
DROP TABLE IF EXISTS #drain
DROP TABLE IF EXISTS #drain2
DROP TABLE IF EXISTS #organic
DROP TABLE IF EXISTS #o1
GO
DECLARE @attributeName CHAR(60);
DECLARE @ruleDesign CHAR(60);
DECLARE @ruleKey CHAR(30);
DECLARE @rating1 CHAR(60);
DECLARE @rating2 CHAR(60);
DECLARE @rating3 CHAR(60);
DECLARE @rating4 CHAR(60);
DECLARE @rating5 CHAR(60);
DECLARE @rating6 CHAR(60);
DECLARE @dateStamp VARCHAR(60);
DECLARE @minAcres INT ;
DECLARE @minPct INT ;
DECLARE @aoiGeom Geometry;
DECLARE @aoiGeomFixed Geometry;
DECLARE @ratingKey CHAR(70);
DECLARE @notRatedPhrase CHAR(15);
DECLARE @Level INT ;
DECLARE @pAoiId INT ;
declare @intersectedPolygonGeometries table (id int, geom geometry);
declare @intersectedPolygonGeographies table (id int, geog geography);
-- Declare all variables here
--|~DeclareChar(@attributeName,60)~
--|~DeclareChar(@ruleDesign,60)~
--|~DeclareChar(@ruleKey,30)~
--|~DeclareChar(@rating1,60)~
--|~DeclareChar(@rating2,60)~
--|~DeclareChar(@rating3,60)~
--|~DeclareChar(@rating4,60)~
--|~DeclareChar(@rating5,60)~
--|~DeclareChar(@rating6,60)~
--|~DeclareVarchar(@dateStamp,20)~
--|~DeclareInt(@minAcres)~
--|~DeclareInt(@minPct)~
--|~DeclareGeometry(@aoiGeom)~
--|~DeclareGeometry(@aoiGeomFixed)~
--|~DeclareChar(@ratingKey,70)~
--|~DeclareChar(@notRatedPhrase,15)~
--|~DeclareInt(@Level)~
-- Create AOI table with polygon geometry. Coordinate system must be WGS1984 (EPSG 4326)
CREATE TABLE #AoiTable
( aoiid INT IDENTITY (1,1),
landunit CHAR(20),
aoigeom GEOMETRY )
;
-- Insert identifier string and WKT geometry for each AOI polygon after this...
SELECT @aoiGeom = GEOMETRY::STGeomFromText('MULTIPOLYGON (((-102.1334674808154 45.944646056283148, -102.1305452386178 45.944662550781629, -102.1250676378794 45.944693468635933, -102.12327175652177 45.944703605814198, -102.12327765248887 45.945772183298914, -102.12330205474188 45.950193894213385, -102.1233054191124 45.950803657359927, -102.12331516688346 45.952569931178118, -102.1233221258513 45.953831080876398, -102.12333600511613 45.956345988730334, -102.1233516074854 45.959173207471792, -102.12459804964163 45.959178487402028, -102.12670803513845 45.959187427580332, -102.13299778465881 45.959214074545628, -102.13341500616872 45.959215842616345, -102.13402890980223 45.959218443460884, -102.13402774158055 45.959111884377819, -102.13401199262148 45.957674528361167, -102.13400912287909 45.957412604789681, -102.13398283564328 45.955013506463786, -102.13397232704421 45.954054476515239, -102.13393392411768 45.950549610965993, -102.13391513994065 45.948835142697533, -102.13390800470529 45.948184012453055, -102.13389569296197 45.947060215585338, -102.13386963505371 45.944681989666435, -102.13386921596879 45.944643788188387, -102.1334674808154 45.944646056283148)))', 4326);
SELECT @aoiGeomFixed = @aoiGeom.MakeValid().STUnion(@aoiGeom.STStartPoint());
INSERT INTO #AoiTable ( landunit, aoigeom )
VALUES ('T9981 Fld3', @aoiGeomFixed);
SELECT @aoiGeom = GEOMETRY::STGeomFromText('MULTIPOLYGON (((-102.12136920366567 45.944704870263536, -102.11670730943416 45.944707968434159, -102.1128892282776 45.944710505426713, -102.11289951024708 45.945739429924686, -102.11291126351028 45.946915639385679, -102.11291860469078 45.947650380666801, -102.11295289231145 45.951081605982836, -102.11297119355163 45.952912994444262, -102.11302630231779 45.95842807180577, -102.11303364349828 45.959162795100383, -102.11314107672411 45.959162904817845, -102.1217964112675 45.959171637252325, -102.12300556925601 45.959172857634769, -102.1233516074854 45.959173207471792, -102.12333600511613 45.956345988730334, -102.1233221258513 45.953831080876398, -102.12331516688346 45.952569931178118, -102.1233054191124 45.950803657359927, -102.12330205474188 45.950193894213385, -102.12327765248887 45.945772183298914, -102.12327175652177 45.944703605814198, -102.12136920366567 45.944704870263536)))', 4326);
SELECT @aoiGeomFixed = @aoiGeom.MakeValid().STUnion(@aoiGeom.STStartPoint());
INSERT INTO #AoiTable ( landunit, aoigeom )
VALUES ('T9981 Fld4', @aoiGeomFixed);
-- End of AOI geometry section
-- #AoiAcres table to contain summary acres for each landunit
CREATE TABLE #AoiAcres
( aoiid INT,
landunit CHAR(20),
landunit_acres FLOAT )
;
-- #LandunitRatingsDetailed1 table
CREATE TABLE #LandunitRatingsDetailed1
( aoiid INT,
landunit CHAR(20),
attributename CHAR(60),
rating_class CHAR(60),
rating_value INT,
rating_key CHAR(60),
rating_pct FLOAT,
rating_acres FLOAT,
landunit_acres FLOAT )
;
-- #LandunitRatingsDetailed2 table
CREATE TABLE #LandunitRatingsDetailed2
(landunit CHAR(20),
attributename CHAR(60),
rating_class CHAR(60),
rating_value INT,
rating_key CHAR(60),
rating_pct FLOAT,
rating_acres FLOAT,
landunit_acres FLOAT,
rolling_pct FLOAT,
rolling_acres FLOAT )
;
-- #LandunitRatingsCART table
CREATE TABLE #LandunitRatingsCART
(id INT,
landunit CHAR(20),
attributename CHAR(60),
rating_class CHAR(60),
rating_value INT,
rating_key CHAR(60),
rolling_pct FLOAT,
rolling_acres FLOAT,
landunit_acres FLOAT )
;
-- #LandunitRatingsCART2 table
-- This table will only contain the final, overall ratings for CART
CREATE TABLE #LandunitRatingsCART2
(id INT IDENTITY (1,1),
landunit CHAR(20),
attributename CHAR(60),
rating_class CHAR(60),
rating_key CHAR(60),
rating_value INT, -- Need to change to rating_value
rolling_pct FLOAT,
rolling_acres FLOAT,
landunit_acres FLOAT,
soils_metadata VARCHAR(150) )
;
-- #AoiSoils table contains intersected soil polygon table with geometry
CREATE TABLE #AoiSoils
( polyid INT IDENTITY (1,1),
aoiid INT,
landunit CHAR(20),
mukey INT,
soilgeom GEOMETRY )
;
-- #AoiSoils2 table contains Soil geometry with landunits
CREATE TABLE #AoiSoils2
( aoiid INT,
polyid INT,
landunit CHAR(20),
mukey INT,
poly_acres FLOAT,
soilgeog GEOGRAPHY )
;
-- #M2 table contains Soil map unit acres, aggregated by mukey (merges polygons together)
CREATE TABLE #M2
( aoiid INT,
landunit CHAR(20),
mukey INT,
mapunit_acres FLOAT )
;
-- #FC table contains Soil map unit acres, aggregated by mukey Farm Class
CREATE TABLE #FC
( aoiid INT,
landunit CHAR(20),
mukey INT,
mapunit_acres FLOAT,
farmlndclass CHAR(30),
farmlndclassvalue INT,
landunit_acres FLOAT )
;
CREATE TABLE #FC2
( aoiid INT,
landunit CHAR(20),
mukey INT,
mapunit_acres FLOAT,
farmlndclass CHAR(30),
farmlndclassvalue INT,
landunit_acres FLOAT,
farmac_pct REAL,
farm_ac_sum REAL,
farmac_sum_pct INT
)
;
-- #M4 table contains Component level data with cokey, comppct_r and mapunit sum-of-comppct_r (major components only)
CREATE TABLE #M4
( aoiid INT,
landunit CHAR(20),
mukey INT,
mapunit_acres FLOAT,
cokey INT,
compname CHAR(60),
comppct_r INT,
majcompflag CHAR(3),
mu_pct_sum INT,
major_mu_pct_sum INT,
drainagecl CHAR(254)
);
-- #M5 table contains Component level ratings for the currently selected soil interpretation
CREATE TABLE #M5
( aoiid INT,
landunit CHAR(20),
mukey INT,
mapunit_acres FLOAT,
cokey INT,
compname CHAR(60),
comppct_r INT,
rating_class CHAR(60),
mu_pct_sum INT )
;
-- #M6 table contains Component level ratings with adjusted component percent to account for missing minor components
CREATE TABLE #M6
( aoiid INT,
landunit CHAR(20),
mukey INT,
mapunit_acres FLOAT,
cokey INT,
compname CHAR(60),
comppct_r INT,
rating_class CHAR(60),
mu_pct_sum INT,
adj_comp_pct FLOAT )
;
-- #M8 table contains Component acres by multiplying map unit acres with adjusted component percent
CREATE TABLE #M8
( aoiid INT,
landunit CHAR(20),
mukey INT,
mapunit_acres FLOAT,
cokey INT,
compname CHAR(60),
comppct_r INT,
rating_class CHAR(60),
MU_pct_sum INT,
adj_comp_pct FLOAT,
co_acres FLOAT )
;
-- #M10 table contains Aggregated rating class values and sum of component acres by landunit (Tract and Field number)
CREATE TABLE #M10
( landunit CHAR(20),
rating_class CHAR(60),
rating_acres FLOAT )
;
-- Hydric table contains soils at the Map Unit, using all map units from table #M2.
CREATE TABLE #Hydric1
(mukey INT,
comp_count INT, -- cnt_comp
count_maj_comp INT, -- cnt_mjr
all_hydric INT, -- cnt_hydric
all_not_hydric INT, -- cnt_nonhydric
maj_hydric INT, -- cnt_mjr_hydric
maj_not_hydric INT, -- cnt_mjr_nonhydric
hydric_inclusions INT, -- cnt_minor_hydric
hydric_null INT ) -- cnt_null_hydric
;
-- #Hydric2 table contains the Low, Rv, and High range for Hydric
CREATE TABLE #Hydric2
(mukey INT,
hydric_rating CHAR (25),
low_pct FLOAT,
rv_pct FLOAT,
high_pct FLOAT )
;
-- #Hydric3 table contains hydric rating acres
CREATE TABLE #Hydric3
(aoiid INT,
landunit CHAR(20),
attributename CHAR(60),
AOI_Acres FLOAT,
rating_class CHAR(60),
rating_key CHAR(60),
mukey INT,
hydric_flag INT,
low_acres FLOAT,
rv_acres FLOAT,
high_acres FLOAT )
;
-- #Easments Hydric3 table contains hydric rating acres
CREATE TABLE #easHydric3
(aoiid INT,
landunit CHAR(20),
attributename CHAR(60),
AOI_Acres FLOAT,
rating_class CHAR(60),
rating_key CHAR(60),
mukey INT,
hydric_flag INT,
low_acres FLOAT,
rv_acres FLOAT,
high_acres FLOAT,
lowpct REAL , rvpct REAL, highpct REAL )
;
-- Identify all hydric components by map unit, using table #M4.
--CREATE TABLE #Hydric_A
-- (mukey INT,
-- cokey INT,
-- hydric_pct INT )
--;
-- Hydric soils at the mapunit level, using all components where hydricrating = 'Yes'.
--CREATE TABLE #Hydric_B
-- (mukey INT,
-- hydric_pct INT )
--;
-- #SDV table contains rule settings from sdvattribute table
CREATE TABLE #SDV
(attributekey BIGINT,
attributename CHAR(60),
attributetablename CHAR(30),
attributecolumnname CHAR(30),
attributelogicaldatatype CHAR(20),
attributefieldsize SMALLINT,
attributeprecision TINYINT,
attributedescription NVARCHAR(MAX),
attributeuom NVARCHAR(60),
attributeuomabbrev NVARCHAR(30),
attributetype CHAR(20),
nasisrulename CHAR(60),
ruledesign NVARCHAR(60),
notratedphrase CHAR(15),
mapunitlevelattribflag TINYINT,
complevelattribflag TINYINT,
cmonthlevelattribflag TINYINT,
horzlevelattribflag TINYINT,
tiebreakdomainname CHAR(40),
tiebreakruleoptionflag TINYINT,
tiebreaklowlabel CHAR(20),
tiebreakhighlabel CHAR(20),
tiebreakrule SMALLINT,
resultcolumnname CHAR(10),
sqlwhereclause CHAR(255),
primaryconcolname CHAR(30),
pcclogicaldatatype CHAR(20),
primaryconstraintlabel CHAR(30),
secondaryconcolname CHAR(30),
scclogicaldatatype CHAR(20),
secondaryconstraintlabel CHAR(30),
dqmodeoptionflag TINYINT,
depthqualifiermode CHAR(20),
layerdepthtotop FLOAT,
layerdepthtobottom FLOAT,
layerdepthuom CHAR(20),
monthrangeoptionflag TINYINT,
beginningmonth CHAR(9),
endingmonth CHAR(9),
horzaggmeth CHAR(30),
interpnullsaszerooptionflag TINYINT,
interpnullsaszeroflag TINYINT,
nullratingreplacementvalue CHAR(254),
basicmodeflag TINYINT,
maplegendkey SMALLINT,
maplegendclasses TINYINT,
maplegendxml XML,
nasissiteid BIGINT,
wlupdated DATETIME,
algorithmname CHAR(50),
componentpercentcutoff TINYINT,
readytodistribute TINYINT,
effectivelogicaldatatype CHAR(20),
rulekey CHAR(30) )
;
-- #RatingClasses table contains the first six rating classes for each interp
CREATE TABLE #RatingClasses
(attributename CHAR(60),
ruledesign CHAR(60),
rating1 CHAR(60),
rating2 CHAR(60),
rating3 CHAR(60),
rating4 CHAR(60),
rating5 CHAR(60),
rating6 CHAR(60) )
;
-- #RatingDomain table containing the domain values for each CART rating for each interp
CREATE TABLE #RatingDomain
(id INT IDENTITY (1,1),
rating_key CHAR(60),
attributename CHAR(60),
rating_class CHAR(60),
rating_value INT )
;
-- #DateStamps table to store survey area datestamps (sacatalog.saverest)
CREATE TABLE #DateStamps
(landunit CHAR(20),
datestamp VARCHAR(32) )
;
-- #LandunitMetadata table to store landunit metadata (survey area and saverest) which comes from #DateStamps
CREATE TABLE #LandunitMetadata
(landunit CHAR(20),
soils_metadata VARCHAR(150) )
;
-- End of CREATE TABLE section
-- Populate #SDV with interp metadata
INSERT INTO #SDV (attributename, attributecolumnname, attributelogicaldatatype, attributetype,
attributeuom, nasisrulename, rulekey, ruledesign, notratedphrase, resultcolumnname,
effectivelogicaldatatype, attributefieldsize, maplegendxml, maplegendkey, attributedescription,
sqlwhereclause, secondaryconcolname, tiebreaklowlabel, tiebreakhighlabel)
SELECT sdv.attributename, sdv.attributecolumnname, sdv.attributelogicaldatatype, sdv.attributetype,
sdv.attributeuom, sdv.nasisrulename, md.rulekey, md.ruledesign, sdv.notratedphrase, sdv.resultcolumnname,
sdv.effectivelogicaldatatype, sdv.attributefieldsize, sdv.maplegendxml, sdv.maplegendkey, sdv.attributedescription,
sdv.sqlwhereclause, sdv.secondaryconcolname,tiebreaklowlabel, tiebreakhighlabel
FROM sdvattribute sdv
LEFT OUTER JOIN distinterpmd md ON sdv.nasisrulename = md.rulename
WHERE sdv.attributename IN ('Agricultural Organic Soil Subsidence', 'Soil Susceptibility to Compaction', 'Organic Matter Depletion', 'Surface Salt Concentration', 'Limitations for Aerobic Soil Organisms', 'Hydric Rating by Map Unit')
GROUP BY md.rulekey, sdv.attributename, sdv.attributecolumnname, sdv.attributelogicaldatatype, sdv.attributetype, sdv.attributeuom, sdv.nasisrulename, sdv.resultcolumnname, sdv.effectivelogicaldatatype,
sdv.attributefieldsize, md.ruledesign, sdv.notratedphrase, sdv.maplegendxml, sdv.maplegendkey, sdv.attributedescription, sqlwhereclause, secondaryconcolname, tiebreaklowlabel, tiebreakhighlabel
;
-- Populate #AoiAcres table
INSERT INTO #AoiAcres (aoiid, landunit, landunit_acres )
SELECT aoiid, landunit,
SUM( ROUND( ( ( GEOGRAPHY::STGeomFromWKB(aoigeom.STAsBinary(), 4326 ).STArea() ) / 4046.8564224 ), 3 ) ) AS landunit_acres
FROM #AoiTable
GROUP BY aoiid, landunit
;
-- Populate #AoiSoils table with intersected soil polygon geometry
INSERT INTO #AoiSoils (aoiid, landunit, mukey, soilgeom)
SELECT A.aoiid, A.landunit, M.mukey, M.mupolygongeo.STIntersection(A.aoigeom ) AS soilgeom
FROM mupolygon M, #AoiTable A
WHERE mupolygongeo.STIntersects(A.aoigeom) = 1
;
-- Populate #AoiSoils2 Soil geometry with landunit attribute
INSERT INTO #AoiSoils2
SELECT aoiid, polyid, landunit, mukey,
ROUND((( GEOGRAPHY::STGeomFromWKB(soilgeom.STAsBinary(), 4326 ).MakeValid().STArea() ) / 4046.8564224 ), 3 ) AS poly_acres,
(GEOGRAPHY::STGeomFromWKB(soilgeom.STAsBinary(), 4326 ).MakeValid()) AS soilgeog
FROM #AoiSoils
;
-- Populate #M2 soil map unit acres, aggregated by mukey (merges polygons together)
INSERT INTO #M2
SELECT DISTINCT M1.aoiid, M1.landunit, M1.mukey,
ROUND (SUM (M1.poly_acres) OVER(PARTITION BY M1.landunit, M1.mukey), 3) AS mapunit_acres
FROM #AoiSoils2 AS M1
GROUP BY M1.aoiid, M1.landunit, M1.mukey, M1.poly_acres
;
-- Populate #FC table with Farm Land Class
-- Fixed 'Farm Land Class' query - return 0 instead on 'not rated'
INSERT INTO #FC
SELECT fcc.aoiid, fcc.landunit, mu.mukey, mapunit_acres,
CASE WHEN farmlndcl IS NULL THEN 'Not rated'
WHEN farmlndcl = 'All areas are prime farmland' THEN 'Prime'
WHEN farmlndcl LIKE 'Prime farmland if%' THEN 'Prime if'
WHEN farmlndcl = 'Farmland of statewide importance' THEN 'State'
WHEN farmlndcl LIKE 'Farmland of statewide importance, if%' THEN 'State if'
WHEN farmlndcl = 'Farmland of local importance' THEN 'Local'
WHEN farmlndcl LIKE 'Farmland of local importance, if%' THEN 'Local if'
WHEN farmlndcl = 'Farmland of unique importance' THEN 'Unique'
ELSE 'Not Prime'
END AS farmlndclass,
CASE WHEN farmlndcl IS NULL THEN 0 -------<<<<<<<<<<<<< sdm
WHEN farmlndcl = 'All areas are prime farmland' THEN 1
WHEN farmlndcl LIKE 'Prime farmland if%' THEN 1
WHEN farmlndcl = 'Farmland of statewide importance' THEN 1
WHEN farmlndcl LIKE 'Farmland of statewide importance, if%' THEN 1
WHEN farmlndcl = 'Farmland of local importance' THEN 1
WHEN farmlndcl LIKE 'Farmland of local importance, if%' THEN 1
WHEN farmlndcl = 'Farmland of unique importance' THEN 1
ELSE 0
END AS farmlndclassvalue, landunit_acres
FROM #M2 AS fcc
INNER JOIN mapunit AS mu ON mu.mukey = fcc.mukey
INNER JOIN #AoiAcres ON #AoiAcres.aoiid=fcc.aoiid
;
INSERT INTO #FC2
SELECT aoiid, landunit, mukey, mapunit_acres, farmlndclass, farmlndclassvalue, landunit_acres, CASE WHEN mapunit_acres = 0 THEN 0 WHEN landunit_acres = 0 THEN 0 ELSE
ROUND ((mapunit_acres / landunit_acres) * 100.0, 2) END AS farmac_pct, SUM (mapunit_acres) OVER(PARTITION BY landunit) AS farm_ac_sum, ROUND (SUM ((mapunit_acres / landunit_acres) * 100.0) OVER(PARTITION BY landunit), 0) AS farmac_sum_pct
FROM #FC
WHERE farmlndclassvalue = 1
-- Enviromental Assessment
INSERT INTO #LandunitRatingsCART2 (landunit, attributename, rating_value, rating_class)
SELECT DISTINCT #FC2.landunit, 'Farm Class' AS attributename,
CASE WHEN farm_ac_sum > 0 THEN 1 ELSE 0 END AS rating_value, CASE WHEN farm_ac_sum > 0 THEN 'Yes' ELSE 'No' END AS rating_class
FROM #AoiAcres
LEFT OUTER JOIN #FC2 ON #AoiAcres.aoiid=#FC2.aoiid
;
-- Easements
INSERT INTO #LandunitRatingsCART2 (landunit, attributename, rating_value, rating_class)
SELECT DISTINCT #FC2.landunit, 'Easements Farm Class' AS attributename,
CASE WHEN farmac_sum_pct >= 50 THEN 1 ELSE 0 END AS rating_value, CASE WHEN farmac_sum_pct>= 50 THEN 'Yes' ELSE 'No' END AS rating_class
FROM #AoiAcres
LEFT OUTER JOIN #FC2 ON #AoiAcres.aoiid=#FC2.aoiid
;
-- Populate #M4 table with component level data with cokey, comppct_r and mapunit sum-of-comppct_r
INSERT INTO #M4
SELECT M2.aoiid, M2.landunit, M2.mukey, mapunit_acres, CO.cokey, CO.compname, CO.comppct_r, CO.majcompflag,
SUM (CO.comppct_r) OVER(PARTITION BY M2.landunit, M2.mukey) AS mu_pct_sum, (SELECT SUM (CCO.comppct_r)
FROM #M2 AS MM2
INNER JOIN component AS CCO ON CCO.mukey = MM2.mukey AND M2.mukey = MM2.mukey AND majcompflag = 'Yes' AND M2.landunit = MM2.landunit) AS major_mu_pct_sum,
drainagecl
FROM #M2 AS M2
INNER JOIN component AS CO ON CO.mukey = M2.mukey
GROUP BY M2.aoiid, M2.landunit, M2.mukey, mapunit_acres, CO.cokey, CO.compname, CO.comppct_r, CO.majcompflag, drainagecl
;
-- Populate #DateStamps with survey area dates for all soil mapunits involved
INSERT INTO #DateStamps
SELECT DISTINCT AM.landunit, ([SC].[areasymbol] + ' ' + CONVERT(VARCHAR(32),[SC].[saverest],120) ) AS datestamp
FROM #M4 AM
INNER JOIN mapunit Mu ON AM.mukey = Mu.mukey
INNER JOIN legend LG ON Mu.lkey = LG.lkey
INNER JOIN sacatalog SC ON Lg.areasymbol = SC.areasymbol
;
-- Populate #LandunitMetadata table with landunit soils-metadata
--
INSERT INTO #LandunitMetadata
SELECT DISTINCT
landunit,
STUFF((SELECT ' | ' + CAST([datestamp] AS VARCHAR(30))
FROM #DateStamps dt2
WHERE dt1.landunit = dt2.landunit
FOR XML PATH ('') ), 1, 2, '') AS soils_metadata
FROM #DateStamps dt1
;
-- END OF STATIC SECTION
-- ************************************************************************************************
-- BEGIN QUERIES FOR SOIL PROPERTIES...
-- ************************************************************************************************
---- START DRAINAGE CLASS------------
CREATE TABLE #drain
( aoiid INT ,
landunit CHAR(20),
landunit_acres FLOAT,
mukey INT,
mapunit_acres FLOAT,
cokey INT,
compname CHAR(280),
comppct_r INT,
majcompflag CHAR(4),
mu_pct_sum INT,
major_mu_pct_sum INT,
drainagecl CHAR(40),
adj_comp_pct DECIMAL (6, 2)
)
;
INSERT INTO #drain
SELECT #M4.aoiid, #M4.landunit, #AoiAcres.landunit_acres, mukey, mapunit_acres, cokey, compname,
comppct_r, majcompflag, mu_pct_sum, major_mu_pct_sum, drainagecl, (1.0 * comppct_r / NULLIF(mu_pct_sum, 0)) AS adj_comp_pct
FROM #M4
LEFT OUTER JOIN #AoiAcres ON #AoiAcres.aoiid = #M4.aoiid
;
CREATE TABLE #drain2
( aoiid INT ,
landunit CHAR(20),
landunit_acres FLOAT,
mukey INT,
mapunit_acres FLOAT,
cokey INT,
compname CHAR(280),
comppct_r INT,
majcompflag CHAR(4),
mu_pct_sum INT,
drainagecl CHAR(40),
adj_comp_pct DECIMAL (6, 2),
co_acres DECIMAL (10, 2))
;
-- Populate drainage class for mapunit and components
INSERT INTO #drain2
SELECT aoiid, landunit, landunit_acres, mukey, mapunit_acres, cokey, compname, comppct_r,
majcompflag, mu_pct_sum, drainagecl, adj_comp_pct, ROUND ( (adj_comp_pct * mapunit_acres), 2) AS co_acres
FROM #drain
;
CREATE TABLE #drain3
( aoiid INT ,
landunit CHAR(20),
landunit_acres REAL,
sum_drain_ac REAL)
;
INSERT INTO #drain3 (aoiid, landunit, landunit_acres, sum_drain_ac )
SELECT DISTINCT aoiid ,
landunit ,
landunit_acres ,
SUM (CASE WHEN drainagecl = 'Poorly drained' THEN co_acres
WHEN drainagecl = 'Very poorly drained' THEN co_acres ELSE 0 END) over(partition by aoiid) as sum_drain_ac
FROM #drain2;
CREATE TABLE #drain4
( aoiid INT ,
landunit CHAR(20),
landunit_acres REAL,
drain_pct REAL)
;
INSERT INTO #drain4 (aoiid, landunit, landunit_acres, drain_pct )
SELECT aoiid, landunit, landunit_acres, CASE WHEN sum_drain_ac = 0 THEN 0 ELSE sum_drain_ac/landunit_acres END AS drain_pct
FROM #drain3;
-- Easement Drainage End
INSERT INTO #LandunitRatingsCART2 (landunit, attributename, rating_value, rating_class)
SELECT DISTINCT landunit, 'Easements Drainage' AS attributename,
CASE WHEN drain_pct >= .50 THEN 1 ELSE 0 END AS rating_value,
CASE WHEN drain_pct >= .50 THEN 'Yes' ELSE 'No' END AS rating_class
FROM #drain4 ;
;
-- Begin SOC
CREATE TABLE #acpf
( aoiid INT ,
landunit CHAR(20),
mukey INT,
mapunit_acres FLOAT,
cokey INT,
compname CHAR(60),
comppct_r INT,
majcompflag CHAR(3),
localphase CHAR(60),
hzname CHAR(20),
hzdept_r INT,
hzdepb_r INT,
awc_r FLOAT,
restrictiondepth INT,
restrictiodepth INT,
TOPrestriction CHAR(80),
tcl CHAR(40),
thickness INT,
om_r FLOAT,
dbthirdbar_r FLOAT,
fragvol INT,
texture CHAR(40),
chkey INT,
mu_pct_sum INT)
;
INSERT INTO #acpf
SELECT DISTINCT
MA44.aoiid ,
MA44.landunit,
MA44.mukey,
MA44.mapunit_acres,
MA44.cokey,
MA44.compname,
MA44.comppct_r,
MA44.majcompflag,
localphase,
hzname,
hzdept_r,
hzdepb_r,
awc_r ,
(SELECT CAST(MIN(resdept_r) AS INTEGER) FROM component LEFT OUTER JOIN corestrictions ON component.cokey = corestrictions.cokey WHERE component.cokey = coa.cokey AND reskind IS NOT NULL) AS restrictiondepth,
(SELECT CASE WHEN MIN (resdept_r) IS NULL THEN 200 ELSE CAST (MIN (resdept_r) AS INT) END FROM component LEFT OUTER JOIN corestrictions ON component.cokey = corestrictions.cokey WHERE component.cokey = coa.cokey AND reskind IS NOT NULL) AS restrictiodepth,
(SELECT TOP 1 reskind FROM component LEFT OUTER JOIN corestrictions ON component.cokey = corestrictions.cokey WHERE component.cokey = coa.cokey AND corestrictions.reskind IN ('Lithic bedrock','Duripan', 'Densic bedrock', 'Paralithic bedrock', 'Fragipan', 'Natric', 'Ortstein', 'Permafrost', 'Petrocalcic', 'Petrogypsic')
AND reskind IS NOT NULL ORDER BY resdept_r) AS TOPrestriction,
(SELECT TOP 1 texcl FROM chtexturegrp AS chtg INNER JOIN chtexture AS cht ON chtg.chtgkey=cht.chtgkey AND chtg.rvindicator = 'yes' AND chtg.chkey=cha.chkey) AS tcl,
CASE WHEN (hzdepb_r-hzdept_r) IS NULL THEN 0 ELSE CAST((hzdepb_r - hzdept_r) AS INT) END AS thickness,
CASE WHEN texture LIKE '%PM%' AND (om_r) IS NULL THEN 35
WHEN texture LIKE '%MUCK%' AND (om_r) IS NULL THEN 35
WHEN texture LIKE '%PEAT%' AND (om_r) IS NULL THEN 35 ELSE om_r END AS om_r ,
CASE WHEN texture LIKE '%PM%' AND (dbthirdbar_r) IS NULL THEN 0.25
WHEN texture LIKE '%MUCK%' AND (dbthirdbar_r) IS NULL THEN 0.25
WHEN texture LIKE '%PEAT%' AND (dbthirdbar_r) IS NULL THEN 0.25 ELSE dbthirdbar_r END AS dbthirdbar_r,
(SELECT CASE WHEN SUM (cf.fragvol_r) IS NULL THEN 0 ELSE CAST (SUM(cf.fragvol_r) AS INT) END FROM chfrags cf WHERE cf.chkey = cha.chkey) as fragvol,
texture,
cha.chkey,
mu_pct_sum
FROM (#M4 AS MA44 INNER JOIN (component AS coa INNER JOIN chorizon AS cha ON cha.cokey = coa.cokey ) ON MA44.cokey=coa.cokey AND MA44.majcompflag = 'yes' )
LEFT OUTER JOIN chtexturegrp AS ct ON cha.chkey=ct.chkey and ct.rvindicator = 'yes'
and CASE WHEN hzdept_r IS NULL THEN 2
WHEN texture LIKE '%PM%' AND om_r IS NULL THEN 1
WHEN texture LIKE '%MUCK%' AND om_r IS NULL THEN 1
WHEN texture LIKE '%PEAT%' AND om_r IS NULL THEN 1
WHEN texture LIKE '%PM%' AND dbthirdbar_r IS NULL THEN 1
WHEN texture LIKE '%MUCK%' AND dbthirdbar_r IS NULL THEN 1
WHEN texture LIKE '%PEAT%' AND dbthirdbar_r IS NULL THEN 1
WHEN om_r IS NULL THEN 2
WHEN om_r = 0 THEN 2
WHEN dbthirdbar_r IS NULL THEN 2
WHEN dbthirdbar_r = 0 THEN 2
ELSE 1 END = 1
;
-- Sums the Component Percent and eliminate duplicate values by cokey
SELECT landunit, aoiid, mapunit_acres , mukey, cokey, (1.0 * comppct_r / NULLIF(mu_pct_sum, 0)) AS adj_comp_pct
INTO #muacpf
FROM #acpf AS acpf2
WHERE acpf2.cokey=cokey
GROUP BY landunit, aoiid, mapunit_acres , mukey, cokey, comppct_r, mu_pct_sum
;
-- grab top depth for the mineral soil and will use it later to get mineral surface properties
-- Because of SOC this wasnt really needed. If any error add statement below back
SELECT compname, cokey, MIN(hzdept_r) AS min_t
INTO #hortopdepth
FROM #acpf
---WHERE texture NOT LIKE '%PM%' and texture NOT LIKE '%DOM' and texture NOT LIKE '%MPT%' AND texture NOT LIKE '%MUCK' AND texture NOT LIKE '%PEAT%'
GROUP BY cokey, compname
;
-- Combine the mineral surface to grab surface mineral properties
SELECT #hortopdepth.cokey,
hzname,
hzdept_r,
hzdepb_r,
thickness,
texture AS texture_surf,
om_r AS om_surf,
dbthirdbar_r AS db_surf,
fragvol AS frag_surf,
chkey
INTO #acpf2
FROM #hortopdepth
INNER JOIN #acpf on #hortopdepth.cokey=#acpf.cokey AND #hortopdepth.min_t = #acpf.hzdept_r
ORDER BY #hortopdepth.cokey, hzname
;
SELECT
mukey,
cokey,
hzname,
restrictiodepth,
hzdept_r,
hzdepb_r,
CASE WHEN (hzdepb_r-hzdept_r) IS NULL THEN 0 ELSE CAST ((hzdepb_r - hzdept_r) AS INT) END AS thickness,
texture,
CASE WHEN dbthirdbar_r IS NULL THEN 0 ELSE dbthirdbar_r END AS dbthirdbar_r,
CASE WHEN fragvol IS NULL THEN 0 ELSE fragvol END AS fragvol,
CASE when om_r IS NULL THEN 0 ELSE om_r END AS om_r,
chkey
INTO #acpfhzn
FROM #acpf
;
--- Depth ranges for SOC ----
SELECT hzname, chkey, comppct_r, hzdept_r, hzdepb_r, thickness,
CASE WHEN hzdept_r < 150 then hzdept_r ELSE 0 END AS InRangeTop_0_150,
CASE WHEN hzdepb_r <= 150 THEN hzdepb_r WHEN hzdepb_r > 150 and hzdept_r < 150 THEN 150 ELSE 0 END AS InRangeBot_0_150,
CASE WHEN hzdept_r < 5 then hzdept_r ELSE 0 END AS InRangeTop_0_5,
CASE WHEN hzdepb_r <= 5 THEN hzdepb_r WHEN hzdepb_r > 5 and hzdept_r < 5 THEN 5 ELSE 0 END AS InRangeBot_0_5,
CASE WHEN hzdept_r < 30 then hzdept_r ELSE 0 END AS InRangeTop_0_30,
CASE WHEN hzdepb_r <= 30 THEN hzdepb_r WHEN hzdepb_r > 30 and hzdept_r < 30 THEN 30 ELSE 0 END AS InRangeBot_0_30,
---5 to 15
CASE WHEN hzdepb_r < 5 THEN 0
WHEN hzdept_r >15 THEN 0
WHEN hzdepb_r >= 5 AND hzdept_r < 5 THEN 5
WHEN hzdept_r < 5 THEN 0
WHEN hzdept_r < 15 then hzdept_r ELSE 5 END AS InRangeTop_5_15 ,
CASE WHEN hzdept_r > 15 THEN 0
WHEN hzdepb_r < 5 THEN 0
WHEN hzdepb_r <= 15 THEN hzdepb_r WHEN hzdepb_r > 15 and hzdept_r < 15 THEN 15 ELSE 5 END AS InRangeBot_5_15,
---15 to 30
CASE WHEN hzdepb_r < 15 THEN 0
WHEN hzdept_r >30 THEN 0
WHEN hzdepb_r >= 15 AND hzdept_r < 15 THEN 15
WHEN hzdept_r < 15 THEN 0
WHEN hzdept_r < 30 then hzdept_r ELSE 15 END AS InRangeTop_15_30 ,
CASE WHEN hzdept_r > 30 THEN 0
WHEN hzdepb_r < 15 THEN 0
WHEN hzdepb_r <= 30 THEN hzdepb_r WHEN hzdepb_r > 30 and hzdept_r < 30 THEN 30 ELSE 15 END AS InRangeBot_15_30,
--30 to 60
CASE WHEN hzdepb_r < 30 THEN 0
WHEN hzdept_r >60 THEN 0
WHEN hzdepb_r >= 30 AND hzdept_r < 30 THEN 30
WHEN hzdept_r < 30 THEN 0
WHEN hzdept_r < 60 then hzdept_r ELSE 30 END AS InRangeTop_30_60 ,
CASE WHEN hzdept_r > 60 THEN 0
WHEN hzdepb_r < 30 THEN 0
WHEN hzdepb_r <= 60 THEN hzdepb_r WHEN hzdepb_r > 60 and hzdept_r < 60 THEN 60 ELSE 30 END AS InRangeBot_30_60,
---60 to 100
CASE WHEN hzdepb_r < 60 THEN 0
WHEN hzdept_r >100 THEN 0
WHEN hzdepb_r >= 60 AND hzdept_r < 60 THEN 60
WHEN hzdept_r < 60 THEN 0
WHEN hzdept_r < 100 then hzdept_r ELSE 60 END AS InRangeTop_60_100 ,
CASE WHEN hzdept_r > 100 THEN 0
WHEN hzdepb_r < 60 THEN 0
WHEN hzdepb_r <= 100 THEN hzdepb_r WHEN hzdepb_r > 100 and hzdept_r < 100 THEN 100 ELSE 60 END AS InRangeBot_60_100,
--100 to 200
CASE WHEN hzdepb_r < 100 THEN 0
WHEN hzdept_r >200 THEN 0
WHEN hzdepb_r >= 100 AND hzdept_r < 100 THEN 100
WHEN hzdept_r < 100 THEN 0
WHEN hzdept_r < 200 then hzdept_r ELSE 100 END AS InRangeTop_100_200 ,
CASE WHEN hzdept_r > 200 THEN 0
WHEN hzdepb_r < 100 THEN 0
WHEN hzdepb_r <= 200 THEN hzdepb_r WHEN hzdepb_r > 200 and hzdept_r < 200 THEN 200 ELSE 100 END AS InRangeBot_100_200,
CASE WHEN hzdepb_r < 20 THEN 0
WHEN hzdept_r >50 THEN 0
WHEN hzdepb_r >= 20 AND hzdept_r < 20 THEN 20
WHEN hzdept_r < 20 THEN 0
WHEN hzdept_r < 50 then hzdept_r ELSE 20 END AS InRangeTop_20_50 ,
CASE WHEN hzdept_r > 50 THEN 0
WHEN hzdepb_r < 20 THEN 0
WHEN hzdepb_r <= 50 THEN hzdepb_r WHEN hzdepb_r > 50 and hzdept_r < 50 THEN 50 ELSE 20 END AS InRangeBot_20_50,
CASE WHEN hzdepb_r < 50 THEN 0
WHEN hzdept_r >100 THEN 0
WHEN hzdepb_r >= 50 AND hzdept_r < 50 THEN 50
WHEN hzdept_r < 50 THEN 0
WHEN hzdept_r < 100 then hzdept_r ELSE 50 END AS InRangeTop_50_100 ,
CASE WHEN hzdept_r > 100 THEN 0
WHEN hzdepb_r < 50 THEN 0
WHEN hzdepb_r <= 100 THEN hzdepb_r WHEN hzdepb_r > 100 and hzdept_r < 100 THEN 100 ELSE 50 END AS InRangeBot_50_100,
om_r, fragvol, dbthirdbar_r, cokey, mukey, 100.0 - fragvol AS frag_main
INTO #SOC
FROM #acpf
ORDER BY cokey, hzdept_r ASC, hzdepb_r ASC, chkey
;
SELECT mukey, cokey, hzname, chkey, comppct_r, hzdept_r, hzdepb_r, thickness,
InRangeTop_0_150,
InRangeBot_0_150,
InRangeTop_0_30,
InRangeBot_0_30,
InRangeTop_20_50,
InRangeBot_20_50,
InRangeTop_50_100 ,
InRangeBot_50_100,
(( ((InRangeBot_0_150 - InRangeTop_0_150) * ( ( om_r / 1.724 ) * dbthirdbar_r )) / 100.0 ) * ((100.0 - fragvol) / 100.0)) AS HZ_SOC_0_150,
(( ((InRangeBot_0_30 - InRangeTop_0_30) * ( ( om_r / 1.724 ) * dbthirdbar_r )) / 100.0 ) * ((100.0 - fragvol) / 100.0)) AS HZ_SOC_0_30,
---Removed * ( comppct_r * 100 )
((((InRangeBot_20_50 - InRangeTop_20_50) * ( ( om_r / 1.724 ) * dbthirdbar_r )) / 100.0 ) * ((100.0 - fragvol) / 100.0)) AS HZ_SOC_20_50,
---Removed * ( comppct_r * 100 )
((((InRangeBot_50_100 - InRangeTop_50_100) * ( ( om_r / 1.724 ) * dbthirdbar_r )) / 100.0 ) * ((100.0 - fragvol) / 100.0)) AS HZ_SOC_50_100,
(( ((InRangeBot_0_5 - InRangeTop_0_5) * ( ( om_r / 1.724 ) * dbthirdbar_r )) / 100.0 ) * ((100.0 - fragvol) / 100.0)) AS HZ_SOC_0_5,
(( ((InRangeBot_5_15 - InRangeTop_5_15) * ( ( om_r / 1.724 ) * dbthirdbar_r )) / 100.0 ) * ((100.0 - fragvol) / 100.0)) AS HZ_SOC_5_15,
(( ((InRangeBot_15_30 - InRangeTop_15_30) * ( ( om_r / 1.724 ) * dbthirdbar_r )) / 100.0 ) * ((100.0 - fragvol) / 100.0)) AS HZ_SOC_15_30,
(( ((InRangeBot_30_60 - InRangeTop_30_60) * ( ( om_r / 1.724 ) * dbthirdbar_r )) / 100.0 ) * ((100.0 - fragvol) / 100.0)) AS HZ_SOC_30_60,
(( ((InRangeBot_60_100 - InRangeTop_60_100) * ( ( om_r / 1.724 ) * dbthirdbar_r )) / 100.0 ) * ((100.0 - fragvol) / 100.0)) AS HZ_SOC_60_100,
(( ((InRangeBot_100_200 - InRangeTop_100_200) * ( ( om_r / 1.724 ) * dbthirdbar_r )) / 100.0 ) * ((100.0 - fragvol) / 100.0)) AS HZ_SOC_100_200
---Removed * ( comppct_r * 100 )
INTO #SOC2
FROM #SOC
ORDER BY mukey ,cokey, comppct_r DESC, hzdept_r ASC, hzdepb_r ASC, chkey
;
---Aggregates and sum it by component.
SELECT DISTINCT cokey, mukey,
ROUND (SUM (HZ_SOC_0_150) over(PARTITION BY cokey) ,4) AS CO_SOC_0_150,
ROUND (SUM (HZ_SOC_0_30) over(PARTITION BY cokey) ,4) AS CO_SOC_0_30,
ROUND (SUM (HZ_SOC_20_50) over(PARTITION BY cokey),4) AS CO_SOC_20_50,
ROUND (SUM (HZ_SOC_50_100) over(PARTITION BY cokey),4) AS CO_SOC_50_100,
ROUND (SUM (HZ_SOC_0_5) over(PARTITION BY cokey),4) AS CO_SOC_0_5,
ROUND (SUM (HZ_SOC_5_15) over(PARTITION BY cokey),4) AS CO_SOC_5_15,
ROUND (SUM (HZ_SOC_15_30) over(PARTITION BY cokey),4) AS CO_SOC_15_30,
ROUND (SUM (HZ_SOC_30_60) over(PARTITION BY cokey),4) AS CO_SOC_30_60,
ROUND (SUM (HZ_SOC_60_100) over(PARTITION BY cokey),4) AS CO_SOC_60_100,
ROUND (SUM (HZ_SOC_100_200) over(PARTITION BY cokey),4) AS CO_SOC_100_200
INTO #SOC3
FROM #SOC2
GROUP BY mukey, cokey, HZ_SOC_0_150, HZ_SOC_0_30, HZ_SOC_20_50, HZ_SOC_50_100, HZ_SOC_0_5, HZ_SOC_5_15, HZ_SOC_15_30, HZ_SOC_30_60, HZ_SOC_60_100, HZ_SOC_100_200
;
SELECT DISTINCT #SOC3.cokey, #SOC3.mukey, adj_comp_pct AS WEIGHTED_COMP_PCT,
CO_SOC_0_30, CO_SOC_0_30 * adj_comp_pct AS WEIGHTED_CO_SOC_0_30,
CO_SOC_20_50, CO_SOC_20_50 * adj_comp_pct AS WEIGHTED_CO_SOC_20_50,
CO_SOC_50_100, CO_SOC_50_100 * adj_comp_pct AS WEIGHTED_CO_SOC_50_100,
CO_SOC_0_150, CO_SOC_0_150 * adj_comp_pct AS WEIGHTED_CO_SOC_0_150,
CO_SOC_0_5, CO_SOC_0_5 * adj_comp_pct AS WEIGHTED_CO_SOC_0_5,
CO_SOC_5_15, CO_SOC_5_15 * adj_comp_pct AS WEIGHTED_CO_SOC_5_15,
CO_SOC_15_30, CO_SOC_15_30 * adj_comp_pct AS WEIGHTED_CO_SOC_15_30,
CO_SOC_30_60, CO_SOC_30_60 * adj_comp_pct AS WEIGHTED_CO_SOC_30_60,
CO_SOC_60_100, CO_SOC_60_100 * adj_comp_pct AS WEIGHTED_CO_SOC_60_100,
CO_SOC_100_200 , CO_SOC_100_200 * adj_comp_pct AS WEIGHTED_CO_SOC_100_200
INTO #SOC4
FROM #SOC3
INNER JOIN #muacpf ON #muacpf.cokey = #SOC3.cokey
GROUP BY #SOC3.cokey, #SOC3.mukey, adj_comp_pct , CO_SOC_0_30, CO_SOC_20_50,CO_SOC_50_100, CO_SOC_0_150, CO_SOC_0_5, CO_SOC_5_15, CO_SOC_15_30, CO_SOC_30_60,CO_SOC_60_100, CO_SOC_100_200
;
-- Unit Conversion
SELECT DISTINCT #M4.mukey, #M4.aoiid ,
#M4.landunit,
landunit_acres, mapunit_acres, ROUND (SUM (WEIGHTED_CO_SOC_0_30) OVER(PARTITION BY #M4.aoiid, #SOC4.mukey) ,4) * 100 AS SOCSTOCK_0_30 ,
ROUND (SUM (WEIGHTED_CO_SOC_20_50) OVER(PARTITION BY #M4.aoiid ,#SOC4.mukey) ,4) * 100 AS SOCSTOCK_20_50 ,
ROUND (SUM (WEIGHTED_CO_SOC_50_100) OVER(PARTITION BY #M4.aoiid ,#SOC4.mukey) ,4) * 100 AS SOCSTOCK_50_100,
ROUND (SUM (WEIGHTED_CO_SOC_0_150) OVER(PARTITION BY #M4.aoiid ,#SOC4.mukey) ,4) * 100 AS SOCSTOCK_0_150,
ROUND (SUM (WEIGHTED_CO_SOC_0_5) OVER(PARTITION BY #M4.aoiid ,#SOC4.mukey) ,4) * 100 AS SOCSTOCK_0_5 ,
ROUND (SUM (WEIGHTED_CO_SOC_5_15) OVER(PARTITION BY #M4.aoiid ,#SOC4.mukey) ,4) * 100 AS SOCSTOCK_5_15 ,
ROUND (SUM (WEIGHTED_CO_SOC_15_30) OVER(PARTITION BY #M4.aoiid ,#SOC4.mukey) ,4) * 100 AS SOCSTOCK_15_30 ,
ROUND (SUM (WEIGHTED_CO_SOC_30_60) OVER(PARTITION BY #M4.aoiid ,#SOC4.mukey) ,4) * 100 AS SOCSTOCK_30_60 ,
ROUND (SUM (WEIGHTED_CO_SOC_60_100) OVER(PARTITION BY #M4.aoiid ,#SOC4.mukey) ,4) * 100 AS SOCSTOCK_60_100 ,
ROUND (SUM (WEIGHTED_CO_SOC_100_200) OVER(PARTITION BY #M4.aoiid ,#SOC4.mukey) ,4) * 100 AS SOCSTOCK_100_200
INTO #SOC5
FROM #SOC4
LEFT OUTER JOIN #M4 ON #M4.mukey = #SOC4.mukey
LEFT OUTER JOIN #AoiAcres ON #AoiAcres.aoiid = #M4.aoiid
GROUP BY #M4.mukey, #SOC4.mukey, #M4.aoiid ,
#M4.landunit,
landunit_acres, mapunit_acres,WEIGHTED_CO_SOC_0_30, WEIGHTED_CO_SOC_20_50, WEIGHTED_CO_SOC_50_100, WEIGHTED_CO_SOC_0_5, WEIGHTED_CO_SOC_5_15, WEIGHTED_CO_SOC_15_30, WEIGHTED_CO_SOC_30_60, WEIGHTED_CO_SOC_60_100, WEIGHTED_CO_SOC_100_200, #SOC4.WEIGHTED_CO_SOC_0_150
;