IssueTrac Database

This is one of many components for IssueTrac Project created as my senior project. Contained within this repository are the necessary sql and shell script to setup the application and feature server database(s).

Requirements

• curl is used to download the TIGER/Line® Shapefile(s) from the US Census Bureau
• ogr2ogr used to convert/import the TIGER/Line® Shapefile(s) into the PostgreSQL database.
• PostgreSQL Database >= v14 the primary persist storage used by the IssueTrac application and the feature server.
• PostGIS >= v3.2 a spatial database extender which adds geographic objects and spatial query
• MySQL a relational database which shall soon persist the data related to the actual IssueTrac application. Mostly this is added as a test case for the spatial platform middleware project.

Install

Download

$ curl https://github.com/nasumilu/issue-trac-databsase/archive/refs/tags/v1.0.0.zip --output issue-trac-database.zip
$ unzip issue-trac-database.zip -d issue_trac-database
$ cd issue-trac-database

Git

$ git clone https://github.com/nasumilu/issue-trac-databsase.git
$ cd issue-trac-database

Composer

Add the repository to the projects composer.json file:

"repositories": [
  {
    "type": "vcs",
    "url": "https://github.com/nasumilu/issue-trac-databsase.git"
  }
],
  "require-dev": {
    "nasumilu/issue-trac-database": "v1.0.0" 
}

$ composer update
$ vendor/bin/issuetrac_db --help

NPM

Add teh repository to the projects package.json file

  "scripts": {
    "issuetrac_db": "issuetrac_db"
  },
  "devDependencies": {
    "issue_trac_db": "git+https://github.com/nasumilu/issue-trac-databsase.git#v1.0.2"
  }

$ npm run issuetrac_db postgresql setup

Usage

Postgresql

$ cd src
$ ./issuetrac_db postgresql setup -H localhost -u postgres -P my_password

MySQL

$ cd src
$ ./issuetrac_db mysql setup -H localhost -u postgres -P my_password

This may take awhile, the script needs to download each of the TIGER/Line® Shapefiles and then import the shapefile into the database.

Setup an individual database

First list the databases available for the target platform

$ ./issuetrac_db postgresql list
Database(s) for postgresql
  feature_server
  issue_trac
  tabulation_area
$ ./issuetrac_db postgresql feature_server --user=postgres --password=my_password

Entity Relational Diagram (ERD)

Feature ServerDatabase

Enhanced Entity Relationship Diagram (EERD)

Application Database

Resources & Links

• TIGER/Line® Shapefiles Documentation
• PostgreSQL Database Documentation
• PostGIS Documentation
• MySQL
• issuetrac_db cli

Back to IssueTrac Project