Introduction to Databases - Lesson Assignment
 
Host: ewis.pld.ttu.ee
Database:  
Port: 5432
User: 
Password:  

To solve the lesson it is necessary to use a table called 'joe_data' which contains
Estonian rivers and table named 'joe_andmed' and a table
The fields in the 'seire_jogi_hydrol_jaamad' table are important
the length of the river (pikkus_km) and the beginning of the river
geographic coordinates and of the end point
( lon1, lat1 - start; lon2, lat2 - end).
Every river and changes in the river
the monitoring station for monitoring has its own
identifier. Tables 'joe_andmed'
and 'monitoring_stations' are related
using the identifier 'id_jogi' - i.e. each
the monitoring station is connected to one specific river,
by which it is located.

Tasks
1. Write a program that outputs the 5 longest rivers.
2. Write a program that outputs rivers that are main rivers and longer than 70 km.
Use id_jogi and id_peajogi to determine the main river.
3. Write a program that outputs the monitoring stations located on the rivers found in the previous point.
The keywords are 'inner join' and 'subqueries'.
To begin with, the task can be simplified and a query can be made without 
considering the previous query
4. Get the coordinates of the beginning and end point of the river from the
database and find the distance between these points
in a straight line. The result can be limited to, for example, 
the first 10 entries.

*Use the following formula to find the distance between two points:
d = R*acos(sin(lat1)*sin(lat2) + cos(lat1)*cos(lat2)*cos(lon1-lon2));
* R - radius of the earth. Unit according to the units in which you want 
to issue the answer.
*Values inherited from tables are in degrees, but C math.h 
trigonometric functions
require arguments in radians.
*The PQgetvalue function returns a result of string type. So it would come around
to convert to a floating point number so that it is possible to perform 
calculations with it