Skip to content
Permalink
Branch: master
Find file Copy path
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
612 lines (432 sloc) 17.5 KB

Workshop PostgreSQL/PostGIS for beginners

FOSS4G 2018 Workshop Dar Es Salaam (Tanzania)

Workshop was friendliy supported by Lars Lingner @mapwebbing and Felix Kunde @flxku

Astrid Emde

What we learn

  • Why database?
  • PostgreSQL / PostGIS
  • SQL Basics
  • Import data / Export data
  • Spatial Functions
  • Spatial Index
  • Roles & handle access to your data

OSGeoLive

This Workshop uses the bran new OSGeoLive (https://live.osgeo.org) Version 12.0 (August 2018). OSGeoLive is based on Lubuntu and contains a collection of more than 50 pre-installed software projects. OSGeoLive also contains example data which will be used for the workshop.

You can download OSGeoLive with the following link. You can install OSGeoLive, run it in a virtual machine (recommended) or use it on an USB stick.

actual Software Versions

OSGeoLive 12.0

  • PostgreSQL 10.4
  • PostGIS 2.4.3
SELECT version(), postgis_version(), postgis_full_version();

Data

  • Natural Earth
  • as ESRI Shapes - countries, provinces, rivers, populated places & more at /home/user/data
  • database: natural_earth2
  • OpenStreetMap
  • database: osm_local

additional information

Why database?

  • central storage of data - no data redundancy
  • consistency of data
  • multi-user access
  • restricted access via access control and access management
  • access your data via different tools
  • combine different data and use SQL to explore and analyze
  • backup, replication ...

PostgreSQL

  • supported by several other programs
  • fast, powerful, reliable, robust
  • easy to maintain
  • follows SQL standard
  • API to many programming languages
  • subselects, functions, trigger, Foreign Data Wrapper, replication & more
  • https://www.postgresql.org/about/

PostGIS

  • Extension for PostgreSQL
  • let PostGIS do the work - not your Desktop GIS
  • Follows standard - OGC Simple Feature Spezification for SQL and OGC ISO SQL/MM Spezification
  • Provides many spatial functions
  • Widley supported by other programs
  • Easy import / export of spatial data (QGIS, shp2pgsql, pgsql2shp, ogr2ogr, dxf2postgis, osm2pgsql)
  • Can use the advantages from PostgreSQL (user management, replication, indexing & more)
  • Very powerful: vector & raster data, geometry (planar) and geography (spheroid), circular objects, 3D, 4D, point cloud, pg_routing for routing, topology
  • Stores data as WKB (Well-known Binary) and displays it as WKT (Well-known text)
  • http://www.postgis.org/
  • http://postgis.net/docs/manual-2.4/

Database Clients

Excercise 1: pgAdmin & first steps in the database

  1. Open pgAdmin
  2. Connect to database natural_earth2
  3. Go to schema public and look for tables
  4. Open a table and look for the geometry column. Can you read the geometry?
  5. Open table spatial_ref_sys and filter by srid = 4326
  6. Go to schema public -> views and open the metadata view geometry_columns

How to communicate with the database?

  • via SQL - Structured Query Language
  • DDL - data definition language
  • DML - data manipulation language
  • DQL - data query language

DQL

  • DQL - to query your data (DQL is part of DML ) f.e to show all data from table spatial_ref_sys with srid = 4326
SELECT * FROM spatial_ref_sys WHERE srid=4326;

DDL

  • DDL to create database, table, user, schema and more
CREATE DATABASE demo;
CREATE TABLE pois(
 gid serial PRIMARY KEY,
 name varchar,
 year int,
 info varchar
);

Modify your table

ALTER TABLE pois ADD COLUMN land varchar;
ALTER TABLE pois RENAME land TO country;
ALTER TABLE pois DROP COLUMN country;
ALTER TABLE pois ADD CONSTRAINT pk_gid PRIMARY KEY (gid); 

Delete your table

DROP TABLE pois;

DML

  • manipulate your data - create data, delete data, change data
INSERT INTO pois (name, year, info, geom) VALUES 
(
'Kölner Dom',
1248,
'https://en.wikipedia.org/wiki/Cologne_Cathedral'
);
DELETE FROM pois WHERE name = 'Kölner Dom';
DELETE FROM pois; -- deletes all data
DELETE FROM pois WHERE gid = 1111;
UPDATE pois SET name = 'Cologne Cathedral' WHERE name = 'Kölner Dom';

Excercise 2: Create your own database with PostGIS extension

  1. Create your own database with the name foss4g

Notice: Use lower case and no spaces as name for your database, tables columns! Makes live easier. As you do not have to use quotations - like "FOSS4G"

  1. Change the connection and connect to your new database.

  2. Load the extension postgis to your new database to be able to handle spatial data.

  3. Check whether the postgis functions, spatial_ref_sys table and the metadata views are there

CREATE DATABASE foss4g;

CREATE EXTENSION postgis;

Excercise 3: createdb via command line

createdb -U user demo
createdb --help

psql -U user demo
CREATE EXTENSION postgis;

Excercise 4: Create your own table cities

CREATE TABLE cities(
 gid serial PRIMARY KEY,
 name varchar,
 country varchar,
 geom geometry(point,4326)
);
INSERT INTO cities(
            name, geom, country)
    VALUES ('Dar es Salaam',ST_SetSrid(ST_MakePoint(39.273933, -6.812810),4326),'Tanzania');
INSERT INTO cities(
            name, geom, country)
    VALUES ('Cologne',ST_SetSRID(ST_MakePoint(6.958307 , 50.941357),4326),'Germany');

QGIS to visualize your data

  • You can visualize, edit and import/export data from a PostgreSQL/PostGIS database
  • You need the information how to connect to the database - only authorized users can connect

Excercise 5: Load data from natural_earth2 and from your new database

  1. Load countries from the database natural_earth2
  2. Create a new PostGIS connection to your new database foss4g
  3. Load your new table cities
  4. Add a new point to your cities table and mark the place where you come from (approximately)

QGIS import data to PostgreSQL via QGIS DB Manager

You can use the QGIS DB Manager to import/export data to/from your database. You find the QGIS DB Manager in the menu at Database -> DB Manager. You already need a connection to the PostgreSQL database that you would like to use.

Best way is to add the data you would like to import to a QGIS project. You can filter the data if you only want to import a subset of your data.

  • Open the DB Manager
  • Connect to your database
  • use the Import layer/file button
  • choose your data
  • define a name for your table, define the SRID, add a primary key (gid recommended)
  • Create a spatial index
  • start the import
  • add the imported data via drag & drop to your QGIS project

Excercise 6: Load data from natural_earth2 shapes to your database

  • import ne_10m_admin_0_countries.shp to table ne_10m_admin_0_countries
  • /home/user/data/natural_earth2/ne_10m_admin_1_states_provinces_shp.shp to table ne_10m_admin_1_states_provinces_shp
  • also import provinces and only import the provinces from Tanzania to table provinces_tza (use Filter admin='United Republic of Tanzania')
  • import all ne_10m_populated_places to table ne_10m_populated_places
  • have a look to your metadata view geometry_columns

Get to know PostGIS functions

ST_AsEWKT or ST_AsText - to display the geometry as text

SELECT ST_AsText(geom) FROM cities; -- without SRID
SELECT ST_AsEWKT(geom) FROM cities; -- with SRID
SELECT ST_AsEWKT(geom) FROM provinces_tza;

Geometry Constructors

ST_GeomFromText - can be used for different geometry types

Update cities set geom = ST_GeomFromText('POINT(6.958307 50.941357)',4326) where name = 'Cologne';
Update ne_10m_admin_0_countries set geom = ST_GeomFromText('MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)))',4326) where name = 'Germany';

Spatial Relationships and Measurements

  • get information about your data f.e. distance, area, length, centroid

Excercise 7: Calculate the area for each country

SELECT gid, name, st_Area(geom)
  FROM public.ne_10m_admin_0_countries;
SELECT gid, name, st_Area(geom, true)
  FROM public.ne_10m_admin_0_countries;
SELECT gid, name, st_Area(geom, true)
  FROM public.ne_10m_admin_0_countries
  WHERE name IN ('Germany','Tanzania');

Excercise 8: Create a view with the centroid for each country

  • Create a view with the centroid for each province
  • try to load the view in QGIS
  • have a look at your geometry_columns view
Create view qry_country_centroid AS
SELECT gid, name, st_centroid(geom)
  FROM public.ne_10m_admin_0_countries;

Recreate the view and typecast your geometry column to point

Drop view qry_country_centroid;
Create view qry_country_centroid AS
SELECT gid, name, st_centroid(geom)::geometry(point,4326) as geom
  FROM public.ne_10m_admin_0_countries;

Use the function ST_PointOnSurface

Create view qry_country_pointonsurface AS
SELECT gid, name, st_pointonsurface(geom)::geometry(point,4326) as geom
  FROM public.ne_10m_admin_0_countries;

Excercise 9:

  • get back to your cities table from excercise 4. Calculate the distance between Dar es Salaam and your home town.
  • use the spheroid for your calculations (use geography)
  • https://postgis.net/docs/ST_Distance.html
SELECT g.name, you.name, ST_Distance(g.geom, you.geom,true) 
FROM cities g, 
cities you 
WHERE 
g.name = 'Dar es Salaam' 
AND you.name='Cologne';

Spatial Index and functional Index

  • Your geometry column should have an index - makes spatial queries faster
  • the bounding box for every geometry will we stored in the index
  • you also can create a functional index - f.e. with ST_Transform
CREATE INDEX gist_cities_geom
ON cities 
USING GIST (ST_Transform(geom,25832));

Geometry Processing

Exercise 10: Buffer populated places with 10 km

CREATE TABLE places_buffer_10_km as
SELECT gid, name, ST_Buffer(geom::geography, 10000)::geometry(polygon,4326) as geom 
  FROM public.ne_10m_populated_places;

```sql
SELECT a.* 
  FROM places_buffer_10_km a, places_buffer_10_km b
  WHERE a.geom && b.geom 
  AND st_intersects(a.geom, b.geom) 
  AND a.gid != b.gid
CREATE INDEX gist_places_buffer_10_km_geom
ON places_buffer_10_km 
USING GIST (geom);

Run the query again and check whether the index is used.

SELECT a.* 
  FROM places_buffer_10_km a, places_buffer_10_km b
  WHERE
  st_intersects(a.geom, b.geom) 
  AND a.gid != b.gid

Exercise 11: Union all provinces from country Tanzania to one area

  • create a view called qry_tanzania_union
  • use ST_UNION http://postgis.net/docs/manual-2.4/ST_Union.html
  • use table ne_10m_admin_1_states_provinces_shp and filter by admin Tanzania
  • add column admin to your view (admin='United Republic of Tanzania') - you have to use GROUP BY
  • typecast the geomety column
  • have a look at your result with QGIS
SELECT ST_UNION(geom)
  FROM public.ne_10m_admin_1_states_provinces_shp 
  WHERE admin='United Republic of Tanzania';
SELECT 1 as gid, 
  admin, 
  st_AsText(ST_UNION(geom))
  FROM public.ne_10m_admin_1_states_provinces_shp 
  WHERE admin='United Republic of Tanzania'
  GROUP BY admin ;
CREATE VIEW qry_tanzania_union AS
SELECT 1 as gid, 
  admin, 
  ST_UNION(geom)::geometry(multipolygon,4326) as geom
  FROM public.ne_10m_admin_1_states_provinces_shp 
  WHERE admin='United Republic of Tanzania'
  GROUP BY admin ;

ST_Subdivide

CREATE TABLE provinces_subdivided AS 
  SELECT name, admin, st_subdivide(geom) AS geom
  FROM ne_10m_admin_1_states_provinces_shp ;

ALTER TABLE provinces_subdivided ADD COLUMN gid serial PRIMARY KEY;
  • with definition of max_vertices (default is 256, not < 8)
DROP TABLE provinces_subdivided;
CREATE TABLE provinces_subdivided AS 
  SELECT name, admin, st_subdivide(geom,20) AS geom
  FROM ne_10m_admin_1_states_provinces_shp ;

ALTER TABLE provinces_subdivided ADD COLUMN gid serial PRIMARY KEY;
CREATE INDEX provinces_subdivided_the_geom_gist
  ON provinces_subdivided
  USING gist
  (geom);

VACUUM ANALYZE provinces_subdivided;

Example 12: ST_Subdivide

  • sometimes it makes sense to divide huge geometries in smaler parts for calculations
  • this example should show the use
  • Create a new function getCountrynameSubdivided() for table provinces_subdivided
  • have a look at EXPLAIN to check the performance
CREATE OR REPLACE FUNCTION getCountrynameSubdivided(mygeometry geometry) 
 RETURNS character varying 
 AS 'SELECT c.name FROM provinces_subdivided c WHERE st_intersects(c.geom,$1);' 
LANGUAGE 'sql'; 
SELECT getCountrynameSubdivided(geom) 
 FROM public.ne_10m_populated_places 
 WHERE gid < 5;
ALTER TABLE ne_10m_populated_places ADD COLUMN countryname varchar;
UPDATE ne_10m_populated_places SET countryname = getCountrynameSubdivided(geom);

PostgreSQL Roles and controlled access

PostgreSQL allows you to create roles (user with login and user without login). This roles can have different power and get get access via GRANT to different objects of your database - f.e a table.

Example 13: Create roles and grant access

  1. Create a role workshop_read and workshop_writer
  2. Create a login role robert with a password and add to workshop_reader
  3. Create a new login role wilma and add wilma to the workshop_writer role
  4. Grant read access to table ne_10m_admin_1_states_provinces_shp to your new role workshop_reader
  5. Grant write access to table cities to your new role workshop_writer
  6. Try to access and edit via QGIS
CREATE ROLE workshop_reader;
CREATE ROLE workshop_writer;

CREATE ROLE robert WITH LOGIN PASSWORD 'foss4g';
GRANT workshop_reader TO robert;

CREATE ROLE wilma WITH LOGIN PASSWORD 'foss4g';
GRANT workshop_writer TO wilma;

GRANT SELECT ON ne_10m_admin_1_states_provinces_shp TO workshop_reader;
-- change to user robert
Select * from ne_10m_admin_1_states_provinces_shp;

GRANT ALL ON cities to workshop_writer;
GRANT USAGE ON SEQUENCE cities_gid_seq TO workshop_writer;

-- change to user wilma in pgAdmin
-- Run the following SQL
SELECT * from cities;
UPDATE cities SET name = 'TEST' WHERE name = 'Dar es Salaam';
You can’t perform that action at this time.