Posts Tagged ‘ postgresql ’

PostGIS in Action 2nd edition is underway!

A few weeks ago Regina Obe posted that work has begun on the 2nd edition of PostGIS in Action.  I can’t wait until it is released, the first edition is an amazing resource and I’m sure the new edition will be better at covering topics covered in the first edition, plus be the best resource for all the new stuff that PostGIS 2.0 brought us such as topology and raster support.

Understandably it’s going to take some time to put all this information together, so until the new edition drops keep an eye on the BostonGIS page, the BostonGIS blog, and everything else Regina Obe and Leo Hsu do in the world of PostGIS and Postgresql.  It’s really too much to keep track of!


Arch updates Postgresql & PostGIS

Postgresql 9.2.1 and PostGIS 2.0.1 just hit Arch’s repositories.  Going from Postgres 9.1 to 9.2 is a major upgrade and will require your 9.1 databases to be migrated.  For steps on how to upgrade your data see this post in the Arch Wiki.  If you want to postpone updating your databases add postgresql and postgresql-libs to your IgnorePkg line in your /etc/pacman.conf file.

Using the native Postgresql connector with LibreOffice Base

I made an earlier post that showed how to connect to a Postgres database through a JDBC connection.  Now the native Postgres connector has been added to the Arch Repositories (libreoffice-postgresql-connector). After you use Pacman to install the extension open Base and tell it you want to connect to an existing database, then select Postgresql from the dropdown list.   It will ask you for a connection string, that string should be formatted like such:

dbname=postgres hostaddr= port=5433 user=db_user password=libreoffice

If Postgres is running on the standard port 5432 you can leave that out.  Depending on how you have your authentication set up in the database you can also leave off the user and password as well.

This method is much MUCH faster than using the JDBC connection.

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.

Batch Geocoding with TIGER Geocoder 2010

I’m compiling a database of all the microbreweries in my state.  So far I’ve got a list of 43 breweries and their addresses.  I’ve geocoded them against the 2010 TIGER street centerlines.  I used a batch geocoding example from PostGIS in Action the basis for this query.

Continue reading

Changing permission on files created by pl/r

With pl/r you have all the functions and abilities of R itself built available to you from your Postgres queries.  Among these is the ability to create image files of graphs.  When you create these graphs, however, by default they are given read-write permission to only the user who controls the Postgres daemon, usually that user is postgres.  There are no permissions for group or other so to see the image file you have to either chmod or chown the file as root and grant the permissions after the fact.  To save this step you can add the chown statement to your pl/r function.

system("chmod 666 /tmp/graph.png")

With the above statement your image, graph.png, will be read-writeable to the file’s user, group, and everyone.


PL/R is one of many language extensions to Postgres that allow you to write functions and triggers in the programming language of your choice, in this case the R language.  R is widely used in statistical analysis, the GIS community is using it more and more often, see this Boston GIS pl/r tutorial for more information.

To get pl/r up and running on Arch you first have to have R itself installed.  Luckily R is in the main package repository and can be installed with a simple pacman -S r.  The R package puts its dynamically linked library in a non-standard location so you’ll need to tell the library linker where to find it so pl/r can make use of it.  To do so go to the /etc/ directory and as root, or through sudo, create a file called R.conf.  The file only needs one line, the path to the R library:


Then as root or through sudo run:


Next you’ll need to install pl/r from AUR.  If you’re running a 64 bit system you’ll want to edit the PGKBUILD file’s Arch section and add ‘x86_64’.  It is missing from the PGKBUILD, it may be added to later versions.

Once pl/r is compiled and installed you’ll need to add it to the databases you want to do statistical analysis on.  If you’re running Postgresql 9.1 or greater pl/r can be installed as an extension.  The easiest way to do so is through pgadmin3.  Just select the database, right click on Extensions, select Add New, then select pl/r from the drop down.