Add Species Data to Database (DB Normalization)

[Reltre]

Current all tree data is stored in a trees table, where each row has all information related to a single tree that is publicly owned and tracked in the city of Santa Monica. This is fine, but managing this data, and updating it may be a bit cumbersome.

One other issue is we don't need all this information depending on what we're trying to display. For instance, let's say we just want to show all trees within a 1 mile radius on the map. All we need for that is the tree location (longitude and latitude). Here is my recommendation for database normalization:

Trees Table

id, species_id, diameter, height, longitude, latitude, street number, street name, side

Species Table

id, common name, botanical name, plant family - botanical, plant family - common, native, EOL_ID

This is just a start. We'll want to add additional data as we develop ways to filter more information about our lovely urban forest in the city of Santa Monica.

[carl-14]

I want to help you with the normalization.

[Reltre]

Great. I think Emily is working on this as well. At the moment we only track individual trees where each tree is a relation in a trees table.

We also want to have a species table as well, and possibly a family table.

Any necessary table and column constraints should also be set as well. On that note, I noticed that the trees table has an index column which seems to be a mapping from an array of trees perhaps? Maybe we should call this id and make it a primary key.

[Reltre]

@ian-r-rose @carl-14 @emillipede What do you think of the proposal above?

[ian-r-rose]

I think that makes good sense!

[emillipede]

Hi @Reltre - thank you for planning out these tables. The fields I have in mind are below. Please let me know what you think.

Family Table

• family_ID
• family_common (EOL)
• family_latin (EOL)

Trees Table (all data from SM trees inventory dataset)

• ID (database ID)
• SM_ID
• SM_tree_number (we need to ask Matt about this field)
• species_ID
• family_ID
• dbh_group
• dbh_min
• dbh_max
• height_group (useful for hotspot?)
• height_min
• height_max
• district (we need to ask Matt about this field)
• longitude
• latitude
• street number
• street name

On deck (data source - google)

• Streetview_imagery

Species Table

• species_ID
• eol_ID (EOL)
• eol_image (EOL)
• name_common
• name_latin
• CA_native (Calflora and Theodore Payne Foundation)
• IUCN_status (IUCN Red List)
• irrigation_requirements (Isabel’s csv from City of Santa Monica Public Works Department PDF)

For relevant non-native species:

• invasive_status (California Invasive Plant Council)

On deck:

• from Santa Monica urban forest administrators
• • heritage trees
• • planting schedule (Kegan's friend may have machine readable data from the city's PDFs)
• • pruning schedule (Kegan's friend may have machine readable data from the city's PDFs)
• • replacement species
• from Canopy Tree Library:
• • Species_Growth
• • Shade_Production
• • Shedability
• • Spread
• • Trunk_clearance
• from i-Tree Tools
• • TBD economic / ecological benefits
• from Theodore Payne Foundation (for CA native species):
• • species_height_x_width
• • native_distribution (within CA)
• • native_habitat
• from TBD data source (for non-native species):
• • native distribution

[emillipede]

• [x] matching the watering information in 'watering requirements' CSV to the list of tree species in the SM trees inventory open dataset.

The new CSV is called irrigation_requirements.csv (https://github.com/emillipede/public-tree-map/blob/master/irrigation_requirements.csv)

Some species in the watering CSV aren't planted anywhere in the city as public trees. In the watering requirements PDFs from the city, not all public trees are represented. Looks like there are guidelines for 170 of the 289 total tree species and varieties.

[emillipede]

we still have some pending data, but the specified datasets are in the DB