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

[Noli Sicad]

Hello Xypron,

Unpivot/Uncrosstab in PostgreSQL - Hack
https://cgsrv1.arrc.csiro.au/blog/2010/05/14/unpivotuncrosstab-in-postgresql/

Function for UNPIVOT
http://www.texastoo.com/post/2008/12/02/TSQL-UNPIVOT.aspx

I think it would be good to have some help functions in Tables e.g.
Transpose/Pivot and Untranspose/Unpivot.

I think this is very useful since all the time we get results in
glpk/mathprog in untranspose form and we have to transpose the results
so we can use graphing software e.g. gnuplot, R to visualise the
result.

Thanks.

Noli






On 9/17/10, Noli Sicad <address@hidden> wrote:
> Hello Xypron,
>
> Thank you very much the workaround using alias names for the columns.
> It works great.
>
> I also patched glpk-4.44 (Ubunut 10.04). I just copy your src folder
> and then configure, make clean, make and then make install.
>
> It is working now with multilines SQL queries.
>
> I have another problem - Unpivot / uncrosstab table as input. This is
> opposite to have I have asked you before - transposing data / pivot.
>
> Sqlite does support not have unpivot / uncrosstab and crosstab/transpose.
>
> I am working on AMPL model  which I am trying to translate to MathProg
> using Sqlite. It would useful for farmers and foresters for forest
> management and carbon sequestration modelling.
>
> Here is the problem.
>
> The AMPL model reads data using this script:
>
> ~~~~~~~
> printf "Reading Yields....\n";
>
> table qryData IN "ODBC" "Otago.mdb" : [i ~ Croptype, j ~ Age], {p in
> PRODUCT: Type[p] <> 'Residue'} <Yield[p,i,AgeToAgeClass[j]] ~ (p)>;
>
> read table qryData;
>
> ~~~~~~~~
>
> It seems that AMPL table has algorithm to unpivot / de-normalised data
>
> (This is unpivot concept in Oracle 11g
> http://www.sqlsnippets.com/en/topic-12698.html
>
> I think MySQL and MS SQL support these features  )
>
> Here are the tables.
>
>
> CREATE TABLE tblData
>  (
>       Croptype        varchar (32),
>       Age                     int,
>       Area            float,
>       TRV                     int,
>       SCOST                   money,
>       PCOST                   money,
>       P1P2                    float,
>       SLOG                    float,
>       PULP                    float,
>       CFREV                   money,
>       LCOST                   money,
>       TCOST                   money,
>       SACOST                  money,
>       RCOST                   money
>       
>       )
>
>       
>
> tblProduct
>
> ProductID     Product Type    Status  ProductName
> 14                    CFREV   Final   Revenue Clearfell Revenue
> 21                    LCOST   Final   Cost    Harvesting Cost
> 3                     P1P2    Final   Yield       Pruned
> 2                     PCOST   Intermediate    Cost    Pruning Costs
> 13                    PULP    Final   Yield     Pulp
> 24                    RCOST   Final   Cost    Roading Cost
> 23                    SACOST  Final   Cost    Cost
> 1                     SCOST   Intermediate    Cost    Silvilcutural Costs
> 4                     SLOG    Final   Yield       Sawlog
> 22                    TCOST   Final   Cost    Transport Cost
> 0                     TRV     Final   Yield   Total Recoverable Volume
>
>
> tblData
>
> Croptype      Age     Area    TRV     SCOST   PCOST   P1P2    SLOG    PULP    
> CFREV   LCOST   TCOST   SACOST  RCOST
> OTD   1       0       12      842     0       0       7       5       482     
> 187     0       20      75
> OTD   2       111.2   25      0       0       0       15      10      1005    
> 373     0       40      149
> OTD   3       483.5   39      0       0       0       23      16      1568    
> 577     0       63      156
> OTD   4       467.4   53      0       0       0       32      21      2131    
> 784     0       86      163
> OTD   5       407.5   67      0       0       0       40      27      2693    
> 987     0       108     169
> OTD   6       177.2   81      0       0       0       49      32      3256    
> 1194    0       131     176
> OTD   7       163     95      0       0       0       57      38      3819    
> 1401    0       154     183
> OTD   8       574     109     0       0       0       65      44      4382    
> 1604    0       177     189
> OTD   9       384.1   123     0       0       0       74      49      4945    
> 1811    0       200     196
>
>
> The wanted outcome,  Yield table
>
> Croptype Age Product Yield
> OTD           1       TRV      12
> OTD           2       TRV      25
> OTD           3       TRV      39
> OTD           4       TRV      53
> OTD           5       TRV      67
> OTD           6       TRV      81
> OTD           7       TRV      95
> OTD           8       TRV      109
> OTD           9       TRV      123
> OTD           10      TRV      137
> OTD           1       SCOST   842
> OTD           2       SCOST   0
> OOTD  3       SCOST   0
> OTD           4       SCOST   0
> OOTD  5       SCOST   0
> ..
>
> Now, how do we implement this in MatProg, I don't mind if we write the
> tables in csv text file first then write the csv file to create the
> tables.
>
>
> Again, here's AMPL table implementation using MS access db.
>
>
>
> printf "Reading Yields....\n";
>
> table tblData IN "ODBC" "Otago.mdb" : [i ~ Croptype, j ~ Age], {p in
> PRODUCT: Type[p] <> 'Residue'} <Yield[p,i,AgeToAgeClass[j]] ~ (p)>;
>
> read table tblData;
>
>
>
> Now, how do we do this part?
>
>
> set S3, dimen 3;
>
> table tab IN "ODBC" 'Driver=SQLITE3;Database=Otago_p.sqlite;'
>
>
>
>
> S3 <- [Product,Croptype, Age], Yield ~ Yield;
>
> Thanks again.
>
> Regards.
>
> Noli
>