Saturday, July 30, 2011

Connect GAMS to PostgreSQL-PostGIS (w/ sql2gms)

First things first, if you are running a 32bit PostgreSQL/PostGIS on a 64bit Windows box, you will need to read my previous blog post (how to configure psqlODBC) before moving on. Back? Ok, once you have your DSN setup you are ready to move onto the next step, testing your connection with sql2gms. I found the easiest way to test my connection was through the sql2gms GUI, located in the root directory of your GAMS install (i.e. c:\Program Files\GAMS\23.1\...). Once there...

Click the Options button to set up your connection

Enter your user name and password. If the connections string is not present, Select the ODBC Data Source/Driver to match mine below. The Connection String should auto populate, if not, replicate the input below. Test the connection, it should connect then disconnect (as seen below). Success! Click ok

Now do a test query. Success!
Now that you know your connection works you can program away. The sql2gms documentation via GAMS/help is quite good.

Below you'll see the standard GAMS tutorial transportation problem, but with sql2gms modifications. Q1 represents the 'Spatially Enabled' part. Q1 is Parameter 'd', and ST_Distance() is used to calculate the distance between the cities. Q2 is Parameter i, etc. 

*Create cmd text to execute multiple queries
$onecho > cmd.txt
C="DSN=PostgreSQL35W"
X="all_data.gdx"

Q1="SELECT b.city_name AS plants, a.city_name AS markets, CAST(ST_Distance(a.the_geom, b.the_geom) / 1609.344 * 0.001 AS numeric(8,2)) AS dist FROM (SELECT city_name, the_geom FROM gams.city_comp WHERE city_type = 'market') a, (SELECT city_name, the_geom FROM gams.city_comp WHERE city_type = 'plant' ) b WHERE a.city_name <> b.city_name"
A1=d

Q2="SELECT city_name FROM gams.city_comp WHERE city_type = 'plant';"
S2=i

Q3="SELECT city_name FROM gams.city_comp WHERE city_type = 'market';"
S3=j

Q4="SELECT city_name, capacity FROM gams.city_comp WHERE city_type = 'plant'"
A4=a

Q5="SELECT city_name, demand FROM gams.city_comp WHERE city_type = 'market'"
A5=b
$offecho

*Execute query
$call =sql2gms @cmd.txt
$call =gdxviewer all_data.gdx

*Create sets and Parameters
SETS
        i canning plants
        j markets ;

Parameters
        a(i)   capacity of plant i in cases
        b(j)   demand at market j in cases
        d(i,j) distance between plants and markets ;

*Load all the data from the gdx
$gdxin all_data.gdx
$load i j d a b
$gdxin

Scalar f freight in dollars per case per thousand miles /90/ ;

Parameter c(i,j) transport cost in thousands of dollars per case ;

        c(i,j) = f * d(i,j) / 1000 ;

Variables
        x(i,j) shipment quantities in cases
        z total transportation costs in thousands of dollars ;

Positive Variable x ;

Equations
        cost define objective function
        supply(i) observe supply limit at plant i
        demand(j) satisfy demand at market j ;

*Constraints
supply(i) .. sum(j, x(i,j)) =l= a(i) ;
demand(j) .. sum(i, x(i,j)) =g= b(j) ;

*objective
cost .. z =e= sum((i,j), c(i,j) * x(i,j)) ;

Model transport /all/ ;

Solve transport using lp minimizing z ;

Display x.l, x.m ; 

Friday, July 29, 2011

Configure 32bit psqlODBC on 64bit Windows Server 2008

Installing and configuring psqlODBC was not as easy as I had anticipated. I guess, I was amazed not many other folks were having the same issues I had. In the end, when all the pieces are in one place, its really not that difficult. As the title said, this post walks you through the steps for installing 32bit psqlODBC on a 64bit Windows Server 2008. Why you ask install 32bit on a 64bit? Simply because postGIS is 32bit only.

  • Install psqlODBC 
    • Unfortunately I kept getting errors using the Stack Builder so I went to the source
  • Add ODBC to Data Source Administrator (tricky part)
    • You cannot add a 32bit driver using the 64bit Data Source Admin
    • To activate 32bit Data Source Admin:
      •  Go to Start
      • Run
      • type: %WINDIR%\SysWOW64\odbcad32.exe
    • Add System DSN, select PostgreSQL Unicode and enter the server parameters
Now, that wasn't so bad now was it?