Database, oh my database...

You should know by now... PostGIS is a database (ok a database extension, but you've got the point). In order to work with PostGIS (and PostgreSQL), we need to know a bit more about how a database works. I'm not going into detail, so if you know how a database works, good for you. If you don't know anything, I suggest you to learn from other sources. I'm going to explain it very quickly and in a very simplistic way, just to going ahead.

A database is a collection of data, and they are usually grouped on tables. A table have different columns, that have a name and a type. For example, a table that should save data for an user can have following columns:

• name
• surname
• birth date
• sex

If we want to create a table in a database we need use a language that database engines understand. The most common is SQL (Structured Query Language) that is a declarative language. We specify what we want to do.

For creating a table representing an user with attributes that we declared, we can write a SQL query like this one:

CREATE TABLE users
(
name       VARCHAR (50),
surname    VARCHAR (50),
birth_date DATE,
sex        CHAR (1)
);

We can see that we have the CREATE TABLE command, that is the one that we must use in order to create a table (really?), followed by a list of column names with their types. The name column is a string with a variable length, up to 50 characters, while birth_date is represented by a date, and so on.

If we want to add an user in the table, we can also use the SQL language. For example:

INSERT INTO users(
 name, surname, birth_date, sex)
 VALUES ('Phil', 'Lancet', '11-9-1962', 'm');

We have a table with different rows. A row is also identified as a record, while a column is also referred as an attribute.

If we want to specify the work of an user, we can add work fields in the table. But work can have different fields, like the name, the salary, a description and so on. If two people have the same work, all these data are duplicated. This is a waste of space, but there's more. If we have to change, for example, the description of a work, we need to change every occurrence of the description. This can lead to many errors if things are not threated carefully. Instead a database allow us to handle duplicated data in a better way.

If we need to specify a work, we can create a new table called works, and add data to this table. For example, this can be a table with work data:

The query for the creation of this table can be:

CREATE TABLE WORK
(
label       VARCHAR (50),
description text,
salary      DOUBLE PRECISION
);

Now we have two tables, and we need to link them together, so we can know the work of a specific user. Here is where relations came into account. Is possible to create relation between tables.

At first, we can see that, in the work table, the label must be unique for every work. We cannot have two different works with the same name. We can (and must) enforce the uniqueness of these values making the label a primary key. A primary key is an attribute that can be used for identifying in an unique way a record.

We can add the primary key using the ALTER TABLE SQL command:

ALTER TABLE WORK
ADD PRIMARY KEY (label);

This transform the label attribute to a primary key.

Now, we can add a work_id attribute in the user table. This table will contain the label of the work that the user are. This way we can link the user to the table.

The SQL code for adding the attribute to the table is:

ALTER TABLE users ADD work_id VARCHAR(50) NULL;

Now we can add a relationship using a foreign key. Wit this we can say to the database that two tables are linked. We say in an explicit way that the value of the work_id column is a primary key value of the work table.

In order to add the relationship, this is the code that we need:

ALTER TABLE public.users
ADD FOREIGN KEY (work_id)
REFERENCES public.WORK (label);

Now if we want to set a work for a user, we can alter the table by updating the column, like this:

UPDATE users
SET work_id = 'President'
WHERE name = 'John' AND surname = 'Doe';

 With this we can associate a work with an user:

pgAdmin

During the installation, we installed also a GUI for managing PostgreSQL database: pgAdmin. This tool allows to avoid to use the console (even if it's a very useful tool).

This tool show on the left panel a list of databases, that' possible to expand to select specific parts of databases for working with them. On the main part of the screen many tabs allows to view and change many aspects of a database, also creating directly queries, like we can see in the screenshot. This is a very useful tool when we want to have a global view of our databases.

Sometime I prefer the console tool for using a database and setting manually queries. It's faster and always available. The command-line tool for PostgreSQL is psql. You can start it by opening it from the console in Linux, or finding it in the Start menu in windows.

Conclusions

In this article we had a brief view of PostgreSQL, with a very short introduction to SQL, databases and pgAdmin. The rest of articles will be more focused on PostGIS, so if you need more information about the database itself, you can find a lot of documentation if you look for it. We'll start to work with PostGIS from next article.

Thanks and see you later!

Add comment