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

[glpk xypron]

Hello Noli,

having a column for each product in a table is not a design I would
suggest because adding a new product requires changing the table
definition.

Furthermore having products in columns may lead to many empty fields.

If you are moving from one data base (MS Access) to another (SQLite)
it seems like a good time to redesign the data base scheme.

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.

The relevant coding is in glpmpl.h <struct TABLE>, glpmpl01.c <get_token()>,
and glpmpl03.c <execute_table(), clean_table()>.

Best regards

Xypron

-------- Original-Nachricht --------
> Datum: Fri, 17 Sep 2010 14:56:42 +1000
> CC: address@hidden
> Betreff: Re: [Help-glpk] SQL query as input table - MathProg

> 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
> >

-- 
GRATIS: Spider-Man 1-3 sowie 300 weitere Videos!
Jetzt freischalten! http://portal.gmx.net/de/go/maxdome