Capturing the zero flow issue for openLCA libraries

[dt-woods]

As brought up in the Fed LCA Commons collab meeting, zero values for quantitative reference flows (e.g., products and wastes) lead to non-invertable metrics in openLCA libraries when calculations are run.

The following was tested in SQL on the 2016 electricity baseline. The goal is to convert this method to Python and implement it in the post-processing steps of electricityLCI's main.main method.

SELECT tf.name as flow_name, tf.flow_type as flow_type, tp.name as process_name, te.*
FROM tbl_exchanges as te
JOIN tbl_flows as tf on te.f_flow = tf.id
JOIN tbl_processes as tp on te.f_owner = tp.id
WHERE te.resulting_amount_value = 0 and tf.flow_type <> 'ELEMENTARY_FLOW';

-- This can be easily modified to remove them all:
DELETE FROM tbl_exchanges as te 
WHERE te.id in (
SELECT te.id FROM tbl_exchanges as te
JOIN tbl_flows as tf on te.f_flow = tf.id
JOIN tbl_processes as tp on te.f_owner = tp.id
WHERE te.resulting_amount_value = 0 and tf.flow_type <> 'ELEMENTARY_FLOW'
);

[dt-woods]

~~@m-jamieson, from what I can tell, you don't need the two JOIN statements in the second query, right? They were added in the first query just for the return table from the SELECT statement.~~ NM. I see that flow table holds the required "flow_type" property used for the comparison.

[dt-woods]

I know internal IDs on exchanges are just a clerical thing, but do we know if there any concern if the exchange list IDs are non-consecutive? Cause this is gonna punch a lot of holes.

[m-jamieson]

@m-jamieson, from what I can tell, you don't need the two JOIN statements in the second query, right? They were added in the first query just for the return table from the SELECT statement.

Yes, you're right.

[m-jamieson]

I know internal IDs on exchanges are just a clerical thing, but do we know if there any concern if the exchange list IDs are non-consecutive? Cause this is gonna punch a lot of holes.

You're talking about within an existing database? So internal id 3 gets deleted, leaving the sequences as 1,2,4? Probably more a question for the openLCA guys. I don't think they're used as a reference at all - don't know how it would cause problems. Those feel like famous last words though.

[bl-young]

So internal id 3 gets deleted, leaving the sequences as 1,2,4? Probably more a question for the openLCA guys. I don't think they're used as a reference at all - don't know how it would cause problems. Those feel like famous last words though.

I seem to recall that this sort of thing could cause a validation error when you validate a database. @pweilerERG do you recall if that's the case?

[pweilerERG]

So internal id 3 gets deleted, leaving the sequences as 1,2,4? Probably more a question for the openLCA guys. I don't think they're used as a reference at all - don't know how it would cause problems. Those feel like famous last words though.

I seem to recall that this sort of thing could cause a validation error when you validate a database. @pweilerERG do you recall if that's the case?

Yes - non-consecutive internalids will cause validation errors in the database. This should work to reset them -

from java.util import Date dao = ProcessDao(db) for d in dao.getDescriptors(): p = dao.getForId(d.id) p.lastInternalId = 0 for e in p.exchanges: e.internalId = p.lastInternalId + 1 p.lastInternalId += 1 v = Version(p.version) v.incUpdate() p.version = v.getValue() p.lastChange = Date().getTime() dao.update(p)