# Flags

FlagType:
Significant example of beautiful species


# Geom65MigrationLog

SqlCode:
INSERT INTO Observations ( GlobalID, UserName, CreationDateTime, Latitude, Longitude, Species, Health, DeadwoodPercent, Height, CrownSpread, GroundLevel, DBH, Comments )
SELECT OriginalData9.GlobalID, OriginalData9.UserName, OriginalData9.CreationDateFixed, OriginalData9.x, OriginalData9.y, OriginalData9.Species, OriginalData9.Health, OriginalData9.DeadwoodPercent, OriginalData9.Height, OriginalData9.CrownSpread, 
OriginalData9.GroundLevel, OriginalData9.DBH, OriginalData9.Comments
FROM OriginalData9;

StepDescription:
Appends the given fields data from original data table to main observations table for Group 9.


SqlCode:
INSERT INTO FlagsIntermediate ( GlobalID, FlagID )
SELECT GlobalID, FlagIDParam
FROM OriginalData
WHERE InStr (Flags, "Structure") > 0;

StepDescription:
Enters Flags data to FlagsIntermediate table for Group 9. This query is run 9 times, by changing the FlagIDParam from 0 to 9 for 9 flag types and simultaniously changing Instring value in (WHERE InStr (Flags, "Structure") > 0 ) to the unique word for each flag type. For example, to append all data having FlagID 6, Instr value used is "damage"; because that word is not repeated for any other flag description.


SqlCode:
SELECT Species.SpeciesID, OriginalData.GlobalID INTO SPECIESID
FROM Species INNER JOIN OriginalData ON Species.Species = OriginalData.Species;

StepDescription:
Temporary Intermediate SPECIESID Table that has been deleted. It gives the unique record of the GlobalID that have a unique type of Species from Species table.


SqlCode:
UPDATE OriginalData10 SET Creator = 'Gabby Riefesal'
WHERE Creator = 'griefese@flemingcollege.ca_Fleming';

StepDescription:
Changes Group 10's Email-Ids to their names to match the UserName field to our database. This query is run 5 times to change 5 email-ids.


SqlCode:
UPDATE OriginalData SET OriginalData.CreationDateFixed = [OriginalData].CreationDateTime;

StepDescription:
Corrects Date Time errors for all groups. Changes DateTime field from text type to Date/Time type


SqlCode:
UPDATE OriginalData12 SET UserName = 'Kerstyne Widmeyer'
WHERE UserName = 'kwidmeye@flemingcollege.ca_Fleming';

StepDescription:
Changes Group 12's Email-Ids to their names to match the UserName field to our database. This query is run 4 times to change 4 email-ids.


SqlCode:
UPDATE OriginalData11 SET DBH = DBH*100;

StepDescription:
To Change DBH column unit of Group 9 and Group 11 from meter to centimeter


SqlCode:
Query 1 : UPDATE OriginalData11 SET Species = Replace(Species, '_', ' ')
WHERE Species LIKE '*_*';    Query 2: UPDATE OriginalData11 SET Species = 'Others'
WHERE  Species = 'Other';

StepDescription:
1st query: Changes the Species names of Group 11. For example, "Manitoba_maple" is changed to "Manitoba maple" for 93 records. 2nd Query: Changes Species name from "Other" to "Others" for 6 records. 1 record need to be changed to "Green/Red ash" by adding "/" sign so that was done manually.


SqlCode:
UPDATE OriginalData11 SET Health = 'LowConcerns'
WHERE Health = '2_Less_Concerns_';

StepDescription:
Updates the Health data of Group 11 that matches as ours data. This query is run 5 times for their 5 health variations in the data.


SqlCode:
UPDATE OriginalData10 SET Health = 'Hazardous'
WHERE Health = '6';

StepDescription:
Group 10. Changes the Health field values from a number to its name that matches our groups health classification. For example, '6' Health value is converted to name Hazardous here. This query is run 6 times to update 6 health types.


SqlCode:
UPDATE OriginalData10 SET Species = 'Others'
WHERE Species = 'other';

StepDescription:
Updates Group 10's Species name of 27 records from "other" to "Others". All other species names are changed manually.


SqlCode:
UPDATE OriginalData11 SET UserName = 'Ali Mokheamar'
WHERE UserName = 'Ali';

StepDescription:
Changes Group 11's User names from firstname to full name.


SqlCode:
INSERT INTO Observations ( GlobalID, UserName, CreationDateTime, Latitude, Longitude, Health, DeadwoodPercent, Height, CrownSpread, GroundLevel, DBH, Comments, Accuracy, Species )
SELECT [OriginalData11].GlobalID, OriginalData11.[UserName], [OriginalData11].CreationDateFixed, [OriginalData11].x, [OriginalData11].y, [OriginalData11].[Health], OriginalData11.[DeadwoodPercent], OriginalData11.[Height], OriginalData11.[CrownSpread], OriginalData11.[GroundLevel], OriginalData11.[DBH], [OriginalData11].Comments, [OriginalData11].Accuracy, [OriginalData11].Species
FROM OriginalData11;

StepDescription:
Appends all fields of Group 11 to our Observations table


SqlCode:
INSERT INTO Observations ( GlobalID, UserName, CreationDateTime, Latitude, Longitude, Health, DeadwoodPercent, Height, CrownSpread, GroundLevel, DBH, Comments, Accuracy, Species )
SELECT [OriginalData10].GlobalID, OriginalData10.[UserName], [OriginalData10].CreationDateFixed, [OriginalData10].x, [OriginalData10].y, [OriginalData10].[Health], OriginalData10.[DeadwoodPercent], OriginalData10.[Height], OriginalData10.[CrownSpread], OriginalData10.[GroundLevel], OriginalData10.[DBH], [OriginalData10].Comments, [OriginalData10].Accuracy, [OriginalData10].Species
FROM OriginalData10;

StepDescription:
Appends all fields of Group 10 to our Observations table.


SqlCode:
UPDATE OriginalData12 SET Species = Replace(Species, '_', ' ')
WHERE Species LIKE '*_*';

StepDescription:
Changes Species names of Group12's data to match as ours. For example, "Eastern_white_cedar" is changed to "Eastern white cedar". Species having "Others" is kept as "Other Decidous" and "Other Coniferous" to avoid data loss.


SqlCode:
UPDATE OriginalData12 SET Health = 'ModerateConcerns'
WHERE Health = '3':

StepDescription:
Changes Group 12's Health numbers to Health descriptions as ours.


SqlCode:
INSERT INTO FlagsIntermediate ( GlobalID, FlagID )
SELECT GlobalID, FlagIDParam
FROM OriginalData12
WHERE InStr (Flags, "arborist") > 0;

StepDescription:
Appends Group 12's Flags to IntermediateFlags table. This query is run 8 times for 8 flag types.


SqlCode:
INSERT INTO FlagsIntermediate ( GlobalID, FlagID )
SELECT GlobalID, FlagIDParam
FROM OriginalData11
WHERE InStr (Flags, "structure") > 0;

StepDescription:
Appends Group 11's Flags to IntermediateFlags table. This query is run 8 times for 8 flag types.


SqlCode:
INSERT INTO FlagsIntermediate ( GlobalID, FlagID )
SELECT GlobalID, FlagIDParam
FROM OriginalData10
WHERE InStr (Flags, "9") > 0;

StepDescription:
Appends Flags data of Group 10 to IntermediateFlags table by running it 9 times by changing FlagIDParam from 1 to 9.


SqlCode:
INSERT INTO Observations ( GlobalID, UserName, CreationDateTime, Latitude, Longitude, Health, DeadwoodPercent, Height, CrownSpread, GroundLevel, DBH, Comments, Accuracy, Species )
SELECT [OriginalData12].GlobalID, OriginalData12.[UserName], [OriginalData12].CreationDateFixed, [OriginalData12].x, [OriginalData12].y, [OriginalData12].[Health], OriginalData12.[DeadwoodPercent], OriginalData12.[Height], OriginalData12.[CrownSpread], OriginalData12.[GroundLevel], OriginalData12.[DBH], [OriginalData12].Comments, [OriginalData12].Accuracy, [OriginalData12].Species
FROM OriginalData12;

StepDescription:
Appends Group 12's data to Observations table


# Observations

Comments:
On whitehorse path on East Coast Trail System. Lots of lichen on bole and branches.


Comments:
Some dead branches remain from tree self-pruning. Tree located near beautiful stream.


Comments:
Has coral lichen on lower branches! One of my favourites!


Comments:
Tree falling over in direction away from trail.


Comments:
We think it was already reviewed by an arborist. Spray paint over painted with grey on trunk


Comments:
Its a Gopher tree. Believed that Noah  built arc using gopher .this tree is locally known as nirampall


Comments:
estimated curcumference because russian olive blicked access


Comments:
dead branches from other trees hung up in canopy


Comments:
this tree has been falling apart for over 20 years. My kids used to make up stories about its life


Comments:
it is growing alone. birches usually grow in clumps


Comments:
Near guard rail, may need to be pruned for road access.


Comments:
Soil eroding at base of tree near ditch, could obstruct road if fallen.


Comments:
Healthy tree infront of house, extra care should be taken


# OriginalData10

Comments:
On whitehorse path on East Coast Trail System. Lots of lichen on bole and branches.


Comments:
Some dead branches remain from tree self-pruning. Tree located near beautiful stream.


Comments:
Has coral lichen on lower branches! One of my favourites!


Comments:
Tree falling over in direction away from trail.


Comments:
We think it was already reviewed by an arborist. Spray paint over painted with grey on trunk


Comments:
estimated curcumference because russian olive blicked access


Comments:
dead branches from other trees hung up in canopy


Comments:
this tree has been falling apart for over 20 years. My kids used to make up stories about its life


Comments:
it is growing alone. birches usually grow in clumps


Comments:
Near guard rail, may need to be pruned for road access.


Comments:
Soil eroding at base of tree near ditch, could obstruct road if fallen.


# OriginalData11

Comments:
Healthy tree infront of house, extra care should be taken


# OriginalData9

Comments:
Its a Gopher tree. Believed that Noah  built arc using gopher .this tree is locally known as nirampall


