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

[Noli Sicad]

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