Page 1 of 16
Table of Contents –Spatial DDL and DML
Part 1 – QGIS and PostGIS …………………………………………………………………………………………. 2
Spatial References and EPSG Numbers ……………………………………………………………………. 2
Exercise 1 – Familiarisation with QGIS and PostgreSQL/PostGIS ………………………………… 2
Exercise 2- Importing Spatial Data into PostgreSQL ……………………………………………………. 8
Part 2- Data Management: Spatial Data ……………………………………………………………………… 13
Exercise 1 – Spatial Data DDL and DML ………………………………………………………………….. 13
Note: As Quantum GIS is Open Source, it tends to get updated
very frequently. These updates often include interface changes
which may move the menu options around. If you don’t find the
menu you need, spend a little time searching through the
interface options until you find the required setting.
Note: QGIS 3.x is the latest version, but all practical notes are
designed for 2.18 as this is the version that UCL has installed
on its PCs
Page 2 of 16
Part 1 – QGIS and PostGIS
Spatial References and EPSG Numbers
The European Petroleum Survey Group (EPSG) number represents an internationally accepted
code for projections (also known as coordinate reference systems). This can often be found
associated with a Spatial Reference ID (SRID) that is also widely used but may vary from GIS
to GIS, so where possible you should use the EPSG code (e.g. when importing data from QGIS
into PostgreSQL).
For Open Street Map datasets, as the data is collected using a GPS, the EPSG code
required is: 4326 (WGS 84)
For data from the Ordnance Survey in the UK, the data is in British National Grid,
which has an EPSG of 27700
Projections/Coordinate Reference Systems in QGIS:
In QGIS, you need to set a default project projection before you add any data to the project.
This will ensure that your data is shown correctly on the map. Exercise 1 will show you
how to do this.
Exercise 1 – Familiarisation with QGIS and
PostgreSQL/PostGIS
QGIS (Quantum GIS) is a free, open-source Geographical Information System which can be
used to view, edit and analyse spatial data in multiple formats including ArcGIS shape files
and MapInfo Tab files. It can also link directly to spatial data stored in PostgreSQL and
display the data on a map. This is in contrast to the current version of ArcGIS (9.3) which
requires an ArcSDE installation or a Data Interoperability license to view PostgreSQL spatial
data.
NB: For this module you are not expected to become QGIS or FME (see later weeks) experts
– you just need to know how to connect to a database and display some map data.
1. Open QGIS. You will get the following basic interface:
Page 3 of 16
A list of layers will appear on the left hand side in the Layers Panel (sometimes at the top,
sometimes at the bottom), with the map taking up the main bulk of the screen. A number of
standard GIS tools are available on the tool buttons, as well as options to connect to various
databases. Each button has an associated tool tip which pops up when you hover over the
button, to tell you what the button does.
2. We start by creating a new project using PROJECT > NEW PROJECT. We must
also set the default projection for the data in this project. As we will be using data
from the United Kingdom, select the following options (see later on for a discussion
about projection settings):
a. Click on PROJECT > PROJECT PROPERTIES
b. Click on COORDINATE REFERENCE SYSTEM (or CRS) to set the default
projection
c. For UK data, select the OSGB 1936/British National Grid (EPSG 27700)
option from the list (you can type 27700 in the filter to find this easily).
d. Make sure that the box marked ‘Enable on-the-fly CRS transformation’ is also
checked. This will allow you to work with datasets in different projections.
e. Now click OK.
f. Save your project as practical_3.QGS
3. Now that we have created the project, we can add some data from our
PostgreSQL database and using this to gain a basic understanding of QGIS.
Create a connection to your database using the LAYER > ADD LAYER > ADD
POSTGIS LAYER option
Page 4 of 16
4. Click NEW to create the new connection
5. Enter your PostgreSQL connection details (developer.cege.ucl.ac.uk, the correct
port, database and username and password details)
Page 5 of 16
6. Save the username and password, then click OK to connect. You will get a warning
as shown below – click OK again
7. Click on the LAYER > ADD POSTGIS LAYER menu option and click CONNECT.
You will get a dialog similar to the following (NB: perhaps the table/layer list may be
slightly different):
Page 6 of 16
8. This interface can be used to connect to the database as follows:
a. If necessary, click on the CONNECT button and enter the password for your
database OR CHANGE THE PARAMETERS AS REQUIRED. If you do not
have an existing connection, you can create one using the NEW button.
b. Once you have done this, you will be given a list of map layers that are
available in your database.
c. From this list, select london_counties and click Add. The layer will be added
to your QGIS map.
9. Now spend some time exploring QGIS. Some example operations are as follows:
a. To zoom and pan around the map, use the tools similar to the following (the
exact appearance may change between QGIS versions), which allow you to
pan, zoom in, zoom out, zoom to extent, zoom to selection, zoom to layer, go
back to the previous zoom and refresh the map.
b. To change the display style of the map layer, double-click on the layer name.
A layer properties dialog will appear as shown below.
c. Click on style to change the display style.
Page 7 of 16
Edit data in QGIS and see the change in PostgreSQL
10. To edit a layer, right-click on the layer in the Layers list and select toggle editing.
Additional menu buttons will appear and your map display will change.
a. To view the attributes of the data, right-click on the layer and select OPEN
ATTRIBUTE TABLE. Note that if you do this while editing is switched on, you
will be able to edit the attribute data.
11. Right-click on the london_counties layer and select EDIT. Open the attribute table
(right click on the layer and select the london_counties attributes layer to change the
name of one of the counties by adding XXXX to the text. (Remember to enable the
editing options, then open the attribute table, and then save your changes by
switching off the editing option.)
12. Now go to the pgAdmin tool and view the data in PostgreSQL. Has the change been
saved?
Edit data in PostgreSQL and see the change in QGIS
13. Create a text file called practical3.txt for all your SQL queries this week.
14. Edit the data in PostgreSQL using an SQL update statement (remember that the
SCHEMA is public this time). Do you see your changes in QGIS? Do you need to
reload the data to see the changes?
15. (Optional task) Clicking on PROJECT > PRINT COMPOSER opens a layout window
which you can use to create printed maps. This offers options including the addition
of a scale bar to the map, adding a legend and exporting the resulting map to a PDF
or image. NB: creating a professional looking map is not required for your
assignment – a screenshot of your data is sufficient.
Page 8 of 16
Exercise 2- Importing Spatial Data into PostgreSQL
Spatial data is often stored in shapefiles (which are the GIS equivalent of word documents or
CAD files or excel spreadsheets). However, as we saw in week one, data in files is more
difficult to manage than data stored in a central database, as shapefiles can be stored on
local hard drives, easily deleted, easily copied (which means that you then miss any changes
to the original) and are not designed to be used by multiple users at the same time.
In this exercise, we will see how to import shapefile data into the PostgreSQL database,
where it can then be more easily backed up and shared with multiple users. Having the
spatial data stored in the same database as non-spatial data also allows queries to be run
that combine both types of datasets.
NB: You can import any spatial data into your database, and QGIS can work with all of the
common spatial data formats.
Make sure you name the imported layers as follows:
– uk_counties_subset
– uk_highway_subset
– uk_poi_subset
1. Make sure you are connected to PostGIS by opening a layer from your database.
2. Open the DATABASE > DB MANAGER > DB MANAGER tool.
3. Select POSTGIS and then select your connection from the list and double click – you
should see the PUBLIC schema and any other schemas you have created.
Page 9 of 16
Page 10 of 16
4. Click on a schema and you will see your tables
5. To import data, select the DOWN ARROW (Import Layer File) option
Page 11 of 16
6. Select the shapefile you need, and set the other details as follows (NB – be careful
about the SRID for the COUNTIES data as it is different):
Page 12 of 16
7. Click OK and wait for the import to run – this may take a while!
8. Repeat the above for the uk_poi_subset (SRID 4326) and the uk_counties_subset
(SRID 27700)
Page 13 of 16
Part 2- Data Management: Spatial Data
Exercise 1 – Spatial Data DDL and DML
The following exercise will guide you through the creation of spatial tables from scratch in
PostgreSQL/PostGIS and will show you how to insert simple data into the tables using the
Well-Known-Text (WKT) method of representing data.
Note that for this exercise we are assuming the data is in British National Grid, using the
Coordinate Reference System 27700.
1. Continue to use the practical3.txt file you created above for all the SQL in this part of
the practical
2. In pgAdmin create a new schema called practical3
3. Type the following SQL into the SQL Editor Pane:
CREATE TABLE practical3.spatial_table_points(id serial, name
varchar);
Execute the query by pressing F5, or clicking on the Execute Query Icon.
4. If you refresh the pgAdmin window database view, you will see that a new empty
table was added to the database. We now need to spatially enable the table by
adding a geometry column.
5. Empty the SQL Editor Pane, then write the following query into your text file and
copy/paste into the editor:
SELECT
AddGeometryColumn(‘practical3′,’spatial_table_points’,’the_geom
‘,’27700′,’POINT’,2);
6. Run the query, then refresh the table view to see the geometry column added to the
table. In this case, we instructed the database to add to the table “spatial_table” a
“the_geom” column which will hold spatial objects in “27700” SRID, aka British
National Grid.
Note: The column will only hold “Point” objects, recorded in “2” dimensions (PostGIS
allows to record objects in 3 dimensions if needed!). This is a CONSTRAINT
Page 14 of 16
7. To add a point object to our table, we need to run the following query:
INSERT INTO practical3.spatial_table_points (name,the_geom) values
(‘Jeremy Bentham’,st_GeomFromText(‘POINT(529440 182165)’, 27700));
Here we instruct PostGIS to insert into our table a new value (or row), with a ‘id’ of 1,
a ‘name’ value of ‘Jeremy Bentham’ , and add a point geometry to our ‘the_geom’
column. The GeometryFromText function returns a specified ST_Geometry value
from a Well-Known Text representation (WKT). In this case, a Point with the specified
British National Grid coordinates.
To check if the query executed, go to the table in the Admin Console, right-click and
click View Data -> View Top 100 Rows. One row with the Jeremy Bentham entry
should pop up.
Alternatively, you can run the following query in the Query Editor:
Select * from practical3.spatial_table_points;
8. Repeat the task above for the following points:
id Name Coordinates
2 Euston Square 529448 – 182361
3 UCL Science Library 529639 – 182146
9. To insert line data, you need to first create a table that can hold such data, as follows:
CREATE TABLE practical3.spatial_table_lines(id serial, name
varchar);
SELECT
AddGeometryColumn(‘practical3′,’spatial_table_lines’,’the_geom’
,’27700′,’LINESTRING’,2);
10. You can then insert lines using text similar to the following:
INSERT INTO practical3.spatial_table_lines values
(‘1′,’University Street 1’,st_geomFromText(‘LINESTRING(529370
182097, 529461 182160)’, 27700));
(Note the use of the ST_GeomFromText option, and the term
LINESTRING in this case.)
11. Similarly, for polygons, use:
CREATE TABLE practical3.spatial_table_polygons(id serial
PRIMARY KEY, name varchar);
Page 15 of 16
SELECT
AddGeometryColumn(‘practical3′,’spatial_table_polygons’,’the_geom’
,’27700′,’POLYGON’,2);
12. Add data to the polygon table as follows:
INSERT INTO practical3.spatial_table_polygons values (‘1′,’DMS
Watson Library’,st_GeomFromText (‘POLYGON((529611 182164,
529635 182131, 529653 182145, 529629 182177, 529611 182164))’,
27700));
13. Create a new table in the practical3 schema, called polygons_no_crs with the same
columns as the previous polygon table.
14. Add a geometry column to this table, but this time put the SRID as 0 (i.e. we don’t
have a coordinate reference system).
15. Write the INSERT statements to insert the three polygons we listed the coordinates
on the slides in class.
16. Once you have completed this exercise, open the tables you have created in QGIS to
view the data you have inserted.
NB: When you open the polygons_no_crs data you will be asked to assign a coordinate
reference system. The easiest thing to do here is to assign British National Grid – 27700 –
and then your data will appear off the south west of the UK1.
You can test this out by adding a map of the UK:
1. Click LAYER > ADD LAYER > ADD WFS2 LAYER
2. Click NEW
3. Call the connection UK_WFS and use the URL:
https://ons-
inspire.esriuk.com/arcgis/services/Electoral_Boundaries/European_Electoral_Region
s_December_2017_Boundaries/MapServer/WFSServer?
1 The more correct thing to do would be to define your own coordinate system – but this is not
something we will cover on this module and you are not expected to do this for your
assignment.
2 A WFS is a Web Feature Service – this is a standard way of sharing map data over the
internet. This is one service of 1000s available worldwide.
Page 16 of 16
4. Click OK
5. Click CONNECT to connect to your new connection
6. Select the first layer from the resulting list and click OK
7. Now zoom to the polygons_no_crs layer by right-clicking on the layer and selecting
ZOOM TO LAYER
8. Very slowly, zoom out (use the mouse wheel to make this easier) – your polygons will
disappear as they get smaller, but the islands off the south west coast of the UK will
appear
9. If you want to see the polygons, edit the style to change the outline width to around
5.0