PgAdmin III 1.13 - change in plugin architecture and PostGIS Plugins   

Installing PgAdmin 1.13 dev

Well PgAdmin 1.13 binaries, are not available on the PgAdmin official site, however they are packaged in the PostgreSQL 9.1 Alpha binaries. To use them, just extract the binaries and click on the PgAdmin3.exe in the bin folder. Most of our windows desktops can run them fine without any prior installation. However if you are on windows and they don't work out of the box, you may need to install the Visual C++ 2008 runtime libraries.

If you want to test out PostgreSQL 9.1 alpha on windows, you can do so by following our instructions listed in our Starting PostgreSQL in windows without install

Attached is our plugins.d which includes all the plugin inis for the plugins we will be describing

Steps for installing plugins

1. Extract binaries
2. Create a folder in the PgAdmin III folder called - plugins.d
3. To enable psql as a plugin, create a file -- doesn't matter what you call it, but something like psql.ini. You can copy the relative settings from your prior PgAdmin III install.
4. To install the new PostGIS Shapefile Loader, copy the postgisgui folder from our PostGIS Windows Experimental Builds page into the bin folder of your PostgreSQL 9.1 Alpa.
5. Create a postgis.shp2pgsql-gui.ini file in your plugins.d folder with the postgis specific ini contents.

   ;
   ; PostGIS shp2pgsql-gui (Windows):
   ;
   Title=PostGIS Shapefile and DBF loader 2.0
   Command="$$PGBINDIR\postgisgui\shp2pgsql-gui.exe" -h "$$HOSTNAME" -p $$PORT -U "$$USERNAME" -d "$$DATABASE" -W "$$PASSWORD"
   Description=Open a PostGIS ESRI Shapefile or Plain dbf loader console to the current database.
   KeyFile=$$PGBINDIR\postgisgui\shp2pgsql-gui.exe
   Platform=windows
   ServerType=postgresql
   Database=Yes
   SetPassword=Yes

6. Make sure your File->Options->bin folder is pointing at the bin of your extracted pg91 bin.
7. Exit out of PgAdmin III and then reopen
8. When you are done your plugins menu should look something like

Take new PostGIS 2.0 Shapefile loader for a test drive

The great new feature of the upcoming loader is the ability to load multiple files at once. Here is what that looks like. For this exercise -- we pulled data from 2009 Tiger/Line Shapefiles and downloaded the State and Equivalent, and Military Installation. We also wanted to add in the 5-digit Zipcode tabulation area 2002 but that is a 600 mb file and we were running out of disk space on our test box.

1. File browse as shown -- hmm hmm sadly in the current less than alpha version -- it seems you can only pick one file at a time so you have to do this twice.
2. Your screen should look something like this:
3. Click into each cell you want to change (you can click the RM checkbox to remove a file). Your screen should look something like this
4. Click options to set advanced options. Note that the options dialog applies to all files.
5. Click the Import button.

PostGIS plugin for displaying geometries in PgAdmin III

Now there is another postgis plugin available written by Jérôme ROLLAND using MapWindow .NET and npgsql.NET. Unfortunately, this sadly doesn't work on Linux/Unix (mostly because Mapwindow.net apparently doesn't run under Mono.NET yet), so at the moment is a windows only plugin. You can download it from Plugin PgAdmin III : PostGISViewer suite. Jerome has written some other PostGIS pgAdmin III plugins including one that piggy-backs on FWTools to provide an interface for exporting PosTGIS data into various formats.

Here is a demonstration of the plugin in action and how to install it.

1. Download the PostGISViewer.rar file and extract it into the bin folder of your PgAdmin III install (in this case the bin folder of your extracted PostgreSQL 9.1 Alpha). Note: rar file can be extracted witht eh free-ly available 7zip or WinRar
2. Create an ini file in plugins.d -- lets call it postgis.postgisviewer.ini. With the contents described in Un plug-in pour PgAdmin III : PostGISViewer
3. If you are running PostgreSQL 9.0 or 9.1, this tool doesn't seem to work unless you set the bytea_ouptut format of your database to escape: ALTER DATABASE test_postgis20 SET bytea_output='escape';
   

   Then exit out of PgAdmin

4. This tool works by writing temp shapefiles to the Data folder of the PostGISViewer. Make sure the folder is not READ ONLY
5. Open query window and type these queries:

   SELECT ST_AsBinary(geom) As geom, GeometryType(geom), stusps
   FROM staging.us_states
   WHERE stusps IN('MA');
   SELECT ST_AsBinary(m.geom) As geom, GeometryType(m.geom), m.fullname
   FROM staging.military_installations As m
       INNER JOIN staging.us_states As s ON ST_Intersects(s.geom,m.geom)
   WHERE s.stusps IN('MA');
   

6. Move your main pgAdmin window such that you can see both the query window and the plugins menu as shown below:
   
7. Select the PostGIS Viewer Plugin option, and then click the feature identifier icon tool and you should see a screen that looks like this:
8. The Outils tab has a whole slew of tools we didn't get a chance to test out.

Note: you can also launch the viewer by selecting a postgis table from the schema tree. We didn't do this since most of our tables are fairly large and painful to load.