Archive for the ‘ Postgresql ’ Category

Getting .fit file data into PostGIS

GPSBabel released an update (1.4.4) on Labor Day.  This update fixed GPSBabel’s FIT file compatibility.  Now FIT files created on Garmin Edge and other devices can be converted into formats that can be imported into PostGIS.  Follow along after the jump to see how.

Continue reading

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.

Quick and easy way to create read-only databases

Postgresql doesn’t have a one-step command to grant permissions on a batch of tables like you can in MySQL.  However, it is much simpler in 9+ than it was previously.  You still have to go schema by schema in a database, but at least you don’t have to go table by table, view by view.   Here are the steps to create a read-only user:

First, create the user:

CREATE USER readonlyusername WITH PASSWORD 'password';

Now for each schema in a database you want the read only user to have access to you need the next two statements:

GRANT USAGE ON SCHEMA schema TO readonlyusername;

GRANT SELECT ON ALL TABLES IN SCHEMA schema TO readonlyusername;

If you’ll be adding new tables or views and you want them to be accessable by your read only user you need the following:


If you know of a quicker and/or easier way please let me know!