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

[Noli Sicad]

Hello Xypron,

If you are going to restructure the Table implementation in MathProg,
I worth looking how to improve the Table OUT as well. Right now, we
can only do like this,
~~~~~~~
table result{g in G_STAND_TYPE, i in I_CULTURAL_PRES, j in J_MGT_YEAR:
 XForestLand[g,i,j]} OUT "ODBC"
'Driver=SQLITE3;Database=forestlp.sqlite;'
'DELETE FROM VarRes;'
'INSERT INTO VarRes VALUES (?,?,?,?)':
g ~ STAND, i ~ CUL_PRES, j ~ MGT_YEAR,  XForestLand[g,i,j] ~ RESULTS;
~~~~~~~~


While AMPL it can do these commands,


~~~~~~~
printf "Solving Model....\n";

solve;



printf "Writing Woodflow Summary Table ....\n";

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;



printf "Writing Demand Summary ....\n";

table tblDemandSummary OUT "ODBC" "Otago.mdb" "tblDemandSummary":

        {dmd in DEMAND} -> [DemandID],

        MinPeriod[dmd] ~ MinPeriod,

        MaxPeriod[dmd] ~ MaxPeriod,

        DemandDest[dmd] ~ Destination,

        Demand[dmd] ~ Demand,

        Shortfall[dmd] ~ Shortfall, Surplus[dmd] ~ Surplus;

write table tblDemandSummary;



printf "Writing Harvest Variables ...\n";

table tblHarvestVariables OUT "ODBC" "Otago.mdb" "tblHarvestVariables":

   {(t,i,j) in HARVEST: Y[t,i,j] > epsilon} -> [Period, Croptype, AgeClass],

   AgeClassToAge[j] ~ Age,

   Y[t,i,j] ~ Area,

   if (isCurrent[t,j]) then Y[t,i,j] else 0 ~ Current,

   if (isFuture[t,j]) then Y[t,i,j] else 0 ~ Future;

write table tblHarvestVariables;



printf "Writing Replanting Variables ...\n";

table tblReplantingVariables OUT "ODBC" "Otago.mdb" "tblReplantingVariables":

   {(t,i,k) in REPLANT: R[t,i,k] > epsilon} -> [Period, Croptype, Replanting],

   R[t,i,k] ~ Area;

write table tblReplantingVariables;



printf "Writing Transfer Variables ...\n";

table tblTransferVariables OUT "ODBC" "Otago.mdb" "tblTransferVariables":

   {(i,j,k) in TRANSFER: Z[i,j,k] > epsilon} -> [Croptype, AgeClass, Transfer],

   AgeClassToAge[j] ~ Age,

   Z[i,j,k] ~ Area;

write table tblTransferVariables;



printf "Writing Slack Variables ...\n";



table tblRadiataShortfall OUT "ODBC" "Otago.mdb" "tblRadiataShortfall":

   {t in PERIOD: RadSlack[t] > epsilon} -> [Period], RadSlack[t] ~
RadiataShortfall;

write table tblRadiataShortfall;



table tblFirShortfall OUT "ODBC" "Otago.mdb" "tblFirShortfall":

   {t in PERIOD: FirSlack[t] > epsilon} -> [Period], FirSlack[t] ~ FirShortfall;

write table tblFirShortfall;



table tblHarvestPlanShortfall OUT "ODBC" "Otago.mdb" "tblHarvestPlanShortfall":

   {(t,i) in PLAN: HarvestPlanSlack[t,i] > epsilon} -> [t ~ Period, i
~ Croptype], HarvestPlanSlack[t,i] ~ AreaShortfall;

write table tblHarvestPlanShortfall;



printf "Writing Clearfell Woodflows & Cashflows ...\n";

table tblWoodflow OUT "ODBC" "Otago.mdb" "tblWoodflow":

   {(t,i,j) in HARVEST, p in PRODUCT: Type[p] in CLEARFELL and
Y[t,i,j] > epsilon and Yield[p,i,j] > epsilon} -> [Period, Croptype,
AgeClass, Product],

   Y[t,i,j] ~ Area,

   Yield[p,i,j] ~ Quantity,

   Y[t,i,j] * Yield[p,i,j] ~ Perpetual,

   if (isCurrent[t,j]) then Y[t,i,j] * Yield[p,i,j]     else 0 ~ Current,

   if (isFuture[t,j]) then Y[t,i,j] * Yield[p,i,j]      else 0 ~ Future;

write table tblWoodflow;



# Intermediate Products



printf "Writing Intermediate Woodflows & Cashflows ...\n";



table tblIntermediateWoodflow OUT "ODBC" "Otago.mdb" "tblIntermediateWoodflow":

   {t in PERIOD, i in CROPTYPE, j in AGECLASS, p in PRODUCT: Type[p]
in INTERMEDIATE and Yield[p,i,j] > epsilon

   and sum {(tn,i,jn) in HARVEST: tn >= t+1 and jn = j+tn-t}
Y[tn,i,jn] > epsilon}  -> [Period, Croptype, AgeClass, Product],

   sum {(tn,i,jn) in HARVEST: tn >= t+1 and jn = j+tn-t} Y[tn,i,jn]  ~ Area,

   Yield[p,i,j] ~ Quantity,

   sum {(tn,i,jn) in HARVEST: tn >= t+1 and jn = j+tn-t} Y[tn,i,jn] *
Yield[p,i,j] ~ Perpetual,

   if (isCurrent[t,j]) then sum {(tn,i,jn) in HARVEST: tn >= t+1 and
jn = j+tn-t} Y[tn,i,jn] * Yield[p,i,j] else 0 ~ Current,

   if (isFuture[t,j]) then sum {(tn,i,jn) in HARVEST: tn >= t+1 and jn
= j+tn-t} Y[tn,i,jn] * Yield[p,i,j]  else 0 ~ Future;

write table tblIntermediateWoodflow;  # append to tblWoodflow later



printf "Writing Allocation ...\n";



table tblAllocation OUT "ODBC" "Otago.mdb" "tblAllocation":

    {(t,spec,prd,orig,dest) in WOODFLOW: U[t,spec,prd,orig,dest] > epsilon}

            -> [Period, Species, Product, Origin, Destination],

                U[t,spec,prd,orig,dest] ~ Volume,

                U[t,spec,prd,orig,dest] * Transport[t,spec,prd,orig,dest] ~ 
Cartage,

                Transport[t,spec,prd,orig,dest] ~ UnitCartage,

                U[t,spec,prd,orig,dest] * Price[t,spec,prd,orig,dest] ~ Revenue,

                Price[t,spec,prd,orig,dest] ~ UnitRevenue;
~~~~~~~~~~

Thanks.

Regards, Noli

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