Alex Sorokine's Random Notes

Saturday, November 2, 2013

Upgrading PostgreSQL 9.1/PostGIS 1.5 to PostgreSQL 9.3/PostGIS 2.1 on Ubuntu 12.04 (precise)

Newer versions of PostgreSQL and PostGIS (9.3 and 2.1) have some nice features like materialized view and raster support respectively. My experience of upgrading both packages from version 9.3 and 1.5 was rather painless and is outlined below.

  1. The versions are not binary compatible, so I have had to dump and restore all databases that I wanted to see after upgrade (no need to dump system templates like template0 and template1)
  2. Replace gis1 gis2 gis3 with a space-delimited list of the databases which you want to preserve after the upgrade. This command will result in a set of binary dump files, one file per database. The file names will containing the name of the database and the date of the dump.

    You will also need a list of database users. You can get the list with \du command in psql.

  3. Remove old packages (this is likely optional, postgresql versions can co-exist but I did not try)
  4. Install new postgresql version following these instructions: https://wiki.postgresql.org/wiki/Apt
  5. (automatic install of 9.3, may need to specify version explicitly)
  6. By this time you should have postgreql server up and running but not responding to external connections. This is a great time to restore dumped databases. Typical cycle for restoring a database includes:
    1. Recreate all database users
    2. Create the database
    3. Recreate extensions, PostGIS 2.0 uses extension mechanism and there is no need to use scripts
    4. restore all other extensions that are needed by the database
    5. Restore the database from the dump file
    6. Now check the content of the err.txt file, it should be empty
      1. However, if you see errors because of any users or extensions missing, drop the database, repeat all the previous steps and also create the missing users and extensions.
      2. If you see an error like 'invalid command \N', first fix all other problems with the databases and repeat the import again, this error will be likely gone.
  7. Reclaim space and update statistics:
  8. modify hba file to your need (details), enable listening on external address in postgresql.conf and finally do

Wednesday, January 25, 2012

Parser for command line parameters in Java/C/C++/Python/shell

My question on stackoverflow:


I am looking for a library to parse command-line parameters that would work identically in Java, C/C++, Python and (preferably) shell. By "identical" I mean (1) have exactly the same rules for parsing of the parameters in all three languages, (2) use the same configuration files or have similar API to specify the parameters, (3) have similar APIs to access the values of the parameters.
I've always used getopt in C and Apache CLI in Java but it would be nice to use the same specification for the parameters across multiple languages.

Wednesday, December 14, 2011

OS X logout from the command line

This morning I found my Mac desktop stuck on logout. I did not want to force reboot it so I did some peeking around for how to logout peacefully. Looks like it is very easy to do:

  1. ssh into the system from another Mac or linux machine
  2. sudo killall loginwindow

Sunday, December 4, 2011

Array permutation

Here is a snippet of Java code that will permute indexes of an array: For working Java class see https://github.com/sorokine/FischerKrause

Tuesday, May 31, 2011

PostGIS function for a random point inside a polygon -- page on PostGIS wiki

I have created a page on PostGIS wiki with the description of the random point functions: http://trac.osgeo.org/postgis/wiki/UserWikiRandomPoint

Monday, May 23, 2011

PostGIS function for a random point inside a polygon -- REVISED

This is a revised random-point-in-polygon function first discussed in my earlier post. It turns out that in some weird cases (multipolygons consisting of a number of small and disperse polygons) the function takes too many iterations to complete. Below is a code for another function called RandomPointMulti that addresses this problem. RandomPointMulti breaks a multipolygon into polygons, randomly chooses a polygon with the probability proportional to the area of the polygon, and then calls original RandomPoint on that polygons. RandomPoint was also modified: some typos have been fixed, exception is thrown if the max number of iterations has been exceeded, and and max number of iterations can be specified as an argument.

Wednesday, April 13, 2011

PostGIS function for a random point inside a polygon

Check the revised version of this post.

This is a function that I wrote to generate a random point inside a polygon. It works by first generating a random point within the bounding box of the polygon and then checking if the point falls inside of the polygon. The limit is 1000 iterations. This limit is probably enough for most practical cases with the exception for polygons that occupy only a small part of its bounding box. This is my first foray into PostgreSQL functions, please judge accordingly. I did not check how the function performs on the geometries other than polygons.