From GeoBox

Connection to the database

These are very simple SQL exercises, mostly to access the database in different ways. There are several ways to connect to your local PostgreSQL RDBMS, as you will see.

Security issues

You should be able to explore the database with different interfaces, embedded in different programs and from the command line.

The database can be access from your local machine or from remote hosts. Access is controlled by the PostgreSQL access rules. These are based on the users identification, the database we are using, and the ip of the client host.

In the future, we should be aware of these security rules, since they are fundamental to keep your data available as possible in a secure way.

For these exercises you DON'T NEED to review the security rules. Right now, your database can be accessed from local and remote hosts, if a username and password is provided.

Connection through phppgadmin

Test the web interface, available at:

http://localhost/phppgadmin

Login with: geobox/geobox

Find the geotuga database, and check the available tables already there.

Open the SQL window, and activate the option Paginate results

-- quantas pessoas tem a tabela contribuinte
SELECT COUNT(*) FROM contribuinte;
-- quantos começam por Jorge?
SELECT * FROM contribuinte WHERE nome ilike 'jorge%';
-- apelido Jorge
SELECT * FROM contribuinte WHERE nome ilike '%jorge';
-- quantas são de Barcelos? (CP = 4750)
SELECT COUNT(*) FROM contribuinte WHERE codpostal LIKE '4750%';

Connection through pgAdmin III

Start the pgAdmin III application (Applications -> Programming -> pgAdmin III), and connect to the geotuga database (doble click on geobox@localhost (localhost:5432)).

Let's do some additional queries on the contribuinte table.

Open the query window (Ferramentas -> Ferramenta de Consulta, ou use o Crtl+E). There are two major areas: one to enter queries and another to display the results. If some SQL commands are already there, select and remove them all.

• Show just the first 4 digitis of the zip code. You might need to remember the available | String Functions and Operators

SELECT SUBSTRING(codpostal FROM 1 FOR 4), * 
FROM contribuinte 
WHERE nome ilike '%jorge%'

After writing the query (or paste), select it and hit F5 (or use the small green button, with the hint Executar consulta)

• Which are the places (i.é, zip codes) with more than 50 inhabitants?

SELECT codpostal, COUNT(*)
FROM contribuinte
GROUP BY codpostal
HAVING COUNT(*) > 50

If the query is ok, switch to the 'Explanation' tab, and run the query with F7.

File:Explicação query.jpg

• Calculate the number of inhabitants per 4-digit zip code.

SELECT SUBSTRING(codpostal FROM 1 FOR 4), COUNT(*)
FROM contribuinte 
WHERE nome ilike '%jorge%'
GROUP BY SUBSTRING(codpostal FROM 1 FOR 4)

Joins (only available after importing profissões)

SELECT nome, codpostal, designacao
FROM contribuinte, profissao
WHERE nome ilike '%jorge%' AND contribuinte.codprofissao = profissao.codprofissao

SELECT designacao, COUNT(*)
FROM contribuinte, profissao
WHERE nome ilike '%jorge%' AND contribuinte.codprofissao = profissao.codprofissao
GROUP BY designacao
ORDER BY COUNT(*) DESC

Integrity

DELETE FROM profissao
WHERE codprofissao = 66

Access the database from the command line

Counting the number of records in contribuinte table:

psql -d geotuga -c "select count(*) from contribuinte"

Showing users with last name like jorge:

psql -d geotuga -c "select * from contribuinte where nome ilike '%jorge'"

You can write SQL queries in any editor and the submit it to the database:

geobox@geobox:~$ cat x.sql 
select codpostal, count(*)
from contribuinte
group by codpostal
having count(*) > 50
geobox@geobox:~$ psql -d geotuga < x.sql
 codpostal | count 
-----------+-------
 4600-620  |    53
 4700-565  |    62
 4740-577  |    65
 4760-480  |    61
 9900-341  |    90
(5 rows)

If the server is running on a remote host:

psql -h 192.168.2.150 -d geotuga -U geobox -c "select nome from contribuinte where nome ilike '%JORGE%'"

Access the database using SDBC, JDBC, or ODBC drivers

Your PostgreSQL databases can be accessed by Open Office tools. The Open Office Base application is the most used front end to databases. Currently, Open Office Base is not installed in the GeoBox.

sudo apt-get install openoffice.org-sdbc-postgresql odbc-postgresql openoffice.org-base

Start Open Office Base. The Database Wizard start, and you should select: Connect to an existing databasee and postgresql as the database type.

Afterwards, the connection parameters are required. Fill the form with the same parameters as showed in the images.

File:CapturaEcra-Database Wizard.png

Save the connection to this database for future usage.

File:CapturaEcra-Database Wizard Save.png