Archive for February, 2012

QGIS 1.7.4 and Arch

QGIS 1.7.4 is now available from AUR.  Before upgrading you’ll want to upgrade Grass to 6.4.2.  After I upgraded Grass QGIS upgraded without any problems.

Connecting to Postgresql with LibreOffice Base

A native Postgresql connector has been added to the Arch Repositories.  Read about it here.


You can use LibreOffice Base to take a quick look at your Postgresql databases, build queries with its query GUI, use the report builder to  build spiffy reports of your data, or use the Base interface to paste data into Postgres.

To connect you’ll need to use Base’s JDBC interface, and to use the interface you’ll need to install the Postgres JDBC driver.  You can download the driver from here, or if you’re using Arch you can find the driver in AUR as postgresql-jdbc.

Before you can use the driver you have to tell LibreOffice where to find it.  Click Tools -> Options…  In the Options menu select Java then click Class Path.  Click Add Archive and find the jar file for the driver.  If you installed from AUR the driver will be found in the /usr/share/java/postgresql-jdbc directory.  If you’re using Java 1.6 or 1.7 use version 4 of the driver, otherwise use version 3.

After you tell LibreOffice where to find the driver you’ll need to restart Base.  The JDBC connection string is formatted more or less like this:


Then tell Base to use the org.postgresql.Driver class.  The next step will ask you for a username/password to use to connect to the database, from there you’ll have access to all the tables that account has access to.

Installing pgRouting in Arch

Installing pgRouting in Arch is a very straightforward process.  The latest version is in AUR, you can install it with yaourt.  If you’re going to need pgRouting’s travelling salesperson (TSP) and/or driving distance (DD) functions you’re going to need to install the Genetic Utility Algorithm Library (GAUL) for TSP and/or Computational Geometry Algorithms Library (CGAL) for DD.  Both libraries are also available from AUR.  Once they are installed  edit pgRouting’s PKGBUILD file and uncomment out the cmake line that activated the DD and TSP flags.

Once pgRouting is compiled and installed you’ll need to add it’s functions to a PostGIS enabled database.  Personally I like to keep pgRouting’s functions in their own schema.  So when I’m adding pgRouting to a database I do the following in psql:

CREATE SCHEMA 'pgrouting';
SET SEARCH_PATH TO 'pgrouting', 'postgis';
\i /usr/share/postlbs/routing_core.sql
\i /usr/share/postlbs/routing_core_wrappers.sql 
\i /usr/share/postlbs/routing_topology.sql 
\i /usr/share/postlbs/routing_dd.sql 
\i /usr/share/postlbs/routing_dd_wrappers.sql
\i /usr/share/postlbs/routing_tsp.sql
\i /usr/share/postlbs/routing_tsp_wrappers.sql

Like pgRouting’s functions I like postGIS’s functions to live in their own schema, most installations put them in the public schema.  If that’s the case in your database replace postgis with public in the set search_path statement above.


QGIS plugins: RT SQL Layers vs Fast SQL Layer

Two fun plugins for QGIS:  RT SQL Layers and Fast SQL Layer.  They have similar functionality, they let you write your own queries against a PostGIS or Spatialite database and have the results.

First, Fast SQL Layer.  Before you install the plugin it needs a python module, pygments, installed.  This will allow Fast SQL Layer to have syntax highlighting.  For Arch users install the module with pacman:

sudo pacman -S python2-pygments

After that is installed, in QGIS go to the Fetch Python Plugins entry under the Plugins menu, then click the Repositories tab, then click the Add 3rd party plugins button.  Once the repositories are updated you can go back to the first tab and install Fast SQL Layers.

Second is RT SQL Layers Plugin.  To add this plugin you need to point QGIS to the Fanutla plugin repository.  If the Add 3rd party plugins button did not find the repository add it manually by copying the target of the previous link and adding it with the Add button.

Now which one is better?  Personally I think it is a tie.  RT SQL Layers gives you a nice GUI for creating your query, just click on the tables and field you want returned and it formats the query for you.  However, once you click OK its window disappears and unless you copied or saved the query you have to start from scratch.  Fast SQL Layers doesn’t have a GUI, it just gives you a window, by default this window will fit itself below your map, and you type out the query by hand.  However, since the window doesn’t go away and the syntax highlighting is very helpful it is easy to catch mistakes and to tweak queries through several versions without starting from scratch each time.

Geos 3.3.2 is in the Community repository

Geos 3.3.2 hit Arch’s Community repository yesterday.  This is big since now all three of PostGIS‘s dependencies (GEOS, Proj, and Postgresql) are now fully up to date which should make the update to PostGIS 2.0 go as smoothly as possible.

GDAL 1.9 hits community-testing

GDAL 1.9.0 has hit Arch’s community-testing repository.  Hopefully Geos 3.3.2 won’t be far behind.  It’s important that both are up to date before PostGIS 2.0’s release is official.