Query outputs
One table column is created for every stock in the Vensim model. Also delays are interpreted as stocks by translator resources implemented in PySD (Houghton & Siegel, 2015). This means delays are written as results to ‘sim_out’ just like ordinary stocks.
If a SDL is involved in the simulation, one row of type array (PostgreSQL type ‘real[]’) is created for every instance of a simulation run. The array index corresponds to the time step of the simulation (see Fig. 4, Left).
Fig. 4. Left: Output table 'sim_out', SDL involved; Right: Output table 'sim_out', TSL only
If only TSL are used in a simulation, one dataset row (of PostgreSQL type ‘numeric’) is created for every time step in the simulation (Fig. 4, Right).
Rows of input and output tables are related one-to-one. For this reason the primary key (‘gid’) of the input table is also copied as a foreign key to the output table (see Fig. 4). This is intended to simplify data querying.
To export results from PostgreSQL to a spreadsheet, select data to be exported by using an SQL statement like
SELECT * FROM sim_out WHERE gid <= 100;
e.g. to select the first 100 rows. Then go to File/Export on the menu bar. Output files (csv or dat) can be edited and visualized using conventional spreadsheet software (see Fig. 5).
Fig. 5. Sensitivity of teacher unemployment against variations in numbers of students
Provided spatial input data is involved, ‘sim_out’ will have a geometry column and can be exported as a shapefile. However, before the export, data needs to be reduced from three to two dimensions. In other words, a time slice of the space-time cube is extracted using the following SQL statements (see also Fig. 6):
ALTER TABLE sim_out ADD COLUMN time_snapshot double precision;
UPDATE sim_out SET time_snapshot = carbon_in_biomass[1000];
In this example, first a column named ‘time_snapshot’ is added to table ‘sim_out’. Then the new column is populated with simulation results of a table column ‘carbon_in_biomass’ at time step 1000. In this way time step 1000 is extracted to a new table column.
After that table 'sim_out' can be exported as shapefile using ‘pgsql2shp’.
Fig. 7. Export of a time slice as shapefile using pgsql12shp in the Windows command prompt
The example above demonstrates the export of the output table named ‘sim _out’ from database ‘Hyde’ which is located on a local machine with username ‘postgres’ and password ‘0420842’ to a shapefile named ‘out.shp’. Important: Run Windows Command Prompt as administrator!