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.
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:
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.
nice introduction! I always procrastinated getting to know PostGIS because it seemed a bit more complicated than ‘usual’ software and formats. But, apperently, it’s not a big deal though, thank you.
I could not access the file /etc/postgresql/9.5/main/pg_hba.conf
Keep saying: postgres is not in the sudoers file. This incident will be reported.
What should I do?
Exit from being user “Postgres” by typing “exit”. Then you can do everything with your own account which is most likely in the group of sudo
What about writing in the terminal: sudo gedit /etc/postgresql/9.5/main/pg_hba.conf
This will not work, as the user postgres is not in the sudo group. your very won user should be in this group. So if you typed su – postgres to work in pgsql than you need to exit this user again to gain access to sudo commands
Nice one Riccardo. I gave up using Postrgresql-PostGIS on Ubuntu, and went back to Windows. That said, a good Linux setup is easy to work with once it is running. The logical step now is to setup a GeoServer and to start publishing layers via SLD, you can generate the SLD files from within QGIS and then just copy and paste them into GeoServer.
and isn’t this feasible with QGIS server as well? I was always blind regarding the “QGIS server” side.
I don’t know, I assume so? I never used QGIS server. GeoServer seems more orientated to Web Publishing. I have used it with a WebGis based on OpenLayers. GeoServer acting as the middleware.
Hi, Riccardo, thank you for sharing, it is a nice job and very useful. But i have a trouble with connection database in QGIS. i made a databse with pgAdmin, called test, and creat a new PostGIS connection, password is admin, i set password in pgAdmin also admin, but still cannot connect, do you know where is the problem? here, with picture you may understand my error better, thank you
hi, again, me ,i guess i found the solution, i need to create PostGIS extension in the datebase, now it works, thanks again.