OpenJump for PostGIS Spatial Ad-Hoc Queries   

Installing

1. Install JRE 1.5 or above if you don't have it already.
2. You can then choose either the stable release version from http://sourceforge.net/project/showfiles.php?group_id=118054 or go with a nightly snapshot build. We tend to go with the nightly snapshot since there have been a lot of speed enhancements made that are not in the current production release.
3. For snapshot releases, no install is necessary - you can simply extract the zip and launch the openjump.bat (for Windows) or openjump.sh for Linux/Unix based to launch the program. The production release includes an installer for windows.
4. Note - OpenJump uses a Plug-In architecture. For our particular exercises, you won't need any plug-ins not in Core. Many plugins are not included in the core, so to get those download them from http://sourceforge.net/project/showfiles.php?group_id=118054. Details on how to install plugins is http://openjump.org/wiki/show/Installing+PlugIns Installing PlugIns
5. For those who don't know anything about PostGIS and have no clue how to load spatial data into PostgreSQL, please check out our Almost Idiot's Guide to PostGIS that demonstrates quickly installing and loading using Mass Town data as an example. Also check out our pgsql2shp and shp2pgsql cheat sheet for dumping and loading spatial data from PostgreSQL.

Connecting to a PostGIS enabled PostgreSQL database

1. Launch the bin/openjump.bat (for windows), bin/openjump.sh (for Linux)
2. On Menu go to Layer->Run Datastore Query -- your screen should look something like this
   
3. Next click the little database icon to the right of the connection drop down
4. Click "Add"
5. Fill in connection info and then click okay
   • Name field can have any name you want to give the connection
   • Driver PostGIS
   • Server - the hostname of the PostgreSQL Server
   • Port 5432 (or if you have a non-standard port whatever that is)
   • Instance This is the name of the database you want to connect to.
   • User, Password - The username and password of the PostgreSQL user you want to connect as.
   
   

   When you are done, your screen should look something like this.
   
   

Writing spatial queries and viewing them

Now we shall create a very trivial query that magically needs no real data. Simply select the connection you just made, and in the query box type the following. Keep in mind for Ad-hoc, open jump requires the format to be in Well-Known Binary (WKB), so force the geometry to WKB with ST_AsBinary():

SELECT ST_AsBinary(the_geom), fakepop
FROM (SELECT 
            ST_Transform(
                ST_SetSRID(
                    ST_MakePoint(-71 + x*random()*0.01,42 + y*0.1*random()),4326),2163) As the_geom,
            x*y As fakepop
        FROM generate_series(100,200,5) x 
            CROSS JOIN generate_series(1,20) y
        ) As foo;

Click okay and when you are done, you should have a breath-taking map that looks something like this and will change each time you run the query:

NOTE: There are options on the file menu that will allow you to save the view as PNG, JPG or SVG.

Now if we want to change the colors of the points based on our fakepop, we do this.

1. Select the Layer
2. Right mouse-click and select Change Styles
3. Switch to Color Theming tab and click to Enable Color Theming and also by Range.

Your screen should look something like this:

And the result of your hard work should look something like this