As the database, I want to be able to ingest Truck Tracker data

[theecrit]

Description

We want to create a new table in the database to accommodate the data coming from Trucker Tracker, a stand-alone app built for our partner, WOEIP, that allows West Oaklanders to report truck activity in their neighborhood. Truck Tracker will send its data to our database so it can be seen together with the AQ data.

Acceptance criteria

• [ ] New table should include: -- TBD
• [ ] ???

Related documentation

TBD

Dependencies and assumptions

[r-b-g-b]

I don't have a detailed understanding of the TruckTracker data, but here goes... An observation is defined as:

• [ ] What type of polluter (AC bus, 2 axle, 3 axle, bobtail, etc)
• [ ] Observation time
• [ ] Observation location
• [ ] User
• [ ] Upload time

I propose two new tables:

• [ ] Polluter
  • name: str, e.g., "2-axle", "bobtail"
  • description: str, e.g., "A semi-truck with two axles", "Whatever a bobtail is"
• [ ] PolluterObservation
  • polluter: foreign key to Polluter
  • time: datetime
  • location: latlon
  • user: foreign key to User
  • uploaded_at: datetime
  • (perhaps) duration: timedelta, e.g., 4 hours
  • (perhaps) note: str, e.g., "Truck sitting there for 4 hours"

@motching Am I missing anything? I think that these two tables would add a capacity to store stationary observations that do not have numerical values attached to them, which could be useful for other kinds of data in the future.

[motching]

@r-b-g-b thanks for picking this up! My comments and questions:

== General ==

• My understanding is that it's good practice to have an idcolumn in every table as a unique primary key.
  • How easy is it to change tables later, i.e. should we futureproof the schema or we can do DB version bumps, migrations when we want?

== Polluter ==

• If we name the table Polluter and not Truck, I think we should have a type column with the value truck. It's outside of the scope of my thinking now, what kind of other polluters we should include, but I like the idea of aggregating data from different sources.
• Trucks will have a license plate and transport company field, both string and allowed to be null.
• This is a bigger question. There will be a photo upload option for the user. Will / should we be able to insert photos into the DB or should we upload them somewhere and just store the url / path. We probably should store these both as an array on the Polluter table and also with the observations.

== PolluterObservation ==

• We have to differentiate between time of observation which the user inputs and time of reporting which we record
• Trucks can be parked, idling or moving. In practice I've been recording this with two booleans: moving_p and engine_running_p. I don't have a strong preference how we store this though, frontend will map to whatever.
• Because trucks can be moving, we need start and finish locations.

This is for now, I'll make a branch and poke around a bit, probably will have more thoughts after that.

[theecrit]

Thanks for starting to unpack this. Before we problem-solve this particular data set, though, I think it would be good to have a defined approach to solving for new additions in general. My suggestion:

1. First identify the data points that exist for the new set.
2. Map data points to existing tables where possible.
3. Identify the gaps and either adjust or add tables as appropriate.

So we have the following data points coming in from TT:

• User ID
• Report ID (?) (@motching Can you confirm if this is a thing that exists?)
• Report date-time
• Observation date-time
• Vehicle type
• Vehicle start location in lat/lon
• Vehicle end location in lat/lon (should record as the same as start location for idling/non-moving vehicles; @motching Does it do this now? Or would we need to implement?)
• Any others?

Not explicitly collected but potentially extrapolated in the future:

• Pollutant (for which a table already exists)
• Pollutant unit of measure (I thought we already had a table for this but I don't see it in the db model @ifed3 recently posted in Slack; is it possible that one's outdated?)
• Pollutant value (for which a table already exists)

Mapping, then, looks like:

• User ID (TT) = User ID (WOAQ)
• Observation date-time (TT) = Collection (WOAQ)?
• Not sure what else? E.g. could Vehicle start location in lat/lon (TT) be mapped to TimeGeo (WOAQ) somehow?

I'd argue that "Vehicle type" should live in a new table called "Sources," which could allow us to scale this table to incorporate other polluting sources beyond vehicles. But we should definitely have a conversation with WOEIP about this, to understand a bit more about use cases.

Let's chat about all this tonight. :)