Spatial Databases and Data Management
Software, Connections and Passwords
Table of Contents
Software ………………………………………………………………………………………………………………… 2
PG Admin 4 ………………………………………………………………………………………………………… 2
QGIS 2.18…………………………………………………………………………………………………………… 2
FME …………………………………………………………………………………………………………………… 2
UCL VPN ……………………………………………………………………………………………………………. 2
Connecting to Desktop Anywhere in Birkbeck …………………………………………………………….. 3
Connecting to PG Admin IV ……………………………………………………………………………………… 3
Changing Your Password ………………………………………………………………………………………… 5
Note: you will be using a different username and password to connect to the
CEGE0052 databases – this will take the form userX where X is the number
next to your name on the sign in sheet.
Note: in this and later practicals it is important that you stick exactly to the
schema and table names as they are written, as we will use these to make
sure you are attending practical sessions.
If you get problems, first try and resolve them yourself – you should pay
attention to detail, and check for obvious things like spelling mistakes, spelling
column or table names incorrectly, missing brackets or quotation marks. Look
carefully at the error message from PostGreSQL as this can be helpful – e.g.
it might give you a line number.
If you cannot resolve the problem then ask for help in class or on the forum for
that specific practical (available for up to 2 weeks after the date of the
practical)
Page 2 of 6
Software
We will be using the following software for this module. You have a number of options in
terms of accessing the software:
1. All the software is installed on the UCL PCs in the cluster rooms
2. All the software is also available on the UCL DesktopAnywhere system
(https://www.ucl.ac.uk/isd/services/computers/remote-access/desktopucl-anywhere)
3. You can install the software on your own machine.
NB: To work outside the UCL network you will first need to connect via the UCL VPN system
(see below).
PG Admin 4
All practicals – PG Admin 4 from here (other operating systems also avail:
https://www.postgresql.org/ftp/pgadmin/pgadmin4/v2.0/windows/
QGIS 2.18
Practical 3 onwards – QGIS 2.18 from here: http://qgis.org/downloads/QGIS-OSGeo4W-
2.18.15-1-Setup-x86_64.exe (later versions of QGIS are available but 2.18 is what is
installed on the UCL PCs so the instructions are tailored to 2.18)
FME
Practical 4 onwards – FME Desktop from here: https://www.safe.com/fme/fme-desktop/trial-
download/ (sign up for a trial license but we also have a UCL license server – see below)
If you have downloaded and installed FME, the license server you need is (UCL VPN is
required):
safelm.cege.ucl.ac.uk
UCL VPN
If you are working on your own machine, or working outside UCL, you will also need to use
the UCL VPN system to connect into the UCL Network. This will allow you to connect to the
FME license server and to the database – you can find instructions here:
http://www.ucl.ac.uk/isd/services/get-connected/remote-working/vpn
Notepad ++ or similar
For the practicals, and for your assignment, you should type all the SQL into a text file created
in Notepad++, Sublime Text or another text editor, and then copy/paste it into PG Admin 4
Most operating systems have a text editor installed, but you can download one as follows:
https://notepad-plus-plus.org/
https://www.sublimetext.com/
NB – do not use Microsoft Word as the quotation marks will be incorrect.
Connecting to Desktop Anywhere in Birkbeck
In some cases, we may be holding practical sessions in a cluster room in Birkbeck University.
This means that we first need to connect to the UCL Desktop Anywhere system first, via the
Birkbeck System.
1. Log on to the Birkbeck PC with the following details:
UserID : ucluser
Password : T34ch1ng
2. Enter your UCL details into the Citrix receiver box:
3. Desktop@UCL Anywhere will start and open in full screen. A black screen will
appear for up to 20 seconds.
Connecting to PG Admin IV
We are using a database called PostgreSQL for all the following exercises. PostgreSQL has
been in development for more than 15 years and is open source – in other words, experts
from around the world can add additional functionality to the database and share this with
other users. It supports standard database data types (integers, text, dates and so forth) and
conforms to the ANSI:SQL 2008 standard.
Note:
Note that the database that you will use for these in-class exercises and for your assignment
is hosted on a central server. This approach allows you to share data amongst multiple
people (although in this case you don’t have rights to each-other’s data!).
Starting PG Admin 4 – Using the UCL System
1. If you are using the UCL system, log on to the Desktop Anywhere system by following
the instructions you will find here (select the instructions for your operating system):
https://www.ucl.ac.uk/isd/services/computers/remote-access/desktopucl-
anywhere/desktopucl-anywhere-how-to-guides
Page 4 of 6
2. Logging in might take some time. Once logged in, you can start PG Admin 4
Starting PG Admin 4 – Installed on Your Machine
1. If you have installed PG Admin 4 on your own machine, it is likely that this will run
inside a browser.
2. Open a browser (e.g. Edge, Chrome) and go to: http://127.0.0.1:49714/browser/
(NB: port number and URL might be different on your machine –make a note when
you first do the installation)
Connecting to Your Database
You will need your databases user number – this is the
number by your name on the sign-in sheet
1. Once you start PG Admin, click on OBJECT > CREATE > SERVER. You will see the
following interface (left hand side):
2. Name your connection ucfsxxx (use your UCL user ID as the connection name) and
then click on the CONNECTION tab (right hand figure above), and enter the following
details:
a. Host name/address: developer.cege.ucl.ac.uk
b. Port: 33029
c. Maintenance database: userXXdb (where XX is your user number – e.g.
user5db, user27db)
d. Username: userxx (where xx is your user number e.g. user5, user23)
e. Password: userxxpassword (where xx is your user number e.g:
user5password, user72password)
f. Save password: check this box
3. Click SAVE
4. Double-click on the connection you have just created
5. You will now see a list of databases, table spaces (which link to the files on disk that
make up the database), group roles and login roles (a role is how you set privileges
on the database – in other words, what data a user can see, can edit and whether the
user can create new tables or not)
6. Double click on the Databases list, and you will get the following screen, which should
show a list of databases. We will be using the userXXdb database, where XX refers
to your user number.
7. Double click on your database (userXXdb) and then navigate down through the
options as follows:
userXXdb > Schemas > public > Tables
This will give you a list of any tables that are currently stored in the public schema in
the database.
8. Double-click on each of the tables to explore their structure. PostgreSQL gives you a
description of each of the columns (fields) in the table, and you can also see any
constraints that have been added (such as primary keys and foreign keys). (Your
screen may show schemas and tables that are different to the one shown below)
Changing Your Password
In order to ensure that people don’t connect to your database by mistake you should change
your database password. (NB: You only need to do this ONCE – you can then use your new
password for the remainder of the module.)
Make sure you make a note of your password as you will
need it every week and I am not able to change
passwords very easily
1. Click on TOOLS > QUERY TOOL
Page 6 of 6
2. In the top right hand box, type the following SQL (adapt for your user details
and your own password)
ALTER USER user1 PASSWORD ‘newpassword’;
3. Run the SQL query by pressing F5
4. Once you have changed your password, fill in the survey here:
https://moodle-1819.ucl.ac.uk/mod/feedback/view.php?id=812205
(NB: your password will not be shared with other students but I will use it to check
your progress through the module)
5. The next time you log in you will need to use your new password