Difference between two types of drains?

[hackajesse]

• [x] Does the data differentiate between the two types of drains? (@jasonlally @afomi )
• [x] If it does, create issue to design different icons, legend, and write copy explaining the difference (@jasonlally)

[afomi]

name is the meaningful attribute in the Drain dataset.

Here are the different types of Drain names in the dataset:

Area Drain, used in Mission Bay
Caltrans GI 1 Drain
Caltrans GI 4 Drain
Caltrans OS Drain
Caltrans Wall Drain
Catch Basin Drain
Drain
Drain Box
Drain drain, go away
Drain or shine
Drainman
Drop Inlet
LID / Other surface flow inlet point
LID / Other surface flow outlet point
MH Inlet: Grated Cover
Psuedo Drain for Model ONLY
Storm Water Inlet Drain
Switchbox Drain
Track Drain
Trench Drain

[hackajesse]

@afomi thx for this - those are the names people have given to the drains, I believe, unless the table is joining names created by users and types of drains. There are drains that drain to the ocean and drains that drain to the closed sewer system. I'm not sure whether the data differentiate or not.

[afomi]

I'll have to revisit the dataset to see if this data is indicated. If it is, there is not currently a place in Adopta to store it, so a heads up there.

[afomi]

The source dataset does not indicate Type of Drain. What does System_Use_Code mean?

Here is a sample of the source CSV file:

{"DPW_Node_ID"=>"22337",
 "PUC_Maximo_Asset_ID"=>"N-36453",
 "System_Use_Code"=>"COMB",
 "System_Use"=>"Combined",
 "Drain_Type"=>"Storm Water Inlet Drain",
 "POINT_X"=>"6010902.84",
 "POINT_Y"=>"2101561.66",
 "Location"=>"(37.751103, -122.405135)"}

[hackajesse]

Will confirm w/PUC which data type would contain that info.

[jasonlally]

System_Use_Code will contain that distinction. I think this is interesting, but I would put making that distinction in the UI on the backburner and do that in the iteration after MVP.

[jasonlally]

Currently having someone review and update documentation of those categories. Related to #37

[jasonlally]

The System Use Code is likely the type we want to use MS4 are the ones that go out to the bay, COMB and STORM go into the sewer system.

[jasonlally]

We have assets for the two different drain types but no way to differentiate in the data model. We can let this be for the MVP and work on this in the next round if we want to close out this round.

@afomi What do you think of adding a things_extended (or similar) table to maintain the integrity of the core data model but allow some flexibility for us. This would be a 1:1 table that would just add fields that we need for use cases that really only suit us.

The model extended table would have:

city_id storm_use_code

storm_use_code could be used to differentiate among drain types for the purposes of displaying different icons and content to folks.

The added benefit here is we can load the data into a separate table to enable functionality without futzing with the things table. Thoughts?

[afomi]

Hi @jasonlally, I think that sounds complex :smile:

I'd opt to add columns to the Things table, especially since maintainability and reusability does not seem to be a primary concern.

I'm also curious as to what features differentiating the drain types enables for users. Or is it an admin concern?

[jasonlally]

It's primarily to educate users on different drain types and to link campaigns for adoption of certain drains. There are drains that drain to the bay and ocean untreated and drains that drain to the combined sewer system.

I think it's a simple civic layer that engages people in understanding broader systems.

I think you're right, we can keep it simple and just add a column and then load the storm_use_code for each drain, which is on the dataset.

[afomi]

cool. so ya, seems like creating another column to track the additional fields makes sense. then, differentiating the type of drain in the UI - everything that @hackajesse said :smile:

[afomi]

Is there a dataset for the legend? I'm asking because I'm generally curious about how metadata is stored and published for open datasets. Implicitly, we can find unique Drain Type values, or explicitly, there may be a definition/lookup dataset somewhere.

[jasonlally]

There's no dataset for the legend per se. You can implicitly query against any column for unique records using Socrata's API query language SoQL: https://dev.socrata.com/docs/queries/

The current framework for publishing data about fields is lacking and is something we're looking into. In the meantime we're working with publishers to provide a data dictionary document, which while not programmatically accessible, is certainly better than providing no context.

[jasonlally]

For now, we know the variable for drains to ocean or bay is MS4. All others go into the sewer system.

[jasonlally]

I think we pretty much have everything we need per Jesse's original comment (all represented by separate issues). The only thing we're missing is an issue to add the column and pass it with the object to the frontend. In the interest of clarity, I'm creating that issue now.

[jasonlally]

This now addressed by #119