Databases in Geography

Applications of Databases to Humanities and Social Sciences

Introduction

Back to questions

Geography : Answer 1

Display, by region and department, the number of cities having more than 100000 inhabitants.

Links between tables

SQL query

(Back to questions)

Geography : Answer 2

Display the list of all the one-way connections between two cities for which the population of one of the 2 cities is lower than 80000 inhabitants.

Links between tables

Besides the links define above, it is necessary to chack that the string "V1.Ville+V.Ville " is not in the table "Distances", because that would mean that connection between the 2 cities is a double-ways connexion. This condition is checked in the nested Select clause.

SQL query

(Back to questions)

Geography : Answer 3

Display the list of cities, distant from 2 road sections, and the distance which separates them.

Links between tables

SQL query

(Back to questions)

Geography : Answer 4

Display the list of cities, which can be distant from 4 road sections (there can indeed be shorter connections into 2 or 3 sections), and the distance which separates them.

Links between tables

SQL query

We reach here the limits of the SQL language because this language is not recursive and the general treatment of question of this type implies a broad recourse to recursivity.

(Back to questions)

Geography : Answer 5

Display the number of inhabitants by region and department. We suppose that the population of a region is that of the cities which are a part of the road network.

Links between tables

SQL query

(Back to questions)

Geography : Answer 6

Display the name of the region which has the longest road network and the number of kilometers of this network.

To treat this question it is necessary to create 2 requests :

6.1-A request of creation of a work table "WorkTable" composed of the attributes: Numregion, Nomregion and Distance for all double-ways connections, between 2 cities of the same region.
This request is linked by a "UNION" predicat to another query which adds to the preceding one all the one-way connections between 2 cities of the same region.

6.2-A request of selection, starting from the table "WorkTable" created above.

Links between tables (requête 6.1)

SQL query 6.1

CREATE VIEW WorkTable(Numregion, Nomregion, Distance) AS
SELECT R.numregion, R.Nomregion, Sum(D.Distance)/2
FROM Distances D, Villes V, Villes V1, Departement P, Departement P1, Region R, Region R1
WHERE D.Villedepart=V.Ville
AND V.Numdept=P.Numdept
AND P.Numregion=R.numregion
AND D.Villearrivee=V1.Ville
AND V1.Numdept=P1.Numdept
AND P1.Numregion=R1.numregion
AND R.Numregion=R1.Numregion
AND D.villedepart IN
(SELECT villearrivee FROM Distances WHERE D.villearrivee=villedepart)
GROUP BY R.Numregion, R.Nomregion
UNION
SELECT R.numregion, R.Nomregion, Sum(D.Distance)
FROM Distances D, Villes V, Villes V1, Departement P, Departement P1, Region R, Region R1
WHERE D.Villedepart=V.Ville
AND V.Numdept=P.Numdept
AND P.Numregion=R.numregion
AND D.Villearrivee=V1.Ville
AND V1.Numdept=P1.Numdept
AND P1.Numregion=R1.numregion
AND R.Numregion=R1.Numregion
AND D.villedepart NOT IN
(SELECT villearrivee FROM Distances WHERE D.villearrivee=villedepart)
GROUP BY R.Numregion, R.Nomregion;

Links between tables (requête 6.2)

SQL query 6.2

The first line of the next query gives the answer to the question.

SELECT W.numregion, W.Nomregion, Sum(W.Distance) AS "Longueur du réseau"
FROM WorkTable AS W
GROUP BY W.numregion, W.Nomregion
ORDER BY 3 DESC;

(Back to questions)