Importing Data

Importing Data

OpenTreeMap includes data importers for species data, existing tree inventories and i-Tree values. The site also requires imports of municipal and zipcode geographies.

Note

All three of these import scripts have a --verbose option, useful if you run into errors.

i-Tree Importer

The i-Tree suite is available as a free download from the USDA at itreetools.org. Download and install the i-Tree Suite, which includes the i-Tree Streets module. Each i-Tree region has a ResourceUnit spreadsheet in iTree's data files, located in the installation directory (for example, C:\Program Files (x86)\i-Tree\STREETS\Data\ResourceUnit). The following list of worksheets are required:

Hydro interception, AQ Ozone dep, AQ NOx dep, AQ PM10 dep, AQ SOx dep, AQ NOx avoided, AQ PM10 avoided, AQ SOx avoided, AQ VOC avoided, BVOC, CO2 avoided, CO2 sequestered, CO2 stored, Natural Gas, Electricity

NB: If saved out of Excel (possibly other programs too), CO2 stored and Natural Gas sheets do not have correct formatting. Make sure commas are the only delimiter, there are no commas/quotes in the numbers (ie 1643.9 not "1,643.9"), and no commas at end of lines. LibreOffice can do this - set delimiter options when you open the CSV, save as CSV again.

For each spreadsheet above, enter the word 'code' without quotes into the A1 cell and save the spreadsheet as a CSV file. Run the following command from the project's root directory for each CSV file:

python manage.py itree_import <filename>.csv <fieldname>

The field name can be found in the database table treemap_resources and is generally the name of the spreadsheet with spaces replaced with underscores (_) and _dbh appended to the end, though this is not the case for every field. The region column will be automatically filled in, but can be changed manually after loading.

For reference, here are the fields from the treemap_resources database table as of v1.2, Sept 2012:

• hydro_interception_dbh
• aq_ozone_dep_dbh
• aq_nox_dep_dbh
• aq_pm10_dep_dbh
• aq_sox_dep_dbh
• aq_nox_avoided_dbh
• aq_pm10_avoided_dbh
• aq_sox_avoided_dbh
• aq_voc_avoided_dbh
• bvoc_dbh
• co2_sequestered_dbh
• co2_avoided_dbh
• natural_gas_dbh
• electricity_dbh
• co2_storage_dbh

Species importer

A template file (species_import_template.csv) and doc file (species_import_doc.txt) for the species importer can be found in the treemap/management/commands folder. This file will update existing species where a match is found and import new species records otherwise. i-Tree data must be imported before species can be imported.

Tree importer

A template file (uimport_template.csv) and doc file (uimport_doc.txt) for the tree importer can be found in the treemap/management/commands folder. Species data must be imported and the choices.py file should be updated before tree data can be imported.

python manage.py uimport --verbose <filename>.csv <data_owner_id>

Where "data_owner_id" is the unique identifier (auth_user.id) of an existing user.

Geographic coordinates are the only required fields for this importer, however several scenarios will cause a tree to fail import. All trees that fail import will be added to an error file which can be corrected and re-imported. The failure scenarios are:

• No coordinates, or coordinates are 0,0 --OR--

• Species information does not match existing species in the database --OR--

• Tree fails the 10 ft proximity test for the following reasons:

  If a tree in the import file and a tree in the database have different species, the new tree is not entered as it is impossible to know whether the new tree is a replacement for the old tree or an addition to the trees in that location.

  If a tree in the new database is near two or more trees in the base layer, the new tree is not entered if there is more than one tree with an unknown species in the base layer. We have no method for accurately determining which old tree should be replaced by the new tree.

  CAVEAT: Trees in the same import event will never fail due to proximity checks.

Geography imports

Currently, geographies are in two tables in the database and can be imported from standard shapefiles using the PostgreSQL tool shp2pgsql. The zipcode table should hold a contiguous zip code layer that covers the same area as the neighborhood table. The neighborhood table can hold multiple overlapping polygons, but should be contiguous when viewed together. The extent of the neighborhood table determines the area where users are allowed to add trees. Recommended geographies are:

• Counties
• Municipal boundaries
• Neighborhoods within larger municipalities

For searchability, a county's name column should be in the format "Xxxxxxxxx County", while a county's county column should be only the "Xxxxxxxxx" portion of the name. Geography names may include spaces. If nested geography are used, smaller or internal polygons should be imported first, followed by the larger polygons.