Always use transactions when doing analysis in PostGIS

Yep, its one of those small annoyances – like keeping up-to-date backups – that you should always do: always use transactions when doing analysis in PostGIS.

In case you don’t know, transactions let you wrap a bunch of database modifications such as UPDATE, INSERT, CREATE, etc. in an undo-able block. This means you can recover from mistakes, like you would use undo when using Excel. When you are happy with your changes, you can then commit them to the database to store them permanently.

You start a transaction by issuing a BEGIN comand, and end it by either undoing the transaction with ROLLBACK or saving it with COMMIT. Here’s an example of how to do it, using pqsl:

Let’s say you realise you have a table two columns with latitudes that are incorrectly entered as positive numbers, although they refer to locations south of the equator:

gis=# SELECT lat_to, lat_from FROM closures ORDER BY closure_id DESC LIMIT 10;

 lat_to  | lat_from 
---------+----------
       0 | -14.9819
 13.8779 | -13.9802
 13.6661 | -14.0998
 14.6906 |        0
 13.6661 | -14.5559
       0 |        0
       0 | -13.9802
       0 |        0
 25.4141 |        0
 22.2898 | -22.9968
(10 rows)

So we, issue some updates, wrapped in a transaction:

gis=# BEGIN;
BEGIN
gis=# UPDATE closures SET lat_to = (-(abs(lat_to))) WHERE lat_to != 0;
UPDATE 1866
gis=# UPDATE closures SET lat_to = (-(abs(lat_from))) WHERE lat_from != 0;
UPDATE 1912

Now, we check our result and realise we have made a mistake, by updating the wrong field in our second query:

gis=# SELECT lat_to, lat_from FROM closures ORDER BY closure_id DESC LIMIT 10;

  lat_to  | lat_from 
----------+----------
 -14.9819 | -14.9819
 -13.9802 | -13.9802
 -14.0998 | -14.0998
 -14.6906 |        0
 -14.5559 | -14.5559
        0 |        0
 -13.9802 | -13.9802
        0 |        0
 -25.4141 |        0
 -22.9968 | -22.9968
(10 rows)

If we hadn’t used a transaction, we would now be stuffed. Fortunately, we can revert the changes:

gis=# ROLLBACK;
ROLLBACK
gis=# SELECT lat_to, lat_from FROM closures ORDER BY closure_id DESC LIMIT 10;

 lat_to  | lat_from 
---------+----------
       0 | -14.9819
 13.8779 | -13.9802
 13.6661 | -14.0998
 14.6906 |        0
 13.6661 | -14.5559
       0 |        0
       0 | -13.9802
       0 |        0
 25.4141 |        0
 22.2898 | -22.9968
(10 rows)

Now we can try again, fixing the broken query:

gis=# BEGIN;
BEGIN
gis=# UPDATE closures SET lat_to = (-(abs(lat_to))) WHERE lat_to != 0;
UPDATE 1866
gis=# UPDATE closures SET lat_from = (-(abs(lat_from))) WHERE lat_from != 0;
UPDATE 1912
gis=# SELECT lat_to, lat_from FROM closures ORDER BY closure_id DESC LIMIT 10;

  lat_to  | lat_from 
----------+----------
        0 | -14.9819
 -13.8779 | -13.9802
 -13.6661 | -14.0998
 -14.6906 |        0
 -13.6661 | -14.5559
        0 |        0
        0 | -13.9802
        0 |        0
 -25.4141 |        0
 -22.2898 | -22.9968
(10 rows)

Yay, the changes we wanted have been made, so we can now commit the transaction to save the changes permanently:

gis=# COMMIT;
COMMIT
gis=# SELECT lat_to, lat_from FROM closures ORDER BY closure_id DESC LIMIT 10;

  lat_to  | lat_from 
----------+----------
        0 | -14.9819
 -13.8779 | -13.9802
 -13.6661 | -14.0998
 -14.6906 |        0
 -13.6661 | -14.5559
        0 |        0
        0 | -13.9802
        0 |        0
 -25.4141 |        0
 -22.2898 | -22.9968
(10 rows)

UPDATE:In order to avoid rolling back transactions every time you make a typo inside them, set ON_ERROR_ROLLBACK to interactive.

Advertisements

  1. 1 Continuing a PostGIS transaction after an error « Opaque Overlay

    […] 1, 2010 in gis, postgis, sql Yesterday’s post expounded the virtues of doing GIS analysis within SQL transactions. One annoyance of that approach is that whenever an error is made within a transaction, the whole […]




Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s



%d bloggers like this: