Missing columns from bcfishpass needed for past cost estimate workflow

[NewGraphEnvironment]

Past cost estimates were built from columns in fish_passage.modelled_crossings_closed_bottom which no longer exists. Below are the outstanding columns that are not in bcfishpass.crossings

file_type_description from whse_forest_tenure.ften_road_section_lines_svw

road_class was in whse_basemapping.transport_line_type_code which can be linked with the modelled_crossing_id using bcfishpass.modelled_stream_crossings through the transport_line_id .

[NewGraphEnvironment]

https://dataedo.com/kb/query/postgresql/find-tables-with-specific-column-name

dbGetQuery(conn,
           "select t.table_schema,
           t.table_name
           from information_schema.tables t
           inner join information_schema.columns c on c.table_name = t.table_name 
           and c.table_schema = t.table_schema
           where c.column_name = 'file_type_description'
           and t.table_schema not in ('information_schema', 'pg_catalog')
           and t.table_type = 'BASE TABLE'
           order by t.table_schema;")

    table_schema                  table_name

1 whse_forest_tenure ften_road_section_lines_svw

[NewGraphEnvironment]

related to #42

dbGetQuery(conn,
           "select t.table_schema,
           t.table_name
           from information_schema.tables t
           inner join information_schema.columns c on c.table_name = t.table_name 
           and c.table_schema = t.table_schema
           where c.column_name = 'road_class'
           and t.table_schema not in ('information_schema', 'pg_catalog')
           and t.table_type = 'BASE TABLE'
           order by t.table_schema;")

      table_schema               table_name
1 whse_basemapping transport_line_type_code

> unique(bcfishpass$transport_line_type_description)
 [1] "Road unclassified"             "Road resource demographic"     NA                              "Road resource"                
 [5] "Road local"                    "Road arterial minor"           "Road highway major"            "Road collector minor"         
 [9] "Private driveway demographic"  "Road recreation demographic"   "Trail recreation"              "Road service"                 
[13] "Trail"                         "Road strata"                   "Road arterial major"           "Road driveway non-demographic"
[17] "Road restricted"              
> unique(bcfishpass$transport_line_surface_description)
[1] "rough"     "loose"     "unknown"   NA          "paved"     "overgrown" "seasonal" 

so the initial road_class that we used to have in the deprecated fish_passage.closed_bottom_crossingscan be determined by joining the transport_line_type_description from bcfishpass (equivalent column is named simply description in the whse_basemapping.transport_line_type_code table) to whse_basemapping.transport_line table and grabbing road_class from whse_basemapping.transport_line_type_code table.

Big question here is since we are redoing this anyway, is there another way to do this that uses the columns we already have in bcfishpass. I would think so because it looks like road_class is derived from description of the transport_line_type_code. oh billy

[NewGraphEnvironment]

need to see if there are other columns in the workflow that we need. Basically we want to do road cost multiplier based on the following info

surface type road type -

• is it a logging road,
• unpaved local (small) road,
• paved small road,
• paved arterial road,
• paved highway

[NewGraphEnvironment]

potential workflow. Consider running in fpr and fpr_xref_rd_surface_cost or something like that and generalize

1. grab the description column of whse_basemapping.transport_line_type_code and mutate a cost multiplier column to build fpr_xref_rd_type_cost object
2. grab the description column of whse_basemapping.transport_line_surface_code and mutate a cost multiplier column to build tables like fpr_xref_rd_surface_cost object

[NewGraphEnvironment]

closing in favour of #63