30. June 2016Sebastian 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 DatabaseMy 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
InstallationThe installation is described for Windows and Ubuntu users. For iOS there is this great piece called PostGIS.app that comes with PostGIS already.
UbuntuThe 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.listand 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 mainIf 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 mainAfter 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 pgadmin3Once 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 postgresAs 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 peerChange it to
# "local" is for Unix domain socket connections only local all all md5Now I restarted PostgreSQL with
sudo /etc/init.d/postgresql reloadAfter 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!
WindowsFor 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 PostGISIn 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.
QGISIn 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'
ArcGISSo 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:
OSMReally cool feature: import OSM data directly using osm2pgsql. Install osm2pgsql with:
sudo apt-get install osm2pgsqlYou 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.pbfThe 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.