Aim

• Create a BMP style dashboard in GLTG focused on Green Infrastructure Data.
• Create a database by reviewing BMP database structure

Project End date

• July, 2023

Features/Tasks:

• Building database and API
• Web visualization/application
  • Map of GI
    • location of GI by practice,
      • table showing count by practices
    • location of GI by category
      • table showing count by category
  • Map of HUC8
    • thematic map by count of GI by HUC8
      • table showing count GI by HUC8

Database Design Draft

BMP Database Structure

Dev Environment Schema (So far...)

SQL SCHEMA Creation Commands (Draft)

CREATE TABLE HUC8 (
id INT NOT NULL,
name VARCHAR(8),
geometry geometry(Multipolygon, 4326)
);

CREATE TABLE practice_types(
practice_type_id int PRIMARY KEY,
practice_catgeory VARCHAR(255) NOT NULL,
other_description VARCHAR(255) NOT NULL,
funding_program VARCHAR(255),
practice_units VARCHAR(255)
)

CREATE TABLE practices(
practice_id int NOT NULL,
city VARCHAR(50),
county VARCHAR(50),
installation_date int not null,
cost float,
practice_drainage_area float,
new_development_or_retrofit VARCHAR(50),
total_nitrogen_reduction float,
total_phosphoruous_reduction float,
pounds_N_reduced float,
pounds_P_reduced float,
type_id int NOT NULL,
FOREIGN KEY (type_id) REFERENCES practice_types(practice_type_id)    
)

CREATE TABLE huc_practice_relations(
huc_practice_id int PRIMARY KEY,
huc8_id int NOT NULL REFERENCES huc8(id),
practice_id int NOT NULL REFERENCES practices(practice_id)
)

Method to push HUC8 data to table

Section 5.4 in https://postgis.net/workshops/postgis-intro/loading_data.html

API

UI