Navigating the LCA database using SQL

Question

Hi all,

I'm trying to get a table of the carbon/water footprints for all the elements in the European Commission's Product Environmental Footprint database, so I can use them in some downstream analyses. I've tried using the built-in SQL tool, and also opening the .zolca file as a Derby database. I found the built-in tool next to useless, since I had to guess blindly at the table names and column names; I've had a little better success navigating through Derby, but since all of the examined products or compounds are linked to their components and by products using anonymized IDs, I've still not be able to find the correct join to get "beef (organic) | 2 kg-CO2/kg | 30 L-H2O/kg" or similar.

I know the data are in the db. I feel like I'm missing something easy. Can someone offer me some insight??

Answer 1

Some hints for others that may end up here, to shorten your journey a bit.

To get all table names in the database, use this statement:

SELECT tablename FROM sys.systables;

Get all datasets from table "tbl_processes":

SELECT * from tbl_processes;

Some hints for the table structure (I'm not affiliated to greenDelta, this information is based on what I found by looking at the tables. No garantuee that any of this is accurate. Tested on openLCA 1.10):

tbl_exchanges links parent flows to the in and out flows of a process:

F_OWNER refers to ID in tbl_processes
F_FLOW refers to ID in tbl_flows
IS_INPUT 1 are inputs, 0 are outputs
F_UNIT refers to ID in tbl_units
RESULTING_AMOUNT_VALUE ist the amount of the in or output

Answer 2

tbl_flows will give you the name and id to use to join to the flow properties, which are contained in several different tables. I find it easier to access the data through the api, the ipc is the easiest.  The client will do all the joins for you and deliver the flows with all of their elements as a dict.

Sorry, not a direct answer to your question, but hope this helps.

Answer 3

next to useless

thanks. But indeed, we may remove the window.

We thought it as an option for those who understand SQL and find it convenient at times ourselves. We do not provide a documentation for this, and thus users who have not heard of systables asf. in derby and / or do not understand what tbl_processes and tbl_flows (e.g.) might contain will be lost, I understand. The IDs are not "anonymised" but just integer to speed up the read and write procedures.

You are only allowed to use the PEF database for work on a PEF study btw.

Comments

I'd hate to see the SQL window go away. It's very helpful in doing mass replacements (among other things) and is also a bit more portable than using the python IPC.