hackoregon / transportation-systems Goto Github PK

• Create a Postgres Docker Container

• AWS S3 integration

• Document and Define Data Science Stack/Repos - There are 5 different data science related repos right now for our team. I am not sure what they are do or what is intent.

• Serve our data sets:

1. ODOT
2. Trimet Ridership
3. Triment Congestion

To support hackoregon/civic-sandbox#8

• Convert CSV into Postgres

The Microsoft Access database we received from ODOT has some queries that ODOT uses to generate reports. Those would help the API and data exploration process, so we should extract them from the database and add them to the documentation.

• Backend Issue 2: Docker Container Setup
• Data Wrangling Issue 5: Determine SRID used by ODOT in Crash Data
• Crash Data API V1

Approximately prioritized - feel free to adjust:

• 1. Establish ground truth source for database dumps
• 2. Make sure all images have latest PostgreSQL client packages (10.1); add them if needed.
• 3. Add "wait for PostgreSQL" scripts to all images that connect to PostGIS
• 4. Document PostgreSQL image startup processes and environment variables
• 5. Merge crash data ingestion into https://github.com/hackoregon/transportation-system-backend
• 6. Merge ridership data ingestion into https://github.com/hackoregon/transportation-system-backend
• 7. Merge PostGIS image construction into https://github.com/hackoregon/transportation-system-backend, including building with Travis CI as is done with the GeoDjango image.

Dependency - needs to wait for the ODOT crash dump API to be merged to master. Some of this will be done in branches to https://github.com/hackoregon/transportation-system-backend.

I don't have a task list yet but now that the table consolidation is done it's time to explore!!

hackoregon/2019-transportation-data-science#7

See hackoregon/civic-devops#19. DevOps is going with Amazon Linux 2 for a PostgreSQL server, which doesn't have PostGIS packages available. So if we're using GeoDjango against it, we'll need PostGIS. https://docs.djangoproject.com/en/2.0/ref/contrib/gis/install/#spatial-database

Given the unique nature of "Amazon Linux" - it's Yum / RPM based, but there's no guarantee that RHEL/CentOS RPMs will inter-operate correctly with it. So we need to build PostGIS and its dependencies from source.

My plan is to make this available as a Dockerfile / Docker compose file for local development, so we're testing on the same software we're deploying.

Hey folks, as discussed at meeting on Tuesday, DevOps are looking to get some dbs up in development server. At this time naming conventions appears to be only blocking issue.

Tasks I see:

1. rename our databases to append the "transportation-systems" title to beginning of all Databases. See hackoregon/civic-devops#1 (comment) for source of truth
2. (Maybe optional) Standardize usage of hyphens vs underscores?
3. Update our local db variables in the django project and test if any migrations/changes are needed.
4. Provide db on google drive
5. I will upload to s3 for devops to deploy
6. Test local api connection to the dev database

I am marking this as a high priority, provided we can get this up successfully, should allow devops to move forward with some architecture work. @znmeb going to assign this to you.

This is in progress in repository https://github.com/hackoregon/transportation-congestion-analysist. The best overview is in https://github.com/hackoregon/transportation-congestion-analysis/blob/master/src/data/README.md.

My initial plan is to distribute both individual table dumps, and a dump of the complete database for the more adventurous / computationally empowered. I recommend exploring the data a month at a time; there are four tables for each month that take up about 10 GB of your PostgreSQL data directory. The whole thing is about 30 GB.

I've started on this using QGIS. The first thing I'm planning on doing is an animated map of the data over time. QGIS has a plugin that makes this possible; we'd need to filter by bus line or the maps are unreadable but it looks like it can be done without any data other than what we have.

Help wanted sign is up - soliciting more ideas!!

issue issue issue @code-farmhouse

GeoDjango is capable of using Spatialite as a back end instead of PostGIS. (https://docs.djangoproject.com/en/2.0/ref/contrib/gis/install/#spatial-database). While PostGIS is preferred, our deployed application may not have a PostGIS server available, just PostgreSQL.

The ridership data is small-ish and very tidy - Spatialite should be capable of doing any GIS work GeoDjango has to do. And the Spatialite database is an SQLite database - it's just a file that lives in the same filesystem image as the container serving up the Django REST Framework API.

do it now

What we had was just some code, mostly PostGIS, to make a database backup from the raw CSV. We should have a Cookiecutter Data Science repo with the ingestion code as the src/data directory.

See hackoregon/transportation-systems-ridership-analysis#4 for the details.