Re: [Help-glpk] SQL query as input table - MathProg

[Noli Sicad]

Hi Xypron,

You are right that we don't need these pivot and unpivot in mathprog.

I manage to do the unpivot in SQL using UNION all. However, the MS
Access database that comes with the AMPL LP model is really bad poorly
designed. The UNION all lead to many duplicates. I just hack the data
using the MS Access queiry results and import it into my Sqlite
database.

This is union all to unpivot data. The pivot data using SQL commands
can be easily found in the net.
~~~~~~~~~~~~~
set S3, dimen 3;
table tab IN "ODBC" 'Driver=SQLITE3;Database=Otago_p.sqlite;'
'Select Croptype, Age, TRV as Product, TRV as Yield from tblData union all'
'select Croptype, Age, SCOST as Product, SCOST as Yield from tblData union all'
'select Croptype, Age, PCOST as Product, PCOST as Yield from tblData union all'
'select Croptype, Age, P1P2 as Product, P1P2 as Yield from tblData union all'
'select Croptype, Age, SLOG as Product, SLOG as Yield from tblData union all'
'select Croptype, Age, PULP as Product, PULP as Yield from tblData union all'
'select Croptype, Age, CFREV as Product, CFREV as Yield from tblData union all'
'select Croptype, Age, LCOST as Product, LCOST as Yield from tblData union all'
'select Croptype, Age, TCOST as Product, TCOST as Yield from tblData union all'
'select Croptype, Age, SACOST as Product, SACOST as Yield from tblData
union all'
'select Croptype, Age, RCOST as Product, RCOST as Yield from tblData'
'ORDER BY Croptype, Age, Product;' :
S3 <- [Product, Croptype, Age], Yield;
display Yield;
~~~~~~~~~~~

I found out as well that the Sqlite3.7.2 odbc driver could not handled
ORDER keyword.

I just produce the tablets and use simple select statement at the end.

However, the JOIN is quite good if we are just dealing with 2 tables,
3 tables join is problematic.

The model is running now.

> Implementing an UNPIVOT in the glpk library would definitely be possible
> but requires a redesign of how table statements are parsed. I would
> guess it needs two work days plus documentation and testing.
>
> If you think it is worth the investment, it would be helpful if you could
> provide a clear definition of the syntax as a basis for a discussion
> on this list.

This is only syntax i.e. AMPL syntax for unpivot that I can find out.

table tblData IN "ODBC" "Otago.mdb" : [i ~ Croptype, j ~ Age], {p in
PRODUCT: Type[p] <> 'Residue'} <Yield[p,i,AgeToAgeClass[j]] ~ (p)>;

In MathProg, I don't really know how, the above syntax can be translated.

set S3, dimen 3;

table tab IN "ODBC" 'Driver=SQLITE3;Database=Otago_p.sqlite;'

'SELECT * FROM tblData':

p in PRODUCT: Type[p] <> 'Residue
S3 <- [Product, Croptype, Age], Yield;

display Yield;

I think PIVOT would be good in the output table as well. For example,
if you are running LP models with time period i.e. 1 to 100 years. You
can get the pivot results per period.

Thanks.

Regards, Noli

Here is the results of my LP run.

Generating MaxYield...
Generating MaxNPV...
Generating Area_Must_Be_Replanted...
Generating Area_Must_Be_Harvested...
Generating Initial_Area_Must_Be_Harvested...
Generating Clearfell_Material_Balance...
Generating Mill_Demand...
Model has been successfully generated
GLPK Simplex Optimizer, v4.44
2785 rows, 13596 columns, 133320 non-zeros
Preprocessing...
1389 rows, 11853 columns, 37464 non-zeros
Scaling...
 A: min|aij| =  1.000e+00  max|aij| =  6.970e+02  ratio =  6.970e+02
GM: min|aij| =  2.020e-01  max|aij| =  4.951e+00  ratio =  2.451e+01
EQ: min|aij| =  4.079e-02  max|aij| =  1.000e+00  ratio =  2.451e+01
Constructing initial basis...
Size of triangular part = 1389
      0: obj =   0.000000000e+00  infeas =  4.282e+05 (0)
    500: obj =   1.783254235e+08  infeas =  4.783e+04 (0)
*   803: obj =   2.525432392e+08  infeas =  9.905e-14 (0)
*  1000: obj =   4.249531265e+08  infeas =  2.697e-13 (0)
*  1500: obj =   4.710136614e+08  infeas =  0.000e+00 (0)
*  1909: obj =   4.812551883e+08  infeas =  0.000e+00 (0)
OPTIMAL SOLUTION FOUND
Time used:   1.2 secs
Memory used: 38.8 Mb (40714186 bytes)
>Exit code: 0    Time: 405.727