🌳 Core Table: trees
Represents each instance of a tree with temporal and spatial attributes.
| Column |
Type |
Description |
tree_id |
UUID / INT |
Primary key |
species |
TEXT |
Scientific/common name |
maturation |
TEXT |
Seedling, Juvenile, Mature, etc. |
health |
TEXT |
Good, Fair, Poor, etc. |
status |
TEXT |
Alive, Planned, Dead |
last_seen |
DATE |
Date of last recorded observation |
parcel_id |
FK → parcels |
Spatial relationship |
neighborhood_id |
FK → neighborhoods |
Lookup by area |
📍 parcels Table
Stores location and geospatial identifiers, useful for mapping and planning.
| Column |
Type |
Description |
parcel_id |
UUID |
Primary key |
address |
TEXT |
Street address |
lat |
FLOAT |
Latitude |
long |
FLOAT |
Longitude |
geoid |
TEXT |
Geographic ID |
district |
TEXT |
Administrative district |
🏘️ neighborhoods Table
Standard lookup table for mapping trees and parcels.
| Column |
Type |
Description |
neighborhood_id |
UUID |
Primary key |
name |
TEXT |
Name of neighborhood |
district |
TEXT |
Corresponding district |
📊 tree_status_logs Table (Temporal State Logging)
Track state changes over time, good for longitudinal analysis.
| Column |
Type |
Description |
log_id |
UUID |
Primary key |
tree_id |
FK → trees |
Which tree this log belongs to |
status |
TEXT |
Alive, Planned, Dead |
health |
TEXT |
Health at the time of log |
log_date |
DATE |
When this status was logged |
⚖️ ej_metrics Table (Environmental Justice Metrics by Neighborhood)
| Column |
Type |
Description |
ej_metric_id |
UUID |
Primary key |
neighborhood_id |
FK → neighborhoods |
Associated neighborhood |
metric_name |
TEXT |
e.g. Heat Vulnerability Index |
metric_value |
FLOAT |
Quantified score or percentile |
source |
TEXT |
Data source (e.g. EJScreen, CDC, etc.) |
Optional Lookup Tables