Which waste row to use when the route is unknown

Join activity_classification on the waste_stream scheme to see every treatment route a publisher gives for one material, then take the row where is_route_default is true and read route_default_rule in the same breath. For DESNZ paper and board the elected row is landfill at 1.1645 kg CO2e per kg, elected by highest_route, which makes it the stream's maximum and not its average.

List the routes of one stream#

Level 1 of the waste_stream scheme is the publisher's own material label, and level 2 is the row's role: route for one treatment, publisher_mix for the publisher's own average, excluded for a scenario that answers a narrower question.

SELECT coalesce(f.sub_category, 'no route stated') AS route,
       r.code AS role,
       round(f.value_co2e_normalized::numeric, 4) AS kg_co2e_per_kg,
       f.is_route_default,
       f.route_default_rule,
       f.slug
FROM open_ef.factors_flat f
JOIN open_ef.activity_classification s
  ON s.factor_id = f.factor_id AND s.scheme = 'waste_stream' AND s.level = 1
JOIN open_ef.activity_classification r
  ON r.factor_id = f.factor_id AND r.scheme = 'waste_stream' AND r.level = 2
WHERE f.library = 'desnz'
  AND s.code = 'Paper > Paper and board: paper'
  AND f.is_default_indicator
  AND f.is_latest_activity_year
ORDER BY f.is_route_default DESC, 3 DESC

To find the stream code for another material, drop the s.code predicate and select s.code for the activity you already have. One stream is one publisher, one material, one unit and one region, and exactly one of its rows carries the flag.

Read the rule, not just the flag#

route_default_rule is NULL on every row that was not elected, and on the elected row it says what kind of number you are holding.

Waste treatment routes and the elected default explains how each rule is decided and which rows can never win.

Never filter on the flag alone#

The flag reaches the view through a join that filters nothing, so it is false, not NULL, on every factor that is not an elected waste row. Adding AND is_route_default to a general query looks harmless and silently throws the rest of factor search away.

SELECT f.library,
       count(DISTINCT f.factor_id) AS factors,
       count(DISTINCT f.factor_id) FILTER (WHERE f.is_route_default) AS route_defaults,
       count(*) FILTER (WHERE f.is_route_default IS NULL) AS nulls
FROM open_ef.factors_flat f
WHERE f.library IN ('ademe','agribalyse','desnz','epa')
  AND f.is_default_indicator
  AND f.is_latest_activity_year
  AND f.is_top_parent
GROUP BY 1
ORDER BY 1

An entire food library comes back with nothing: AGRIBALYSE fans no waste material out by treatment route, so it has no stream and nothing to elect from. The three that do keep a sliver of themselves, 40 rows of ADEME's 2,767, 42 of DESNZ's 1,132 and 61 of EPA's 360. Keep the flag for the last step of a waste query, after you have already narrowed to the stream.

Query the route you know#

When the line does say what happened to the material, ignore the election and ask for that route. The route is the classification sub-category, under category_code = 'waste'.

SELECT coalesce(f.sub_category_code, 'not stated') AS route_code,
       count(DISTINCT f.factor_id) FILTER (WHERE f.library = 'ademe') AS ademe,
       count(DISTINCT f.factor_id) FILTER (WHERE f.library = 'desnz') AS desnz,
       count(DISTINCT f.factor_id) FILTER (WHERE f.library = 'epa') AS epa
FROM open_ef.factors_flat f
WHERE f.library IN ('ademe','desnz','epa')
  AND f.category_code = 'waste'
  AND f.is_default_indicator
  AND f.is_latest_activity_year
  AND f.is_top_parent
GROUP BY 1
ORDER BY 1

Add AND f.sub_category_code = 'landfill' to the query above and you have the landfill rows only.

The 57 ADEME rows that state no route name a material rather than a treatment, and the stream role says which kind each one is.

SELECT r.code AS role,
       f.system_boundary,
       count(DISTINCT f.factor_id) AS factors
FROM open_ef.factors_flat f
JOIN open_ef.activity_classification r
  ON r.factor_id = f.factor_id AND r.scheme = 'waste_stream' AND r.level = 2
WHERE f.library = 'ademe'
  AND f.category_code = 'waste'
  AND f.sub_category_code IS NULL
  AND f.is_default_indicator
  AND f.is_latest_activity_year
  AND f.is_top_parent
GROUP BY 1, 2
ORDER BY 3 DESC

Fifty are a publisher's average end-of-life row for the material, the kind publisher_mix elects from, and seven are an average with recycling taken out. All 57 are end_of_life. ADEME's negative avoided-credit rows are not among them: they are reported beside the treatment row they credit rather than standing alone, so is_top_parent keeps them out of the counts in this section. Drop that filter and they come back, under the same stream and the same roles.

Watch out#

Watch out. Use a LEFT JOIN when you attach the election to a list of waste factors. A stream whose rows are all ineligible has no elected row at all, and an inner join drops those lines without a word. No public stream is in that state today, but the model allows it.

Watch out. Log the assumption. When you take the elected row because the route is unknown, record the slug you used and the rule that chose it. A highest_route number is defensible as a conservative estimate and indefensible as a measurement, and a later release can move the flag to a different row of the same stream.

Note. Neither column is an API filter, an export column or a factor search facet. This is a SQL-only route into the data; on the portal you pick the route row yourself.

Related#

• Waste treatment routes and the elected default
• Code lists for the waste_stream scheme and the waste sub-categories
• Rolling up by a classification code
• Choosing between similar factors