...
- 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
- location of GI by practice,
- Map of HUC8
- thematic map by count of GI by HUC8
- table showing count GI by HUC8
- thematic map by count of GI by HUC8
- Map of GI
Database Design Draft
draw.io Diagram | |||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
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