Upgrading Postgres & PostGIS on Windows

Major upgrades for both PostGIS and Postgresql were released in the last few weeks.  Postgres moved from 9.5 to 9.6 and PostGIS from 2.2 to 2.3.  With both programs moving up major version numbers you have to take an extra step to make sure your upgrade goes smoothly.  In Windows PostGIS is an easy install, it comes bundled with the Postgres installer in the StackBuilder.  This is very handy, except in cases like this were both programs are jumping to new major versions.  You’ll run into a snag if you try to use pg_upgrade to migrate your database from the old to new server.  Pg_upgrade will look for the old PostGIS libraries in your new Postgres directory, not find them and error out.  To get around this you’ll need to upgrade PostGIS first.

To start download the new version of PostGIS for your current Postgres install from here: http://download.osgeo.org/postgis/windows.

Once installed upgrade PostGIS in your spatial databases by running:


Change 2.3.0 to the version up PostGIS you are upgrading to. Once you upgraded PostGIS in your old spatial databases run the Postgres installer for the new version. The installer will install the new version in parallel with your existing install(s), by default it will run the new version on port 5433 instead of 5432.

After the new version is installed make sure both the old and new server services are stopped, then in each servers data folder open the pg_hba.conf file and change the connection method to ‘trust’.  Next open a command prompt and navigate to a directory you have write permission in and run:

"c:\Program Files\PostgreSQL\9.6\bin\pg_upgrade.exe" -b "c:\Program Files\PostgreSQL\9.5\bin" -B "c:\Program Files\PostgreSQL\9.6\bin" -d "c:\Program Files\PostgreSQL\9.5\data" -D "c:\Program Files\PostgreSQL\9.6\data" -p 5432 -P 5433 -U postgres

Change the version numbers and database superuser account as needed.

Installing PostgreSQL 9.5 and PostGIS 2.2 on Fedora 24

Here’s the easiest way to get a very up-to-date installation of PostGIS up and running quickly.  First up, set up the Postgres 9.5 repository straight from Postgres:

sudo rpm -ivh https://download.postgresql.org/pub/repos/yum/9.5/fedora/fedora-24-x86_64/pgdg-fedora95-9.5-3.noarch.rpm

Next up, install Postgres:

sudo dnf install postgresql95 postgresql95-server postgresql95-libs postgresql95-contrib postgresql95-devel

The -devel package is optional, but I will be building Ruby’s PG gem later so it needs to be there for me. If you have no such plans feel free to skip it.  If you want a GUI to help manage the database add the ‘pgadmin3’ package to the list. Now it is time to initialize the database. Do so with:

sudo /usr/pgsql-9.5/bin/postgresql95-setup initdb

That will take a minute to run as it sets up the initial database. Once the database is initialized start Postgres with systemd by running:

sudo systemctl start postgresql-9.5

If you want Postgres to start at boot run the same command but change ‘start’ to ‘enable’ before you reboot or shutdown. Next up is a little housekeeping. Set a password for the Postgres account.  I like to do this in the database itself rather than on the Postgres user account.  To do so run:

sudo -i postgres psql postgres

This starts a psql session as the Postgres user then in psql allowing you to set the password with:

\password postgres

Next up since this is just a development box I’ll change Postgres’ login method from Ident to MD5.

sudo gvim /var/lib/pgsql/9.5/data/pg_hba.conf

Change all ‘peer’ and ‘ident’ to ‘md5’. If this is a production server you’ll want a more secure and robust login method. However, if you don’t want bother with passwords at all instead of ‘md5’ replace them ‘trust’. Next up is installing PostGIS. It and nearly all its dependencies are nicely packaged in the Postgres repository making it easy to keep the two in sync. Install PostGIS with:

dnf install postgis2_95 postgis2_95-client

As a test you can create a database called ‘test’ and enable PostGIS with:

sudo -u postgres createdb test
psql -d test -U postgres -W -c "CREATE SCHEMA postgis; CREATE EXTENSION postgis WITH SCHEMA postgis; ALTER DATABASE test SET search_path TO public, postgis;"
psql -d test -U postgres -W -c "SELECT postgis_full_version();"

If the last command returns information about the version of PostGIS installed you’re good to go. Have fun!

Getting QGIS Server to work on Windows

Anita Graser published an easy to follow guide for getting QGIS Server running on a Windows system.  There have been a few small changes since this guide was published, the biggest being Apache released version 2.4.

First up is to download Apache and the fcgi mod from Apache Lounge.

Unzip Apache to c:\, which will create C:\Apache24.  From the modules zip extract mod_fcgid.so to C:\Apache24\modules.

Copy httpd_qgis.conf from C:\OSGeoW64\httpd.d\httpd_qgis.conf to C:\Apache24\conf\extras.  Comment out lines 13 and 14 and add:

Require all granted

on a new line above </Directory>.

In c:\apache24\conf\httpd.conf add

Include conf/extra/httpd_qgis.conf

to the # Supplemental section.


Now if you go to the link below you should see the capabilities page for your QGIS Server installation.


Presto is cool

So far I’ve only noticed a few superficial differences between Fedora and Arch.  One of the cooler Fedora features is Presto.  Where Arch uses Pacman to as its package management system Fedora uses Yum.  Presto is an add-on to the Yum system that allows users to download only the changes to installed binaries.  For example today a new version of Libreoffice hit the Fedora repositories.  Where under Arch I would have had to download 99MB of files, but thanks to Presto only sending the changes I downloaded a total of 3 MB.  This is a great feature for those on slower connections or who are under monthly bandwidth caps.

COGO for QGIS and ArcView

A few days ago one of my favorite QGIS 1.8 plugins was updated to work with QGIS 2.0.  The Azimuth and Distance Plugin allows you to map a polygon by COGO calls which is very handy if you’re mapping a property boundary from its deed description.  The only drawback is it will only measure the line segments in project’s coordinate reference system’s units, or if your CRS is in meters you can enter the distances in feet.  If you have an older deed that is measured out in rods, chains, and links you’ll need to convert the distances manually before drawing out your polygon.

If you’re an ArcView (or ArcDesktop Basic if you prefer) user who needs to do some COGO work and don’t want to spend a ton of money to upgrade to ArcEditor here’s a guide to how to set up ArcMap and how to do a ground to grid correction without the COGO toolbar.

Switched from Arch to Fedora

A few months back my old Dell Inspiron laptop died.  It was a warhorse, surviving drops onto concrete floors, being stepped on by a big dog.  The cap to the zero key popped off, and the tab key would stick from time to time but it kept on trucking.  Most of the bezel around the monitor had broken / fallen off, I think this was the downfall.  Finally the monitor quit working.  I couldn’t work with it docked up to an external monitor so I replaced it with an ASUS K55N.

If I were a Windows only user it would be a great laptop for the price, but due to work and personal preference I’m booted into Linux 99% of the time.  This suited me well on the Dell, its 2GB of RAM and aging processor made Windows molasses slow, meanwhile the Linux side hummed right along as long as I didn’t have too many browser tabs full of Flash videos open at once.  However, on the ASUS I kept running into issue after issue.

First of all getting Arch onto the ASUS was a chore.  Part of the problem was this was my first attempt at installing an OS onto a UEFI machine.  Eventually after figuring out how to turn off the secure boot feature I was able to boot to the Arch LiveCD installer.  The installation went fine until I tried to reboot and GRUB2 did not take over.  For some reason the efimanager did not install or run correctly during the installation process.  Eventually I switched to ReFind and figured out how to manually add an entry for it in the laptop’s boot order.

Once Arch was installed I had a few more problems to work through.  Suspend wouldn’t work, I gave up on solving that issue and switched to using Hibernate instead.  Another issue was with the sound system, I could not get audio to work for more than one program at a time, and once a program “claimed” the sound no other program could output to the speakers until the first program was closed.  Going through pages of Google results pointed to PulseAudio as the culprit, but I could not remedy the situation.  Both of these were minor annoyances, the final straw moving me from Arch to Fedora was QGIS stopped working.

The first two problems were most likely hardware related.  QGIS however was an Arch only issue.  The problem came about as a result of the Achilles heel of rolling release distributions, a key library was updated to a version incompatible with QGIS.  I was unable to launch QGIS and compiling a new version would fail.  Facing a deadline I decided to wipe Arch and move on to Fedora.

Installing Fedora was much easier than installing Arch.  Partly due to the fact I was now more familiar with the ins and outs of UEFI, but also Fedora’s installer is a user friendly GUI and Arch’s installer is a wiki page on their website.

Fedora has generally run better on the ASUS.  The audio system now works perfectly, but I can no longer hibernate.  The laptop will hibernate, but on recovery the screen goes black.  The computer is running, switching to a different TTY allows me to log in and run commands from the command line, but I can’t get the screen to come back on.  I think this is due to the ATI video card and its radeon driver.   Hopefully I can find a solution soon.

I was able to get my GIS stack installed, all of the programs I use are in Fedora’s repository.  I did have to make a change to GDAL, I use several non-open GIS data formats (ESRI File Geodatabases, MrSID rasters, and ERDAS ecw rasters).  Under Arch it was just a matter of downloading the appropriate API or SDK, altering the default PKGBUILD file to point to the appropriate libraries and recompiling.  Under Fedora you have to edit the SPEC file, and SPEC files are much more complicated that Arch’s PKGBUILD files.  In addition Fedora’s package management system expects any libraries that a packaged program is complied against to be accounted for in a package.  Eventually I got it all sorted out and I got back to work.

I’m still too new to Fedora to do a comparison between it and Arch.  Certainly installing Fedora is much easier than installing Arch.  For day to day use they are about the same, in fact since I kept my /home on a separate partition they are nearly identical experiences.  Both use systemd to manage services, and both give you up to date software.  That was one worry about moving to Fedora, Arch’s rolling release always gave me easy access to the latest versions of the software I used on a daily basis.  Although Fedora isn’t rolling release all of the software I use is the latest and greatest.  QGIS, PostGIS, Postgres, LibreOffice, etc are all up to date.  As I said earlier the rub comes when I need to tweak software from the Fedora default.  Perhaps it will get easier for me as time goes on, or perhaps I’ll flush my root directory and reinstall Arch.

ESRI makes it (kinda) easy to move data around via Spatialite

With the release of ArcGIS 10.2 ESRI added support for Spatialite databases.  You can’t directly edit Spatialite data, but you can create a new database, import data into one, and export data from one.  You can even create new feature classes in a database along with new tables and views.  These features make Spatialite databases a great way to move data from organization to organization.

There isn’t a way though the GUI to create a spatialite database, but it only takes three lines of ArcPy code.

import arcpy

out_path = 'C:/Data/Test.sqlite'

arcpy.gp.CreateSQLiteDatabase(out_path, 'SPATIALITE')

If you use the Python Geoprocessing window you can get it down to two lines by skipping the import statement.  If you skip the optional ‘SPATIALITE’ parameter Arc will store the geometry in ESRI’s native ST_Geometry format, which shuts out people using OSGeo software, so please use the ‘SPATIALITE’.

Once your database is created you can use ArcCatalog to import data into it, export data from it, create a new table, create a new view or a new feature class.  You can view your data in ArcGIS, or view and edit your data with QGIS and other OSGeo GIS platforms, or you can use the spatialite GUI.