Posts Tagged ‘ PostGIS ’

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:

ALTER EXTENSION postgis UPDATE TO '2.3.0';

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!

Displaying PostGIS data in ArcGIS Desktop

There are a few ways to display PostGIS data in an ArcGIS Desktop map.  The easiest I’ve found is to use Ragi Burham‘s OGR Plugin for ArcGIS.  It allows you to bring in data from almost any OGR supported vector format, including PostGIS and Spatialite.  For right now the layers you bring in will be read-only but according to the project’s FAQ write support will be added thanks to new features in ArcGIS Desktop 10.1 SP1.

The project is in Beta and there are some rough corners.  To connect to your PostGIS database you have to provide a connection string, there is no GUI for this as of yet.  For now though, the example connection string under the field where you type in your information is a very good guide on what information you need and how it needs to be formatted in order to create a connection.  Once you bring in your layers they act like any other vector layer, except they are read-only.  The only problem I’ve found so far is labels aren’t placed correctly.

On the bright side this plugin allows you to use your PostGIS vector layers as base layers for data creation and editing.  You can use ArcGIS’s snapping and trace features as if the layers were a native shapefile or feature class and for cartography purposes you can symbolize your layers by category or quantity.

Overall this plugin is a great addition, a simple way to integrate OSgeo data, and other OGR supported data into your maps.

 

UPDATE: Map documents don’t store the connection information, so if you save a mxd and open it later the connections to your PostGIS layers will be broken.

Checking Distances in a Single Table

At work we have two identical GPS units.  One had to be sent in for service so we wanted to check to make sure it was back in working order.  We collected a series of points with both units.  I took the data collected and put it into a Postgres table; the table ended up with three fields:  set_id which identified the point as collected on each unit, unit_name to show which unit collected the point, and geom for the geometry of the point itself.  Here’s the query I came up with to compare the data:

SELECT DISTINCT a.set_id, ST_Distance(a.geom, b.geom) As dist
FROM test_pts As a JOIN test_pts As b USING (set_id)
WHERE a.unit_name <> b.unit_name
ORDER BY dist DESC;

Too often I forget about self-joins.  They made this query quick to write and easy to understand.

Installing Postgis 2.1.0SVN on Arch

I wanted to try the new Tiger geocoder extension that’s coming with PostGIS 2.1.0.  I set up a PKGBUILD to upgrade the 2.0.2 that’s in Arch’s repository.

Since this is a major version upgrade a hard upgrade is required.  The upgrade went smoothly except the spatial_ref_sys table wasn’t populated.  Running the spatial_ref_sys.sql script found at /usr/share/postgresql/contrib/postgis-2.1 solved that problem.

Half off PostGIS In Action 2nd Edition!

James Fee found a 50% off coupon code for PostGIS In Action’s 2nd Edition.  Not only does the coupon code get you 50% off the 2nd edition, you also get the 1st Edition, and a PDF download of a preview of the 2nd edition as it is in progress.   Currently they’re up to chapter 5, and so far it is awesome.

The book really pushes OpenJump due to its ability to run and display ad-hoc PostGIS queries.  They do a run down of various OSGeo desktop programs and in their QGIS writeup they lament the fact that QGIS doesn’t have out of the box ad-hoc capabilities like OpenJump.  I’m not sure about that though, the DB Manager function added in 1.8 seems to be pretty complete.  You can send your query results to the map canvas, DB Manager has syntax highlighting and autocomplete, it’s awesome.  The 1.9 version is even better since it adds import / export functions.  You can use the import function to import shapefiles or any other format OGR supports.  It also allows you to import your ad-hoc results into your PostGIS database.  You can take your tabular results and copy / paste them to a text file or spreadsheet as tab delimited data.

The one limitation to DB Manager / QGIS is your ad-hoc queries have to have a unique integer field if you want to see the resulting layer.  I usually get around that by using Postgres’s row_number() window function.

Installing PGRouting on Arch Linux

PGrouting adds routing functionality to a PostGIS database.  For Arch users you can install it from AUR.  If you want PGRouting’s Travelling Sales Person (TSP) functionality installed you’ll need to install Gaul (available from AUR from gaul-devel) and if you want Driving Distance (from AUR as cgal).  After you install PGRouting run

sudo ldconfig

so you’re system knows where to find the new PGRouting libraries.

Next up is to add PGRouting to your PostGIS database.  PGRouting doesn’t have an extension yet so you’ll have to run some sql files.  I like to install PGRouting into its own schema so I start psql and use:

CREATE SCHEMA pgrouting;
SET search_path pgrouting, postgis;
\i /usr/share/postlbs/routing_core.sql
\i /usr/share/postlbs/routing_core_wrappers.sql
\i /usr/share/postlbs/routing_tsp.sql
\i /usr/share/postlbs/routing_tsp_wrappers.sql
\i /usr/share/postlbs/routing_dd.sql
\i /usr/share/postlbs/routing_dd_wrappers.sql

You may get some errors like

ERROR:  language "C" does not exist

If you do, just open the file you tried to run and find the line referenced.  Change the “C” to “c” and then re-run the .sql file.

Now all that is left to do is add the pgrouting schema to your search path.  Personally I use an ALTER DATABASE statement, but other people may prefer to make the change on a per user basis.