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

[Noli Sicad]

Hello Xypron,

This is how AMPL table PIVOT the result in OUT.


{p in PRODUCT: Status[p] in YIELD} <sum {(t,i,j) in HARVEST}
Y[t,i,j]*Yield[p,i,j] ~ (p)>;

In
~~~~~
table tblWoodflowSummary OUT "ODBC" "Otago.mdb" "tblWoodflowSummary":

        {t in PERIOD} -> [Period],

        sum {(t,i,j) in HARVEST} Y[t,i,j] ~ ClearfellArea,

        {p in PRODUCT: Status[p] in YIELD} <sum {(t,i,j) in HARVEST}
Y[t,i,j]*Yield[p,i,j] ~ (p)>;

write table tblWoodflowSummary;


~~~~~

Again, I don't know how we can translate this in MathProg.

Regards, Noli


On 9/18/10, Noli Sicad <address@hidden> wrote:
> 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
>