Bienvenue sur PostGIS.fr

Bienvenue sur PostGIS.fr , le site de la communauté des utilisateurs francophones de PostGIS.

PostGIS ajoute le support d'objets géographique à la base de données PostgreSQL. En effet, PostGIS "spatialise" le serverur PostgreSQL, ce qui permet de l'utiliser comme une base de données SIG.

Maintenu à jour, en fonction de nos disponibilités et des diverses sorties des outils que nous testons, nous vous proposons l'ensemble de nos travaux publiés en langue française.

source: trunk/workshop-foss4g/simple_sql.rst @ 63

Revision 62, 5.9 KB checked in by thomasg, 13 years ago (diff)

Fin correction typo et orthographe V2 du document

Partie 6 : Requêtes SQL simples

System Message: WARNING/2 (<string>, line 4)

Title underline too short.

Partie 6 : Requêtes SQL simples
===============================

:term:`SQL`, pour "Structured Query Language", définit la maniÚre d'importer et d'interroger des données dans une base. Vous avez déjà rédigé du SQL lorsque nous avons créé notre premiÚre base de données.

System Message: ERROR/3 (<string>, line 6); backlink

Unknown interpreted text role "term".

Rappel:

SELECT postgis_full_version();

Maintenant que nous avons chargé des données dans notre base, essayons d'utiliser SQL pour les interroger. Par exemple,

"Quels sont les noms des quartiers de la ville de New York ?"

Ouvrez une fenêtre SQL depuis pgAdmin en cliquant sur le bouton SQL

./screenshots/pgadmin_05.png

Puis saisissez la requête suivante dans la fenêtre

SELECT name FROM nyc_neighborhoods;

et cliquez sur le bouton Execute Query (le triangle vert).

./screenshots/pgadmin_08.png

La requête s'exécutera pendant quelques (mili)secondes et retournera 129 résultats.

./screenshots/pgadmin_09.png

Mais que s'est-il exactement passé ici ? Pour le comprendre, commençons par présenter les quatre types de requêtes du SQL :

  • SELECT, retourne des lignes en réponse à une requête
  • INSERT, ajoute des lignes dans une table
  • UPDATE, modifie des lignes existantes d'une table
  • DELETE, supprime des lignes d'une table

Nous travaillerons principalement avec des requêtes de type SELECT afin d'interroger les tables en utilisant des fonctions spatiales.

Requête de type SELECT

System Message: WARNING/2 (<string>, line 46)

Title underline too short.

Requête de type SELECT
----------------------

Une requête de type Select est généralement de la forme :

SELECT colonnes FROM données WHERE conditions;

Note

Pour une description exhaustive des paramÚtres possible d'une requête SELECT, consultez la documentation de PostgresSQL.

Les colonnes sont soit des noms de colonnes, soit des fonctions utilisant les valeurs des colonnes. Les données sont soit une table seule, soit plusieurs tables reliées ensemble en réalisant une jointure sur une clef ou une autre condition. Les conditions représentent le filtre qui restreint le nombre de lignes à retourner.

"Quel sont les noms des quartiers de Brooklyn ?"

Nous retournons à notre table nyc_neighborhoods avec le filtre en main. La table contient tous les quartiers de New York et nous voulons uniquement ceux de Brooklyn.

SELECT name
  FROM nyc_neighborhoods
  WHERE boroname = 'Brooklyn';

La requête prendra à nouveau quelque (milli)secondes et retournera les 23 éléments résultants.

Parfois, nous aurons besoin d'appliquer des fonctions sur le résultat d'une de nos requêtes. Par exemple,

"Quel est le nombre de lettres dans les noms des quartiers de Brooklyn ?"

Heureusement PostgreSQL fournit une fonction calculant la longueur d'une chaîne de caractÚres : :command:`char_length(string)`.

System Message: ERROR/3 (<string>, line 75); backlink

Unknown interpreted text role "command".
SELECT char_length(name)
  FROM nyc_neighborhoods
  WHERE boroname = 'Brooklyn';

Bien souvent, nous sommes moins interessés par une ligne particuliÚre que par un calcul statistique sur l'ensemble résultant. Donc, connaître la longueur des noms de quartiers est moins intéressant que de calculer la moyenne de ces longueurs. Les fonctions qui renvoient un résultat unique en utilisant un ensemble de valeurs sont appelées des "fonctions d'aggrégations".

PostgreSQL fournit un ensemble de fonctions d'aggrégations, parmi lesquelles :command:`avg()` pour calculer la moyenne, and :command:`stddev()` pour l'écart type.

System Message: ERROR/3 (<string>, line 85); backlink

Unknown interpreted text role "command".

System Message: ERROR/3 (<string>, line 85); backlink

Unknown interpreted text role "command".
"Quel est le nombre moyen et l'écart type du nombre de lettres dans le nom des quartier de Brooklyn ?"
SELECT avg(char_length(name)), stddev(char_length(name))
  FROM nyc_neighborhoods
  WHERE boroname = 'Brooklyn';
         avg         |       stddev
---------------------+--------------------
 11.7391304347826087 | 3.9105613559407395

Les fonctions d'agrégation dans notre dernier exemple sont appliquées à chaque ligne de l'ensemble des résultats. Comment faire si nous voulons rassembler des données ? Pour cela, nous utilisons la clause GROUP BY. Les fonctions d'agrégation ont souvent besoin d'une clause GROUP BY pour regrouper les éléments en utilisant une ou plusieurs colonnes.

"Quel est la moyenne du nombre de caractÚres des noms de quartiers et l'écart-type du nombre de caractÚres des noms de quartiers, renvoyé par section de New York ?"
SELECT boroname, avg(char_length(name)), stddev(char_length(name))
  FROM nyc_neighborhoods
  GROUP BY boroname;

Nous ajoutons la colonne boroname dans le résultat afin de pouvoir déterminer quelle valeur statistique s'applique à quelle section. Dans une requête agrégée, vous pouvez seulement retourner les colonnes qui sont (a) membre de la clause de regroupement ou (b) des fonctions d'agrégation.

   boroname    |         avg         |       stddev
---------------+---------------------+--------------------
 Brooklyn      | 11.7391304347826087 | 3.9105613559407395
 Manhattan     | 11.8214285714285714 | 4.3123729948325257
 The Bronx     | 12.0416666666666667 | 3.6651017740975152
 Queens        | 11.6666666666666667 | 5.0057438272815975
 Staten Island | 12.2916666666666667 | 5.2043390480959474

Liste de fonctions

avg(expression): fonction d'agrégation de PostgreSQL qui retourne la valeur moyenne d'une colonne.

char_length(string): fonction s'appliquant aux chaînes de caractÚre de PostgreSQL qui retourne le nombre de lettres dans une chaîne.

stddev(expression): fonction d'aggrégation de PostgreSQL qui retourne l'écart type d'un ensemble de valeurs.

Note: See TracBrowser for help on using the repository browser.