Aim
- Create a BMP style dashboard in GLTG focused on Green Infrastructure Data.
- Create a database by reviewing BMP database structure
Database Design Draft
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