PostgreSQL and PostGIS: A brief introduction

You probably seen this already (maybe on your very own PC as well): A folder with shape files. Well we’re living in the 21st century and I do have and use those folders still. After a talk of Sebastian Meier at Maptime Berlin I was convinced and started to work with a databases instead of folders. So let me show you how to install PostgreSQL along with PostGIS on Ubuntu and Windows, how to get data into it, import OSM data and how to connect it with QGIS/ArcGIS.

Why-Oh-Why a PostGIS Database

My normal type of geodata were the good old shape file. But once you work with them and you need to work often with them they come quite unhandy:

• 4 files at least for a valid shape file (.prj, .dbf (WTF!!!), .shp and .shx)
• it’s binary: good luck with using a text editor to open it
• attribute names like “UK_LO_RoadNetwork”?! Forget it! 10 Characters at max
• maximum file size (.dbf / .shp) is 2GB
• numeric attributes are stored as characters (integers/floats), causing potential problems with rounding etc.
• NULL values are interpreted differently between systems
• …

And as I am a fan of open source technology the first thing I was thinking of as an alternative is of course PostgreSQL and its extension PostGIS. It is fully supported in QGIS (there is a limited implementation in ESRI as you might see later on).

Installation

The installation is described for Windows and Ubuntu users. For iOS there is this great piece called PostGIS.app that comes with PostGIS already.

Ubuntu

The installation on Ubuntu drove me nuts as I am running Xenial Xerus (aka Ubuntu 16.04). So normally I would use the UbuntuGIS PPA but this is not supporting Xenial. So I switched to the unstabel UbuntuGIS PPA which gives me the opportunity to install PostgreSQL 9.5 with PostGIS 2.2. So first I added the ppa to my sources list. You can do this by hand:

sudo edit /etc/apt/sources.list

and add the following lines in the end:

deb http://ppa.launchpad.net/ubuntugis-unstable/ubuntu xenial main
deb-src http://ppa.launchpad.net/ubuntugis-unstable/ubuntu xenial main

If you run Ubuntu 14.04 you might be lucky with using the stable repo:

deb http://ppa.launchpad.net/ubuntugis/ppa/ubuntu trusty main
deb-src http://ppa.launchpad.net/ubuntugis/ppa/ubuntu trusty main

After the source was added I was moving on:

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib postgis postgresql-9.5-postgis-2.2 pgadmin3

Once this is done we will also alter the postgres user for our db which is also set in the db itself: Change the postgres user’s Linux password, change user to postgres and alter the password in the db:

sudo passwd postgres #sets a new password
su - postgres #changes to user postgres
psql -d template1 -c "ALTER USER postgres WITH PASSWORD 'newpassword';" #change 'newpassword' to the one set before inside of postgres

As it is said here we shall tighten up the access to the DB a little by editing the file /etc/postgresql/9.5/main/pg_hba.conf:

# "local" is for Unix domain socket connections only
local    all        all             peer

Change it to

# "local" is for Unix domain socket connections only
local    all        all             md5

Now I restarted PostgreSQL with

sudo /etc/init.d/postgresql reload

After that, you can start pgadmin3 with the command “pgadmin3” in the console. This will open the GUI to administrate PostgreSQL databases. Just use the File–>Add Server dialog to connect to your local running postgres database and you’re ready to go.
As you can see we still miss the PostGIS extension. PostGIS enables PostgreSQL to store spatial data and also provides some cool tools and functions to analyze your data. This can be added to any database via pgAdmin3 So I am creating a database with this extension:
An alternative is also the command line but first use the postgres user for this:

su - postgres
psql -c "CREATE DATABASE tutorial;"
psql -d tutorial -c "CREATE EXTENSION postgis;"
psql -d tutorial -c "CREATE EXTENSION postgis_topology;"

Now we are ready to go!

Windows

For Windows the installation is a bit more straightforward: EnterpriseDB already offers a nice installer. It|s very simple and you will be asked in the end whether you would like to install PostGIS as well: Here is the gallery with all dialogues:
Now you can run pgAdmin3 (it’s installed along the PostgreSQL installation from EnterpriseDB) and connect it with your database:

GIS data and PostGIS

In this section I’ll show you how to connect with your locally running database and how to add and work with data in QGIS and ArcGIS. In the end I’ll shortly show some OSM data interaction with PostGIS.

QGIS

In QGIS I am using the Browser Panel (View–>Panels–>Browser Panel) to connect with my DB and create a new PostGIS connection. The values needs to be filled in are comparable to those from the pgAdmin3 connection:

Once you have a connection you can add data from QGIS and get data from the DB as follows:
If you want to use the great PostGIS functions like buffer or intersect directly in QGIS. I will use the buffer function from PostGIS instead of the QGIS / GDAL one and fill a new polygon table in my database with the result: It all starts with a select:

SELECT geom FROM schools; --this will create a selection with only th attibute "geom"
SELECT 
	geom,
	id --we can select every column for a new layer we like
FROM schools;
SELECT 
	geom,
	id 
FROM schools WHERE kids < 500; --we can select even with simple clauses
SELECT 
	ST_Transform(geom, 3857), -- you can transform your geometry to 3857 with this small line
	id,
	kids,
	classes_per_school
FROM schools WHERE kids < 500;
SELECT 
	ST_transform(ST_Buffer(ST_Transform(geom, 3857),1000),4326), --and even combine it with a buffering...we needed to transform to EPSG3857 to define a 1000m buffer
	id,
	kids,
	classes_per_school,
	ST_AsGeoJSON(ST_transform(ST_Buffer(ST_Transform(geom, 3857),1000),4326)) AS geom --create this column with the name 'geom'
FROM schools WHERE kids < 500;

I am using those commands in the DB manager in QGIS:
And if you like to store it directly again in your PostGIS database you can create a table out of your selection:

CREATE TABLE buffer AS SELECT 
	kids,  
	id,
	ST_Transform(ST_Buffer(ST_Transform(geom, 3857),1000),4326) As geom 	
FROM schools WHERE kids < 500;
SELECT Populate_Geometry_Columns(); -- set it as a polygon layer
CREATE INDEX id ON buffer USING GIST ( geom ); -- creates a spatial index to speed things up for future work
ALTER TABLE buffer ADD PRIMARY KEY (id); -- set the primary key to the key in the attribute 'id'

ArcGIS

So let me have a look at ArcGIS. I am working with ArcGIS 10.3 and the data interoperability extension. This was provided by ESRI with the home use license (thanks again @ESRI_de). The data interop extenesion lets you import and export data to PostgreSQL. But let me stop writing and switch over to a nice video tutorial on how to add and query data in ArcMAP:

OSM

Really cool feature: import OSM data directly using osm2pgsql. Install osm2pgsql with:

sudo apt-get install osm2pgsql

You can download OSM data from the side geofabrik. Once you have selected a proper region you can download the osm.pbf file to your local machine via the browser or the commandline. The import into PostGIS is shown in the second line:

wget http://download.geofabrik.de/europe/great-britain/england/greater-london-latest.osm.pbf
osm2pgsql -W -c -d tutorial -U postgres -H localhost greater-london-latest.osm.pbf

The commands are as followed:

• -W: Force password prompt.
• -c: Remove existing data from the database. This is the default if –append is not specified.
• -d: The name of the PostgreSQL database to connect to (default: gis).
• -U: PostgreSQL user name.
• -H: Database server host name or socket location.

Now you have all the data from London in your database. If you store the polygon features in a shape file you will create huge files!!! The memory used in the db is quite low with approx. 120mb… (command: SELECT pg_size_pretty(pg_total_relation_size(‘public.planet_osm_polygon’)) )

Conclusion

PostgreSQL with PostGIS is great!