Introduction to PgAdmin and Databases¶

PgAdmin is one of the most popular, open-source tool for PostgreSQL/PostGIS. It is used as an administrative and development inteface to interact with the Postgres Databases. For more information, you can visit pgAdmin’s official website. PgAdmin is available as a stand-alone software tool or as a part of the package with PostgreSQL. Since it is open-source, you can download and use it on your own systems or use the version installed on the PCs in the Lab.

The pgAdmin Interface¶

PgAdmin is a light-weight tool. The landing page when you open the tool is the ‘Dashboard’ as seen in the following image. In the Dashboard, you have the links to the documentation on the various aspects of pgAdmin and the world PostgreSQL. The documentation details everything about PostgreSQL based on the version installed in your systems.

Getting Started - Connecting to a server¶

In the above image, you will see the ‘Object Explorer’. In this, you will see the list of servers connected. To connect a new server, right-click on the ‘Servers’ > Register > Server as shown in the following image.

When registering a server, especially a remote server, the following parameters need to be set.

Name: This is to identify your server. Ýou can it any name of choice.

Host name/address: This is the location where the server instance is running. To test out connection and settings. You can test out by connecting to the default ‘localhost’.

Maintanace database: This is the name of the Database running on the server you specified in the above Host name. For testing purposes, you could leave it with the default - postgres.

Username & Password: These are self-explanatory. If it is a local repository, you can use the default or set the values as per your choice.

Note

For the potential Exercise concerning the 3DCityDB, we will be using a remote server which alread has an instance of 3DCityDB v5 running on it. The above details then become crucial for establishing a connection to the server. All of the abovementioned details will be provided to you when the exercise tasks are posted. For more information on the 3DCityDB, please see the link here

For the tutoring session you can use the following details

For the tutoring session you can use the following details

 Hostname: 10.162.246.20

 Port: 5438

 Maintanace Database: citydbv5

 Username: student

 Password: ssme_2025

Once the parameters are validated and a connection is established, the database (defined by you) will automatically appear in the Sever list in the Object Explorer CityGML LoD2 test data from TUM is already uploaded to the instance. It is also given here for your use if you are using personal systems.

Alternatively, you can use the following credentials if the above connection times out.

Alternate Credentials - Port:5439

 Hostname: 10.162.246.20

 Port: 5439

 Maintanace Database: citydbv5

 Username: student

 Password: ssme_2025

Warning

Please make sure to check the hostname, port number and other details properly. Even a small error will lead to a failed connection.

Undetstanding the Database Structure¶

The Database is an organised hierarchically structured system for data storage and maintanace. The structure is generally like this - Server > Database > Schemas > Tables

Important things to know within the Database

Extensions: These are packages that can extend the capabilities of POstgreSQL for application specific functions. Some common extensions can be found in this link here. There are more and you can find specific extension by googling them. Extensions for PostgreSQL are similar to packages in the Python. PostgreSQL is the fundamental core and the Extensions strengthen and extend capabilities in a certain direction. For example, PostGIS is a powerful and popular extension to PostgreSQL that provides geographical and geometrical capabilities to database systems. In a new database, extensions are often not installed and need to be manually installed.

Note

We will be using PostGIS heavily in our exercises. And when using 3DCityDB, PostGIS is automatically installed on the instance but this would not be the case for other schemas. Please check if this is installed as the “Geometry” functions will not work without PostGIS

Schema: This is the structure of the RDBMS explained in a formal language. It is like the Masterplan or Blueprint. The default Schema for any database is the public schema. If you have bespoke schemas, they will be listed under the ‘schemas’ under your connected database.

Note

When you connect to the 3DCityDB instance, you will find the ‘citydb’ schema automatically listed. This contains all the tables which are mapped from the CityGML files.

Functions - This will list all the fuctions that are possible in the database. These will automatically include the functions from the installed extensions. You can also create your own functions which will then appear here.

Tables - This is the most important thing to know since the tables contain all the information that you will need. Under the tables, you will find additional parameters such as Columns, Constraints, Indexes, etc. These will give you a quick look at the structure of the table.

Writing SQL Queries¶

Open the ‘SQL Editor’ to type in your SQL codes.

Warning

Your database has to be selected before the Query tool becomes available. Please click on the database you created/connected to before you try to open the editor.

SQL Test Queries¶

Select all features from a table matching a particular attribute!

--Select all features that have their address in Arcisstr.!

SELECT count(a.street) FROM address a WHERE a.street LIKE '%Arc%'

--Select all features with their Feature ID, Object ID, Name and Geometries

SELECT
  f.id AS feature_id,
  f.objectid,
  p.val_string AS property_value,
        gd.geometry
FROM
  feature f
JOIN
  property p ON p.feature_id = f.id
JOIN
  geometry_data gd ON gd.feature_id = f.id
WHERE
  p.name = 'name';