Hi, I managed to implement transportation model sql (i.e. transp_odbc.mod in ..glpk-4.4.2/examples/sql) using ODBC in Access, Excel and SQLite and compare the results to the original transp.mod found in the ../glpk-4.42/examples folder).
Result of the original transp.mod Variables;result x[Seattle,New-York] = 50 x[Seattle,Chicago] =300 x[Seattle,Topeka] = 0 x[San-Diego,New-York] = 275 x[San-Diego,Chicago] = 0 x[San-Diego,Topeka]= 275 Access and Excel results (transp_odbc_mdb.mod and transp_odbc_xls.mod) LOC1 LOC2 QUANTITY Seattle Chicago 300.00 San Diego New York 325.00 San Diego Topeka 275.00 SQLite result (transp_odbc_sqlite3.mod) LOC1 LOC2 QUANTITY Seattle New York 50 Seattle Chicago 300 San Diego New York 275 San Diego Topeka 275 As you can see in the results, Access and Excel results are different to the original transp.mod model but SQLite result is exactly the same as the original transp.mod. Reason for this variation of results in Access and Excel (I thought) probably Microsoft ODBC drivers are not complaint to the SQL 92 standard (http://msdn.microsoft.com/en-us/library/ms711756%28VS.85%29.aspx). No 2 primary keys are allowed in Access tables just an example. The Excel ODBC driver does not support the use of SQL statements DELETE, UPDATE, or ALTER ( http://www.stata.com/support/faqs/data/odbc_excel.html) I suspected that the matrix formulation of the access and excel models are entirely different from that of the original transp.mod model and SQLite model (i.e. transp_odbc_sqlite3.mod) because SQL querying by Microsoft ODBC drivers for Access and Excel. However, when converted the 3 models into CPLEX format, the 3 models are exactly the same, just ordering of the variables and constraints (see converted models and output of the models runs below). Anyway, BEWARE of using Access and Excel using OBDC (SQL statement) in MathProg. I recommend using SQLite OBDC in MathProg (FYI, Android and Iphone use SQLite :-)). Anybody got opinion why Access and Excel model yielded different results? Regards, Noli ~~~~~ \* Problem: transp_odbc_sqlite3 *\ Minimize cost: + 0.225 x(Seattle,'New_York') + 0.153 x(Seattle,Chicago) + 0.162 x(Seattle,Topeka) + 0.225 x('San_Diego','New_York') + 0.162 x('San_Diego',Chicago) + 0.126 x('San_Diego',Topeka) Subject To supply(Seattle): + x(Seattle,'New_York') + x(Seattle,Chicago) + x(Seattle,Topeka) <= 350 supply('San_Diego'): + x('San_Diego','New_York') + x('San_Diego',Chicago) + x('San_Diego',Topeka) <= 600 demand('New_York'): + x(Seattle,'New_York') + x('San_Diego','New_York') >= 325 demand(Chicago): + x(Seattle,Chicago) + x('San_Diego',Chicago) >= 300 demand(Topeka): + x(Seattle,Topeka) + x('San_Diego',Topeka) >= 275 \* Problem: transp_odbc_xls *\ Minimize cost: + 0.225 x('San_Diego',Chicago) + 0.162 x('San_Diego','New_York') + 0.126 x('San_Diego',Topeka) + 0.153 x(Seattle,Chicago) + 0.225 x(Seattle,'New_York') + 0.162 x(Seattle,Topeka) Subject To supply('San_Diego'): + x('San_Diego',Chicago) + x('San_Diego','New_York') + x('San_Diego',Topeka) <= 600 supply(Seattle): + x(Seattle,Chicago) + x(Seattle,'New_York') + x(Seattle,Topeka) <= 350 demand(Chicago): + x('San_Diego',Chicago) + x(Seattle,Chicago) >= 300 demand('New_York'): + x('San_Diego','New_York') + x(Seattle,'New_York') >= 325 demand(Topeka): + x('San_Diego',Topeka) + x(Seattle,Topeka) >= 275 \* Problem: transp_odbc_mdb *\ Minimize cost: + 0.225 x(Seattle,'New_York') + 0.153 x(Seattle,Chicago) + 0.162 x(Seattle,Topeka) + 0.162 x('San_Diego','New_York') + 0.225 x('San_Diego',Chicago) + 0.126 x('San_Diego',Topeka) Subject To supply(Seattle): + x(Seattle,'New_York') + x(Seattle,Chicago) + x(Seattle,Topeka) <= 350 supply('San_Diego'): + x('San_Diego','New_York') + x('San_Diego',Chicago) + x('San_Diego',Topeka) <= 600 demand('New_York'): + x(Seattle,'New_York') + x('San_Diego','New_York') >= 325 demand(Chicago): + x(Seattle,Chicago) + x('San_Diego',Chicago) >= 300 demand(Topeka): + x(Seattle,Topeka) + x('San_Diego',Topeka) >= 275 ~~~~~~~ Running transp_odbc_sqlite3.mod >C:\gusek_0-2-9\gusek\glpsol.exe --cover --clique --gomory --mir -m >"transp_odbc_sqlite3.mod" GLPSOL: GLPK LP/MIP Solver, v4.42 Parameter(s) specified in the command line: --cover --clique --gomory --mir -m transp_odbc_sqlite3.mod Reading model section from transp_odbc_sqlite3.mod... Reading data section from transp_odbc_sqlite3.mod... 104 lines were read Reading plants... Connected to SQLite 3.6.22 - C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp.db3 SELECT PLANT, CAPA AS CAPACITY FROM transp_capa Reading markets... Connected to SQLite 3.6.22 - C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp.db3 SELECT MARKET, DEMAND FROM transp_demand Display statement at line 61 b['New York'] = 325 b[Chicago] = 300 b[Topeka] = 275 Reading dist... Connected to SQLite 3.6.22 - C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp.db3 SELECT LOC1, LOC2, DIST FROM transp_dist Display statement at line 72 d[Seattle,'New York'] = 2.5 d[Seattle,Chicago] = 1.7 d[Seattle,Topeka] = 1.8 d['San Diego','New York'] = 2.5 d['San Diego',Chicago] = 1.8 d['San Diego',Topeka] = 1.4 Generating cost... Generating supply... Generating demand... Model has been successfully generated GLPK Simplex Optimizer, v4.42 6 rows, 6 columns, 18 non-zeros Preprocessing... 5 rows, 6 columns, 12 non-zeros Scaling... A: min|aij| = 1.000e+000 max|aij| = 1.000e+000 ratio = 1.000e+000 Problem data seem to be well scaled Constructing initial basis... Size of triangular part = 5 0: obj = 0.000000000e+000 infeas = 9.000e+002 (0) * 4: obj = 1.561500000e+002 infeas = 0.000e+000 (0) * 5: obj = 1.536750000e+002 infeas = 0.000e+000 (0) OPTIMAL SOLUTION FOUND Time used: 0.0 secs Memory used: 0.1 Mb (133563 bytes) Writing result... Connected to SQLite 3.6.22 - C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp.db3 DELETE FROM transp_result INSERT INTO transp_result VALUES (?,?,?) Model has been successfully processed >Exit code: 0 Time: 0.210 Running the transp_odbc_xls.mod >C:\gusek_0-2-9\gusek\glpsol.exe --cover --clique --gomory --mir -m >"transp_odbc_xls.mod" GLPSOL: GLPK LP/MIP Solver, v4.42 Parameter(s) specified in the command line: --cover --clique --gomory --mir -m transp_odbc_xls.mod Reading model section from transp_odbc_xls.mod... Reading data section from transp_odbc_xls.mod... 96 lines were read Reading plants... Connected to EXCEL 08.00.0000 - C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp SELECT PLANT, CAPA AS CAPACITY FROM [transp_capa$] Reading markets... Connected to EXCEL 08.00.0000 - C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp SELECT * FROM [transp_demand$] Display statement at line 55 b[Chicago] = 300 b['New York'] = 325 b[Topeka] = 275 Reading dist... Connected to EXCEL 08.00.0000 - C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp SELECT * FROM [transp_dist$] Display statement at line 64 d[Seattle,'New York'] = 2.5 d[Seattle,Chicago] = 1.7 d[Seattle,Topeka] = 1.8 d['San Diego',Chicago] = 2.5 d['San Diego','New York'] = 1.8 d['San Diego',Topeka] = 1.4 Generating cost... Generating supply... Generating demand... Model has been successfully generated GLPK Simplex Optimizer, v4.42 6 rows, 6 columns, 18 non-zeros Preprocessing... 5 rows, 6 columns, 12 non-zeros Scaling... A: min|aij| = 1.000e+000 max|aij| = 1.000e+000 ratio = 1.000e+000 Problem data seem to be well scaled Constructing initial basis... Size of triangular part = 5 0: obj = 0.000000000e+000 infeas = 9.000e+002 (0) * 4: obj = 1.662750000e+002 infeas = 0.000e+000 (0) * 7: obj = 1.332000000e+002 infeas = 0.000e+000 (0) OPTIMAL SOLUTION FOUND Time used: 0.0 secs Memory used: 0.1 Mb (133559 bytes) Writing result... Connected to EXCEL 08.00.0000 - C:\Down2\glpk-4.42\glpk-4.42\examples\sql\transp INSERT INTO [transp_result$] VALUES (?,?,?) Model has been successfully processed >Exit code: 0 Time: 0.311 _______________________________________________ Help-glpk mailing list [email protected] http://lists.gnu.org/mailman/listinfo/help-glpk
