Jointure et regroupement

La combinaison de JOIN avec GROUP BY fournit le type d'analyse qui est courramment utilisé dans les systèmes SIG.

Par exemple : Quelle est la population et la répartition raciale du quartier de Manhattan ? Ici nous avons une question qui combine les informations relatives à la population recenssée et les contours des quartier, or nous ne voulons qu'un seul quartier, celui de Manhattan.

SELECT
  neighborhoods.name AS neighborhood_name,
  Sum(census.popn_total) AS population,
  Round(100.0 * Sum(census.popn_white) / Sum(census.popn_total),1) AS white_pct,
  Round(100.0 * Sum(census.popn_black) / Sum(census.popn_total),1) AS black_pct
FROM nyc_neighborhoods AS neighborhoods
JOIN nyc_census_blocks AS census
ON ST_Intersects(neighborhoods.the_geom, census.the_geom)
WHERE neighborhoods.boroname = 'Manhattan'
GROUP BY neighborhoods.name
ORDER BY white_pct DESC;

  neighborhood_name  | population | white_pct | black_pct
---------------------+------------+-----------+-----------
 Carnegie Hill       |      19909 |      91.6 |       1.5
 North Sutton Area   |      21413 |      90.3 |       1.2
 West Village        |      27141 |      88.1 |       2.7
 Upper East Side     |     201301 |      87.8 |       2.5
 Greenwich Village   |      57047 |      84.1 |       3.3
 Soho                |      15371 |      84.1 |       3.3
 Murray Hill         |      27669 |      79.2 |       2.3
 Gramercy            |      97264 |      77.8 |       5.6
 Central Park        |      49284 |      77.8 |      10.4
 Tribeca             |      13601 |      77.2 |       5.5
 Midtown             |      70412 |      75.9 |       5.1
 Chelsea             |      51773 |      74.7 |       7.4
 Battery Park        |       9928 |      74.1 |       4.9
 Upper West Side     |     212499 |      73.3 |      10.4
 Financial District  |      17279 |      71.3 |       5.3
 Clinton             |      26347 |      64.6 |      10.3
 East Village        |      77448 |      61.4 |       9.7
 Garment District    |       6900 |      51.1 |       8.6
 Morningside Heights |      41499 |      50.2 |      24.8
 Little Italy        |      14178 |      39.4 |       1.2
 Yorkville           |      57800 |      31.2 |      33.3
 Inwood              |      50922 |      29.3 |      14.9
 Lower East Side     |     104690 |      28.3 |       9.0
 Washington Heights  |     187198 |      26.9 |      16.3
 East Harlem         |      62279 |      20.2 |      46.2
 Hamilton Heights    |      71133 |      14.6 |      41.1
 Chinatown           |      18195 |      10.3 |       4.2
 Harlem              |     125501 |       5.7 |      80.5

Que ce passe-t-il ici ? Voici ce qui se passe (l'ordre d'évaluation est optimisé par la base de données) :

1. La clause JOIN crée une table cirtuelle qui contient les colonnes à la fois des quartier et des recensements (tables neighborhoods et census).

2. La claue WHERE filtre la table virtuelle pour ne conserver que la ligne correspondant à Manhattan.

3. Les lignes restantes sont regroupées par le nom du quartier et sont utilisées par la fonction d'agrégation : :command:`Sum()` pour réaliser la somme des valeurs de la populations.

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

   Unknown interpreted text role "command".

4. Après un peu d'arythmétiques et de formatage (ex: GROUP BY, ORDER BY)) ssur le nombres finaux, notre requête calcul les porcentages.

Nous pouvons aussi utiliser le test de la distance dans notre clef de jointure, pour créer une regroupement de "tout les éléments dans un certain rayon". Essayons de regarder la géographie raciale de New York en utilisant les requêtes de distance.

Premièrement, essayons d'obtenir la répartition raciale de la ville.

SELECT
  100.0 * Sum(popn_white) / Sum(popn_total) AS white_pct,
  100.0 * Sum(popn_black) / Sum(popn_total) AS black_pct,
  Sum(popn_total) AS popn_total
FROM nyc_census_blocks;

      white_pct      |      black_pct      | popn_total
---------------------+---------------------+------------
 44.6586020115685295 | 26.5945063345703034 |    8008278

Donc, 8M de personnes de New York, environ 44% sont "blancs" et "26% sont "noirs".

Duke Ellington chantait que "You / must take the A-train / To / go to Sugar Hill way up in Harlem." Comme nous l'avons vu précédemment, Harlem est de très loin le quartier ou se trouve la plus grande concentration d'africains-américains de Manhattan (80.5%). Est-il toujours vrai qu'il faut prendre le train A dont Duke parlait dans sa chanson ?

Premièrement, le contenu du champ routes de la table nyc_subway_stations va nous servir à récupérer le train A. Les valeurs de ce champs sont un peu complexes.

SELECT DISTINCT routes FROM nyc_subway_stations;

A,C,G
4,5
D,F,N,Q
5
E,F
E,J,Z
R,W

Donc pour trouver le train A, nous allons demander toute les lignes ayant pour routes la valeur 'A'. Nous pouvons faire cela de différentes manières, mais nous utiliserons aujourd'hui le fait que la fonction :command:`strpos(routes,'A')` retourne un entier différent de 0 si la lettre 'A' se trouve dans la valeur du champs route.

SELECT DISTINCT routes
FROM nyc_subway_stations AS subways
WHERE strpos(subways.routes,'A') > 0;

A,B,C
A,C
A
A,C,G
A,C,E,L
A,S
A,C,F
A,B,C,D
A,C,E

Essayons de regrouper la répartition racialedans un rayon de 200 mètres de la ligne du train A.

SELECT
  100.0 * Sum(popn_white) / Sum(popn_total) AS white_pct,
  100.0 * Sum(popn_black) / Sum(popn_total) AS black_pct,
  Sum(popn_total) AS popn_total
FROM nyc_census_blocks AS census
JOIN nyc_subway_stations AS subways
ON ST_DWithin(census.the_geom, subways.the_geom, 200)
WHERE strpos(subways.routes,'A') > 0;

      white_pct      |      black_pct      | popn_total
---------------------+---------------------+------------
 42.0805466940877366 | 23.0936148851067964 |     185259

La répartition raciale le long de la ligne du train A n'est pas radicallement différente de la répartition générale de la ville de New York.

Jointres avancées

Dans la dernière partie nous avons vu que le train A n'est pas utilisé par des populations si éloignées de la répartition totale du reste de la ville. Y-a-t-il des train qui passent par des parties de la ville qui ne sont pas dans la moyenne de la répartition raciale ?

Pour répondre à cette question, nous ajouterons une nouvelle jointure à notre requête, de telle manière que nous puissions calculer simultanément la répartition raciale de plusieures lignes de métros à la fois. Pour faire ceci, nous créerons une table qui permettra d'énumérer toutes les lignes que nous voulons regrouper.

CREATE TABLE subway_lines ( route char(1) );
INSERT INTO subway_lines (route) VALUES
  ('A'),('B'),('C'),('D'),('E'),('F'),('G'),
  ('J'),('L'),('M'),('N'),('Q'),('R'),('S'),
  ('Z'),('1'),('2'),('3'),('4'),('5'),('6'),
  ('7');

Maintenant nous pouvons joindre les tables des lignes de métros à notre requête précédente.

SELECT
  lines.route,
  Round(100.0 * Sum(popn_white) / Sum(popn_total), 1) AS white_pct,
  Round(100.0 * Sum(popn_black) / Sum(popn_total), 1) AS black_pct,
  Sum(popn_total) AS popn_total
FROM nyc_census_blocks AS census
JOIN nyc_subway_stations AS subways
ON ST_DWithin(census.the_geom, subways.the_geom, 200)
JOIN subway_lines AS lines
ON strpos(subways.routes, lines.route) > 0
GROUP BY lines.route
ORDER BY black_pct DESC;

 route | white_pct | black_pct | popn_total
-------+-----------+-----------+------------
 S     |      30.1 |      59.5 |      32730
 3     |      34.3 |      51.8 |     201888
 2     |      33.6 |      45.5 |     535414
 5     |      32.1 |      45.1 |     407324
 C     |      41.3 |      35.9 |     430194
 4     |      34.7 |      30.9 |     328292
 B     |      36.1 |      30.6 |     261186
 Q     |      52.9 |      26.3 |     259820
 J     |      29.5 |      23.6 |     126764
 A     |      42.1 |      23.1 |     370518
 Z     |      29.5 |      21.5 |      81493
 D     |      39.8 |      20.9 |     233855
 G     |      44.8 |      20.0 |     138602
 L     |      53.9 |      17.1 |     104140
 6     |      52.7 |      16.3 |     257769
 1     |      54.8 |      12.6 |     659028
 F     |      60.0 |       8.6 |     438212
 M     |      50.0 |       7.8 |     166721
 E     |      69.4 |       5.3 |      86118
 R     |      57.7 |       4.8 |     389124
 7     |      42.4 |       3.8 |     107543

Comme précédemment, les jointure créent une table virtuelle de toutes les combinaisons possible disponibles à l'aide des contraintes de type JOIN ON`, ces lignes sont ensuite utilisées dans le regroupement ``GROUP. La magie spatiale tiend dans l'utilisation de la fonction ST_DWithin qui s'assure que les bloques sont suffisamment proches des lignes de métros inclues dans le calcul.

Liste de fonctions

ST_Contains(geometry A, geometry B): retourne TRUE si et seulement si aucun points de B est à l'extérieur de A, et si au moins un point à l'intérieur de B est à l'intérieur de A.

ST_DWithin(geometry A, geometry B, radius): retourne TRUE si les géométries sont distantes du rayon donné.

ST_Intersects(geometry A, geometry B): retourne TRUE si les géométries/géographies "s'intersectent spatialement" (partage une portiond de l'espace) et FALSE sinon (elles sont dijointes).

round(v numeric, s integer): fonction de PostgreSQL qui arrondit à s décimales.

strpos(chaîne, sous-chaîne): fonction de chaîne de caractères de PostgreSQL qui retourne la position de la sous-chaine.

sum(expression): fonction d'agrégation de PostgreSQL qui retourne la somme d'un ensemble de valeurs.

Footnotes