The presentation introducing spatial RDBMS can be found here: DSD2014 Postgres_PostGIS.pdf

This page contains basic exercises for working with PostGIS, presented by Frank Keppel and Johan Beekhuizen on November 3th.  

PostGIS is the spatial extension of PostgreSQL. This relational database can be managed with the pgAdmin software tool. PgAdmin offers a graphical user interface (GUI) to work with postgreSQL databases, but the most powerful method of working with the database is by means of SQL scripting. In the following short exercises we mostly work with the GUI as this is easier to start with.

Creating a PostGIS database

  • Start pgAdmin III from Windows start menu. On the left side of the pgAdmin window you 'll see the "Object browser", with "Server Groups" and one server: "PostgreSQL 9.3 (localhost:5432)". 

  • For this exercise we 'll create the database on the local machine (the localhost server). Normally, the database would be on a dedicated server, but for practicing a setup on your local machine is more convenient. Double click on the "PostgreSQL 9.3 (localhost:5432)", then right-click on "Databases" --> "New Database...". 

  • The "New Database..." dialog opens. Here you can set many options, but for this basic exercise we'll stick with most of the defaults and we'll need to give the database a name, such as "dsd". In postgreSQL, the preferred naming convention is to use only lower case letters. Set the name and press "OK". A database is added in the object browser. Double click on the new database to show the contents and properties:
  • As you will see there are quite some properties. We should add the PostGIS extension here. Right-click on "Extensions (1)" --> "New Extension...". A dialog opens where you can set up the extension. Click on the down arrow in the "Name" text box to bring up a list with all installed extensions,  select the postgis extension (see figure below) and click on "OK". The postgis extensions is added to your database and you can now use the postgis functionality


Add shapefiles to the database

Now it is time to add some spatial data to the empty database.

  • Start the plugin: “PostGIS Shapefile and DBF loader 2.1”, which can be found at windows start menu -> PostGIS.
  • Set Connection parameters and press OK (the username might differ for your installation, but is often set to "postgres" as default):

  • If all went well, you should see in the "Log Window" below the Shapefile import window "Connection succeeded". Next, click on "Add File" and select the "WorldCountriesWGS.shp" and "WorldMajorRivers.shp" you just downloaded and unpacked. These shapefiles have been made freely available for educational purposes by ESRI.
  • Set the SRID (Spatial Reference System Identifier) to 4326, which is the WGS84 coordinate reference system used for most global datasets;
    click on the SRID field in the dialog and change the value to 4326:

     
  • Click “Import” to load the shapefiles into the PostGIS database.
  • Let's view the data we just imported; open or start pgAdmin. Our import action created two tables in the database. In order to see the tables we first click on "Schemas" in the Object Browser. A schema is a partitioning mechanism; it is used to organize tables within the database in logical groups. Click on the '+' button next the "Schemas (1)". The new database by default contains a "public" schema with a list of objects. For this exercise we only need to work with the "Tables". The database contains three tables (if not, refresh the database by pressing F5).  
     
  • You can view the data by selecting the table and then pressing the  button. If the table is very large, it might be wise to only view part of the table to avoid loading in all records. Right click on the table in the Object browser, then select “View Data” --> “Show Top /Last 100 rows”.


Import .csv file with point location 

Often, one needs to import spatial data from a text file containing x and y coordinates. Adding these data as a spatial dataset in postGIS is a bit more complicated. We will now show how to add a simple text file containing one location. 

  • Create an empty table in which we will store the text (.csv) file. Right click on "Tables" in the Object Browser and select "New Table...". First set the name of the table, for example "location". Next, click on the "Columns" tab and click on "Add". Add four columns with the following names and Data Types:
    Name: id, Data type: integer
    Name: x, Data type: numeric 
    Name: y, Data  type: numeric
    Name: description, Data type: text 
  • If all went well, you should have the following list of columns:

     

  • Finally, we could add a Primary Key to the table. In a relational database, a primary key is a field (or attribute) in a table that uniquely identifies each row/record, and must therefore contain unique values. In postgreSQL, Primary Keys are added as a constraint. Go to the "Constraints" tab and select "Primary Key" (this will probably be the default) and click "Add". The "New Primary Key..." window opens. Set the Name to "primarykey" and go to the Columns tab. Select the Column "id", click "Add" and press "OK" to close the window and "OK" again to close the "New Table..." window. A new table is added. 

  • Import the downloaded .csv file: right click on the "locations" table, and select "Import". Click on the Browse button to select the downloaded "locationRD.csv". Set Format to "csv" and go to the tab "Misc. Options", where you should set the Delimiter to ";".

  • The contents of the "locationRD.csv" file are added to the location table, but do not contain any geometry yet. In postGIS, geometry is a type of attribute that contains all spatial information, for either polygons, points or lines. It also stores the coordinate system. We will now add a geometry column using two SQL queries. Click on the  button and paste the following code in the SQL editor:
ALTER TABLE location 
ADD COLUMN geom geometry(POINT,28992);
  • Run the code with the  button (or press Ctrl-E). A column is added with a point geometry attribute and an SRID of '28992' (the Dutch 'Rijksdriehoek' (RD) coordinate system in which the coordinates of the location .csv file are stored). Next, the geom column needs to be filled with the coordinates given in the table. Run the following line of code: 
UPDATE location 
SET geom = ST_SetSRID(ST_MakePoint(x,y),28992);
  • With the ST_MakePoint function a point location is created from the x and y columns in the location table, and the SR_SetSRID sets the coordinate system to the Dutch RD system. If you open the table, the geom field is filled with a long list of hexadecimal characters, incomprehensible to most people. You can show the properties of spatial objects in PostGIS in a “Well-Known Text (WKT)” form with the following SQL statement: 
SELECT ST_AsText(geom) 
FROM location;


Basic GIS-analysis in the postGIS database

Now that we have loaded point, line and polygon data in the postgreSQL database we can perform some basic analyses. A good starting point for information on how to work with postGIS is the PostGIS 2.0 manual (http://postgis.net/docs/manual-2.1/). The most relevant parts (for working with vector data) of the extensive manual are:

4: Using PostGIS: Data Management and Queries
Information on the geometry behind spatial objects and on importing spatial data into a postgreSQL database.

8: PostGIS reference.
This chapter contains an overview, explanations and examples of all important PostGIS vector functions.

  • Re-project the point location to WGS84 (which is the system used for the river and country shapefiles) and store the new geometry in a new column 'geomwgs':  
ALTER TABLE location 
ADD COLUMN geomwgs geometry(POINT,4326);
UPDATE location 
SET geomwgs = ST_Transform(geom,4326);
  • This will add a second column containing the transformed coordinates in the WGS84 system (SRID = 4326). Recall that the results can be shown as WKT with: 
SELCT ST_AsText(geomwgs) 
FROM location;

 

Plot data in QGIS

The pgAdmin client does not have (GIS) mapping capabilities. However, you can easily make a connection to the postGIS database with QGIS and analyse the data with this popular open-source GIS desktop software.

  • Start QGIS
  • Click on “Add PostGIS layers”
  • Set up a new connection by clicking on the “New” button.
  • Fill in the Connection Information from your local PostGIS database. You can find the required information in pgAdmin by clicking on the database server in the Object browser. Figure 1 shows an example set of connection parameters. You do not need to define the “Service” and can leave the checkboxes to the default values.

  • Click “OK” when done. A new connection is added. Press the “Connect” button.
     
  • You probably see only “public” in the main window. Press the expand (‘+’) button to show the contents of the postGIS database.

  • Select the “location” (with the SRID of 4326), “worldcountries” and “worldmajorrivers” tables and press “Add” to view the layers in QGIS.

  • The layers are added to the “Layers” list. If you cannot see the rivers or the location, change the order of the layers. The top layers are drawn over the bottom layers, therefore the point shapefile (“location”) should be set on top of the Layers window, followed by the line shapefile (“worldmajorrivers”), and the polygon shapefile (“worldcountries”) should be below.

Change layer style

The three layers are shown but have arbitrary colors as the layer styles have not been set. 

  • Double click on the layer to open the “Layer Properties” window. Go to the “Style” tab. Here you can set the style using an extensive set of options.

  • When you are happy with the style, store the style in the PostGIS database. In the Style window, click on “Save Style” --> “Save in database (postgres)”. Give the style a name and check the “Use as default style for this layer” and press “OK”.

  • Go back to pgAdmin and show the tables of the database. A new table “layer_styles” should be added. Next time when importing the data from the PostGIS database, the layer wil automatically be shown in the right style. The style will also be used when viewing the data with Geoserver.

Extra: more advanced GIS-analysis with PostGIS

If you still have time left, you can try to perform the following postGIS analyses:

  • Find the country in which the location from the location table lies:
SELECT w.*
FROM worldcountries w, location l
WHERE st_within(l.geomwgs, w.geom);

 

  • Create a view based on a spatial query and map the view in QGIS:
      Create a database view named 'rhinecountries' based on a spatial query.
      When connecting from QGIS the view can be added as a layer...
CREATE VIEW rhinecountries AS
SELECT c.*
FROM worldmajorrivers r, worldcountries c
WHERE r.name = 'Rhine'
AND st_intersects(r.geom, c.geom);
  • No labels

Disclaimer