PgAdmin III 1.13 - change in plugin architecture and PostGIS Plugins

One of the neat changes already present in the PgAdmin III 1.13dev, is the change in plugin architecture. Version 1.13 dev allows for multiple plugin*.ini files. How does this work. Well if you have a plugins.d folder in your PgAdmin III version folder, it will read all the inis in that folder and load them as plugins.

Recall in PgAdmin III Plug-in Registration: PostGIS Shapefile and DBF Loader, we demonstrated how to load the PostGIS shapefile and dbf loader as a plugin in PgAdmin III, well this time we will demonstrate how to do it using PgAdmin version 1.13. Better yet, we'll show you the new and improved PgAdmin III Shapefile and DBF Loader in the works for PostGIS 2.0.

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

Side Note: We also have windows binaries (only 32-bit currently sorry) for the latest changes in PostGIS 2.0 for PostgreSQL 8.4, 9.0, and 9.1 Aplha 1. Keep in mind before PostGIS 2.0 is released, there will be some major changes in disk format and old deprecated functions being dropped. These haven't happened yet, BUT wILL. You can however start testing out some of the new Raster features and 3D functions. Jorge Arevalo has a nice sequence of articles showing Raster operations normally done with Oracle GeoRaster, and how you would accomplish similar functionality with PostGIS Raster. Some of the content is sadly already dated, since WKT Raster has been officially folded into PostGIS 2.0.

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: PostGIS 2.0 laoder files selected
  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 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.