Formatting Existing Inventories into Streets Guide - i-Tree
Contents
1. Formatting Existing Inventories for Streets Introduction i lree The Formatting Existing Inventories for Streets Guide was designed to compliment the i Tree Streets User s Manual v5 x This guide will assist users through the basic steps of preparing and importing an existing street tree inventory into i Tree Streets This method is one of the quickest and easiest ways for users with an existing street tree inventory to utilize the i Tree assessment tools This guide is broken down into 4 phases which can be completed in stages as time and resources permit 1 Early decisions 2 Formatting an Existing Street Tree Inventory 3 Creating an Access ready project from an Excel spreadsheet 4 Creating an i Tree Streets project amp importing in data Online Resources This document also refers to additional reference materials which can be accessed on the i Tree website under the Resources Archives tab See the section titled Streets Import Resources Several reference materials will be referred to during various phases of this guide to assist users in understanding steps and available options Phase 1 Early Decisions 1 Requirements Total time needed could vary between a half day to a few days depending on the size and complexity of your existing inventory data and a user s comfort level working with various software applications Microsoft Office Excel amp Access and i Tree Streets are required to complete the import step2. Residential Not Entered Remoy Silver maple Residential Not Entered Routine Silver maple Residential Not Entered Safety CADY Residential Not Entered NAA ULHY B Residential Not Entered Remoy Norway maple Not Entered Safety ULHY B Residential Residential Not Entered Routine Silver maple Residential Not Entered PP EA f ee New Edit Delete Duplicate Help ea o ma Total Records 1872 www itreetools org Updated October 2013 15 x Formatting Existing Inventories for Streets COPY OF Streets Manual page 25 Table 1 Required field names and formats as well as i lree category options for final Streets Access database It is only necessary to include the fields for which you have data Remember to be extra careful with the spelling of the field names as they must be exact SS StreetSeg Number 0 4 000 000 000 if sampling or 0 for complete inventory Zone Number or text 1 4 000 000 000 if number up to 20 characters if text or blank if not recorded SpCode Text Up to 10 characters see www itreetools org gt ee Resources for species codes CityManaged Number 1 if yes O if private 1 or blank if not recorded assumes public tree 1 10 for categories or actual value Possible values LandUse Number 1 10 blank or 0 if not recorded site Type Number 1 10 blank or 0 if not
3. of the Streets formatting example spreadsheet Indicates option it part of a beta module Defaults OF Cancel d User Defined Fields Record OEH Zone Sample Condition Streets Site Type Lor Site LandUse Maintenance Conflicts Other 1 Other 2 Other 3 Priority Task Code Description oo Defaults www itreetools org Updated October 2013 Formatting Existing Inventories for Streets gt Define optional Other1 2 amp 3 fields if they are being used in your inventory data Note predefined User Defined Fields Record CBH Zone Sample Condition Streets Site Type Loc Site Land Use Maintenance Conflicts Other 1 Other Other 3 Define Other Three values correspond with inventory data to be imported IT ree Height Class Code Description _ gt Hit OK when all user defined fields are completed and project will automatically save OF Cancel 7 Once project is saved select File Import Inventory data from the main menu e Import Data x Database File Project Name T Ok aaa Browse gt Select Browse in the dialog box and navigate to the Access database with the STRATUM _Inventory table created in the previous phase gt Select the Access database file and hit Open The Database File field will be populated in the dialog box Hit OK Database File CAD ocuments and Settings zel Project
4. 1 Change Field Names as shown in the manual on page 25 Notice in the table below that Zone is used for quadrant and OtherOne is used for height class data which was provided in ranges in the existing inventory Data fields must be spelled exactly as listed in the manual but do not need to be in the same order ID StreetNumber SpCode OtherOne CondWood 345 732 laphamst Ash Spp NE 68 15 30 Elm Spp Hybrid 2 Change species and data to Streets species codes and accepted values as shown in the Streets manual Midwest species codes FR for Fraxinus spp and ULS for Ulmus spp are used below Values for CondWood and OtherOne were changed to numeric data types Other existing values ID Zone etc were in acceptable formats for import ID Zore SpCode CondWood OtherOne StreetName StreetNumber ee R fes eee ee se nw us 51 4 1 laphamst 735 Formatting considerations The Streets formatting example spreadsheet found on the i Tree website has several worksheets showing a complete inventory as exported from a GIS system and the same data in a different worksheet properly formatted for import into i Tree Streets This is only an example and all data does not need to be imported unless desired An additional worksheet labeled Metadata shows the relationships of existing data with numeric values required for import This information is helpful when later setting up the Streets project user defined field
5. Minmaza Albizia julibrissin selecting Input Species from the main menu Checking records for errors It is a good idea to check records for additional errors by selecting Input Records from the main menu Issues will be noted with a red exclamation mark indicating that a problem exists The example at the right is showing undefined species codes Records can be sorted by ascending or descending values by clicking on the field name This is good a way to identify values out of range due to typos Save your project and run reports page 44 once issues are resolved CityManaged SpCode LandUse SiteT ype BDS OTHER Eeee O SU Oo y o Stocking OF Cancel 2 LocSite e Tree Inventory LocNo Yes Northern hackberry Residential Open Not Entered Routine Bur oak Residential Open Not Entered Routine Silver maple Residential g Not Entered Routine Swamp white oak Not Entered Open Not Entered Nd Swamp white oak Residential Open Not Entered NAA White ash Not Entered Open Not Entered NA Silver maple Residential Open Not Entered Routine Silver maple Residential 4 Not Entered Routine ULHY B Residential Open Not Entered Routine White oak Not Entered Open Not Entered NAA Silver maple Residential Not Entered NA Silver maple
6. menu navigate to All Programs and then select Microsoft Access from the list e Inthe Access dialogue box highlight Blank database under Available Templates Select the folder icon next to the File Name dialog box in the right panel to browse to a location and name your database e Inthe File name dialogue box name your database YourCityProject mdb or similar e Remember to select the Save as type dropdown and select 2002 2003 format mdb if using a newer version of Access e Browse to the location where you want to save it and press OK and then Create button to create and save your new database 2 Import an Excel spreadsheet into the newly created Access database e Select External Data from the top menu and select Excel e Specify the data source by browsing to the file location of your formatted Excel file YourCityData xls select the file e Click on the radial button to Import the source data into a new table in the current database and hit okay e Using the Import Spreadsheet Wizard highlight the Show Worksheets radial button and select the worksheet containing your inventory data records click Next gt e Check the First Row Contains Column Headings box and click Next gt e Field Options Dialog Box Verify that Field Names and Data Types follow the formatting conventions described in the i Tree Streets Manual on page 25 Field names can be renamed here and data fields that are not being imported into Streets can b
7. Hame Cancel Browse I Tree Streets x 1672 records imported successfully gt You should then receive a message indicating that records have been imported successfully gt Troubleshooting If you receive an error carefully check Field names for correct spelling proper sequencing and correct data types in the Access database These are the most common causes of errors during the import process www itreetools org Updated October 2013 Formatting Existing Inventories for Streets gt Isolating an error in data can be difficult if you are working with numerous data fields and a large inventory You can attempt to import in your data eliminating one field at a time to isolate the data field where an error may be Causing a problem 8 Defining new species if you have new species in your data you will receive a message informing you that i Define Species Scientific Assigned Name Sp Value Non Tree Common Mame Species Normvway maple there are unmatched a ee ee ee species gt The program will then launch the Define Species dialog box indicating that species need to be defined Refer to page 41 for species matching steps which will need to be completed at some point You can hit OK and save the project without defining species and complete later by Delete Assigned Species Value Common Name Scientific Name Tree Type Fed maple Acer rubrurn BDL O
8. ated October 2013 Formatting Existing Inventories for Streets 5 User Defined Fields page 28 check boxes will activate data collection fields and should be set up to match the data in your existing inventory Individual tabs will allow you to customize the data parameters within each option to match the data in your inventory as pin User Defined Fields Reportable Data Public Private Trees IY Maintenance Info Pest Signs amp Symptoms M Other One E Street Segment W Land Use Sidewalk Damage M Other Two Land Use Maintenance Conflicte Other 1 Other 2 Other 3 Record CBH Zone Sample Condition Streets Site Type Loc Site IM Condition I Site Type IY Wire Conflicts I Other Three shown in the next 2 screen Inventory Details Captures IY Street Address a Surnv eyor l Location Site GPs Lat Long ral Survey Date Location Number gt Tabs that are not checked on the Record tab will appear grayed out indicating they are not active gt You can always comeback and modify fields after closing this screen by selecting Input Data Fields from the Streets menu 6 Page through the Tabs which are active and change defaults to match the data fields in your reformatted inventory Recommended Maintenance Code Description five Hl Defaults The Maintenance amp Priority descriptions and numeric values shown here are taken from the Metadata worksheet
9. d project 1 Launch Streets amp Create New Project Select File Open New Project Database Project Name gt Click the Specify button and click Create vetor Tee Year be E New which launches the Specify Cimate Regon database location dialog box 2 Navigate to a location where _ 4 save database as you want to create your OH project and type the name Organize v New folder in the File name dialog box MB Desktop n f Downloads Then hit Save This will di Moore_Park_revised automatically populate the aiii Database field in the New I Libraries Project box Computer vr File name Save as type i Tree Steets Database isteets a Hide Folders 3 Fill in the remaining fields in the New Project dialog box e New Project gt Project Name Type a project name bae gt Inventory Type Complete is used for vece m this example gt Climate Region Choose your climate PR RUAS a region from the dropdown Climate Region gt Click Finish when complete This will launch the Define City Dialog box www itreetools org Updated October 2013 Formatting Existing Inventories for Streets 4 Define City Fill in first 4 drop downs for Nation State County amp City Remaining fields can be filled in later if desired by selecting Input City information from the menu Hit OK Define Costs This informatio
10. e excluded by checking the Do not import field skip box e Indexed If you have an ID field in your inventory choose Yes no duplicates for that column All other fields should be left with the default Indexed choice of No Note you will need to highlight each column to verify the Field options selection When data field verification is complete click Next gt e lf you do not have an ID field in your inventory choose Let Access Add Primary Key It will create an ID field as your primary key If you already have an ID field with inventory numbers select Choose My Own Primary Key and select ID from the pull down menu click Next gt to continue e Inthe Import to Table box type the name STRATUM _Inventory click Finish and then OK to complete the import process NOTE Though your mdb file can have any name the table with the Streets formatted inventory must be named STRATUM_Inventory www itreetools org Updated October 2013 Formatting Existing Inventories for Streets e You can select the STRATUM _Inventory table in the available tables panel to i ree verify that your data has imported properly e Save and Exit Access Phase 4 Creating an i Tree Streets project amp importing in data You will now need to go through the process of creating a New Project in Streets by following the project creation amp definition steps in Phase 1 amp 2 of the Streets Manual You will then import your existing data into this newly create
11. f available as it will enhance reporting and analysis of data Zones can also be useful for comparison of areas if your existing inventory has such data available Data fields that will not be imported into Streets can be deleted from the working copy of the inventory spreadsheet 1 www itreetools org Updated October 2013 Formatting Existing Inventories for Streets Phase2 Formatting Existing Data for i Tree Streets i Tr e The following documents and files will be referred to in the next phases They can be accessed or downloaded from the I Tree Tools website under the Resources Manuals amp Workbooks and Resources Archives tabs e i Tree Street User s Manual v5 PDF pages 25 copy at end of guide e Streets Formatting Example Excel spreadsheet e i Tree Streets amp STRATUM Species Codes by Climate Region Excel spreadsheet Data Requirements Refer to page 25 of the Streets manual for proper field names formatting requirements and acceptable value ranges for inventory data Remember only data fields that will be imported need to be reformatted Extra data fields should be deleted to avoid import errors Streets will add in all data fields to your database after importing Examples of steps to reformat an existing inventory are illustrated below using simplified raw inventory data ID_ House Street Common Name Quadrant Diameter Height Class 345 732 laphamst AshSpp NE 68 15 30 Poor
12. n is used for a cost benefit analysis You can fill in values if known or leave blank and fill in later Hit OK Benefit Prices Hit OK to accept default benefit values or customize as desired with local values Current home resale electricity and Natural gas values may be available from local resources and will make results more locally applicable pi Define City Nation United States of America ka Total Municipal Average Sidewalk Budget Width Ft Total Linear Miles Population o of Street mile Total Land Area Average Street eq mi width Ft e Define Cost Public Private All Annual Price of Repair mitigation of Infrastructure Damage Annual Price of Annual Pruning Litter Storm Clean Up Average annual Litigation Annual Tree And Cs And Settlements due to Stump Removal And Tree Related Claims Disposal i Annual Planting Annual Expenditure for Program Administration Annual Pest and A k i i Disease Control nnual Expenditures for Inspection Answer Annual Service Requests Establishment Irrigation fs Other Annual Expenditures Public nnual Total Program yi Benefit Prices Electricity E Kwh SO2 b 2 06 Natural Gas 0 98 T herr YOC E 3 75 Stormwater earl ei Interception 0 027 gallori P10 lb 2 64 Average Home 218 000 Resales Value NO b 4 34 i www itreetools org Upd
13. recorded Locsite Number 1 10 blank or 0 if not recorded WireConflict 1 10 blank or 0 if not recorded CondLvs Number OtherThree Number i Number Number ama 1 10 blank or 0 if not recorded 1 10 blank or 0 if not recorded 1 10 blank or 0 if not recorded GPS Lat Text Decimal degrees 0 90 with direction 35 34642345N 10 www itreetools org Updated October 2013 Formatting Existing Inventories for Streets GPS_Lon Text Decimal degrees 0 180 with direction 98 34642345W StreetName Up to 50 characters StreetNumber Up to 50 characters 11 www itreetools org Updated October 2013
14. reets species code Save work frequently during this process in case you make a mistake and need to restore back to a previous copy ees Year Plant Species Common Name 341 Ailanthus 2008 341 Ailanthus 2008 341 Ailanthus 2008 341 Ailanthus 2008 341 Ailanthus 2008 341 Ailanthus 2008 341 Ailanthus 2008 341 Ailanthus 2008 341 Ailanthus 2008 341 Ailanthus 2008 341 Ailanthus 2008 951 American Basswood 951 American Basswood 951 American Basswood So TT zx g 5 Find Replace 95 Find what american Basswood o sstsi lt s 95 Replace with 7 Ee 95 g5 Replace All Replace Find All Find Nest Close 95 www itreetools org Updated October 2013 Formatting Existing Inventories for Streets Phase 3 Creating an Access ready project from an Excel spreadsheet i Tr C Only Access mdb formatted files can be imported into i Tree Streets all other database file types must be converted to mdb files The following directions detail the process for importing a Streets formatted inventory from an Excel spreadsheet into an Access Table The process outlined below may vary slightly depending on your operating system and the version of Excel or Access you are using Save your Access database as a 2002 2003 file type format if you are using Microsoft Access 2007 or 2010 Instructions below are using Access2010 1 Create a new Access database for Streets e From your computer s Start
15. s 2 www itreetools org Updated October 2013 Formatting Existing Inventories for Streets e New species can be used in the SpCode field if not available in a regional species code list Keep track of new species as they will need to be defined and assigned values in the Streets program later e OtherOne OtherTwo and OtherThree fields are optional and will only allow predefined values such as Hazard Tree 1 Yes 2 No You will not be able to use OtherOne to capture unique data entries such as numeric tree height values Tree heights or other similar unique data types will need to be in predefined ranges such as in the previous example e Tree ID numbers Streets automatically generates ID numbers and uses the ID value for managing records in the database Therefore the ID field is not designed for managing tree tag numbers or identifying and locating trees Street address street name location site and location number are considered values used for field location and identification of street trees The reformatting process can be a time consuming task Users with advance Excel skills may be able to use pivot tables to expedite data reformatting However it is possible to reformat existing data by simply using the Sort option and Find and Replace within a field as shown below Note a copy of the common name field is duplicated for editing changed to SpCode sorted and then each species is changed in the highlighted field to a St
16. s System requirements for i Tree are on page 4 of the Streets manual 2 Considerations amp limitations Streets will provide a snap shot in time of your Street tree resource Streets is an assessment tool which will provide estimates of benefits and values which will enhance strategic planning and management Streets is not designed to be dynamically or frequently updated to manage street trees or track work histories Refer to the Streets manual and other reference documents under the Resources Archives section of the i Tree website to learn more about the methodology and limitations of the application The Community Tree Guides are the best place to start further exploration of how the Streets model works 3 Acquiring existing tree inventory records There are multiple types of street tree inventories available including GIS based systems simple spreadsheets amp custom tree inventory and management systems It is easiest to work with inventory data records in a spreadsheet format where you can easily edit data as required Many inventory systems allow exporting records out as csv files or xls files for use in Microsoft Excel Always work with a copy of your original data 4 Decide what data to import A minimum of tree species and DBH are required for Streets to compute benefit values Including additional information is optional and will allow for more detailed analysis of the resource would recommend including tree Condition i
Download Pdf Manuals
Related Search