Changes in trunk/workshop-foss4g/joins.rst [62:1]

File:

• trunk/workshop-foss4g/joins.rst (modified) (10 diffs)

trunk/workshop-foss4g/joins.rst

@@ -1 +1 @@
 .. _joins:
 
-Partie 12 : Les jointures spatiales
-===================================
-
-Les jointures spatiales sont la cerise sur le gâteau des base de données spatiales. Elles vous pemettent de combiner les informations de plusieurs tables en utilisant une relation spatiale comme clause de jointure. La plupart des "analyses SIG standards" peuvent être exprimées à l'aide de jointures spatiales.
-
-Dans la partie précédente, nous avons utilisé les relations spatiales en utilisant deux étapes dans nos requêtes : nous avons dans un premier temps extrait la station de métro "Broad St" puis nous avons utilisé ce résultat dans nos autres requêtes pour répondre aux questions comme "dans quel quartier se situe la station 'Broad St' ?"
-
-En utilisant les jointures spatiales, nous pouvons répondre aux questions en une seule étape, récupérant les informations relatives à la station de métro et le quartier la contenant :
-
-.. code-block:: sql
-
-  SELECT
-    subways.name AS subway_name,
-    neighborhoods.name AS neighborhood_name,
+Section 12: Spatial Joins
+=========================
+
+Spatial joins are the bread-and-butter of spatial databases.  They allow you to combine information from different tables by using spatial relationships as the join key.  Much of what we think of as "standard GIS analysis" can be expressed as spatial joins.
+
+In the previous section, we explored spatial relationships using a two-step process: first we extracted a subway station point for 'Broad St'; then, we used that point to ask further questions such as "what neighborhood is the 'Broad St' station in?"
+
+Using a spatial join, we can answer the question in one step, retrieving information about the subway station and the neighborhood that contains it:
+
+.. code-block:: sql
+
+  SELECT 
+    subways.name AS subway_name, 
+    neighborhoods.name AS neighborhood_name, 
     neighborhoods.boroname AS borough
   FROM nyc_neighborhoods AS neighborhoods
@@ -21 +21 @@
   WHERE subways.name = 'Broad St';
 
-::
-
-   subway_name | neighborhood_name  |  borough
+:: 
+
+   subway_name | neighborhood_name  |  borough  
   -------------+--------------------+-----------
    Broad St    | Financial District | Manhattan
 
-Nous avons pu regrouper chaque station de métro avec le quartier auquel elle appartient, mais dans ce cas nous n'en voulions qu'une. Chaque fonction qui envoit un résultat du type vrai/faux peut être utilisée pour joindre spatialement deux tables, mais la plupart du temps on utilise : :command:`ST_Intersects`, :command:`ST_Contains`, et :command:`ST_DWithin`.
-
-Jointure et regroupement
-------------------------
-
-La combinaison de ``JOIN`` avec ``GROUP BY`` fournit le type d'analyse qui est couramment 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 recensée et les contours des quartiers, or nous ne voulons qu'un seul quartier, celui de Manhattan.
-
-.. code-block:: sql
-
-  SELECT
-    neighborhoods.name AS neighborhood_name,
+We could have joined every subway station to its containing neighborhood, but in this case we wanted information about just one.  Any function that provides a true/false relationship between two tables can be used to drive a spatial join, but the most commonly used ones are: :command:`ST_Intersects`, :command:`ST_Contains`, and :command:`ST_DWithin`.
+
+Join and Summarize
+------------------
+
+The combination of a ``JOIN`` with a ``GROUP BY`` provides the kind of analysis that is usually done in a GIS system.
+
+For example: **"What is the population and racial make-up of the neighborhoods of Manhattan?"** Here we have a question that combines information from about population from the census with the boundaries of neighborhoods, with a restriction to just one borough of Manhattan.
+
+.. code-block:: sql
+
+  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,
@@ -52 +52 @@
 ::
 
-   neighborhood_name  | population | white_pct | black_pct
+   neighborhood_name  | population | white_pct | black_pct 
  ---------------------+------------+-----------+-----------
   Carnegie Hill       |      19909 |      91.6 |       1.5
@@ -84 +84 @@
 
 
-Que ce passe-t-il ici ?  Voici ce qui se passe (l'ordre d'évaluation est optimisé par la base de données) :
-
-#. La clause ``JOIN`` crée une table virtuelle qui contient les colonnes à la fois des quartiers et des recensements (tables neighborhoods et census).
-#. La clause ``WHERE`` filtre la table virtuelle pour ne conserver que la ligne correspondant à Manhattan.
-#. 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 population.
-#. AprÚs un peu d'arithmétique et de formatage (ex: ``GROUP BY``, ``ORDER BY``)) sur le nombres finaux, notre requête calcule les pourcentages.
-
-.. note::
-
-   La clause ``JOIN`` combine deux parties ``FROM``.  Par défaut, nous utilisons un jointure du type :``INNER JOIN``, mais il existe quatres autres types de jointures. Pour de plus amples informations à ce sujet, consultez la partie `type_jointure <http://docs.postgresql.fr/9.1/sql-select.html>`_ de la page de la documentation officielle de PostgreSQL.
-
-Nous pouvons aussi utiliser le test de la distance dans notre clef de jointure, pour créer une regroupement de "tous les éléments dans un certain rayon". Essayons d'analyser 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.
-
-.. code-block:: sql
-
-  SELECT
-    100.0 * Sum(popn_white) / Sum(popn_total) AS white_pct,
-    100.0 * Sum(popn_black) / Sum(popn_total) AS black_pct,
+What's going on here? Notionally (the actual evaluation order is optimized under the covers by the database) this is what happens:
+
+#. The ``JOIN`` clause creates a virtual table that includes columns from both the neighborhoods and census tables. 
+#. The ``WHERE`` clause filters our virtual table to just rows in Manhattan. 
+#. The remaining rows are grouped by the neighborhood name and fed through the aggregation function to :command:`Sum()` the population values.
+#. After a little arithmetic and formatting (e.g., ``GROUP BY``, ``ORDER BY``) on the final numbers, our query spits out the percentages.
+
+.. note:: 
+
+   The ``JOIN`` clause combines two ``FROM`` items.  By default, we are using an ``INNER JOIN``, but there are four other types of joins. For further information see the `join_type <http://www.postgresql.org/docs/8.1/interactive/sql-select.html>`_ definition in the PostgreSQL documentation.
+
+We can also use distance tests as a join key, to create summarized "all items within a radius" queries. Let's explore the racial geography of New York using distance queries.
+
+First, let's get the baseline racial make-up of the city.
+
+.. code-block:: sql
+
+  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
+:: 
+
+        white_pct      |      black_pct      | popn_total 
   ---------------------+---------------------+------------
    44.6586020115685295 | 26.5945063345703034 |    8008278
 
 
-Donc, 8M de personnes dans 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.
+So, of the 8M people in New York, about 44% are "white" and 26% are "black". 
+
+Duke Ellington once sang that "You / must take the A-train / To / go to Sugar Hill way up in Harlem." As we saw earlier, Harlem has far and away the highest African-American population in Manhattan (80.5%). Is the same true of Duke's A-train?
+
+First, note that the contents of the ``nyc_subway_stations`` table ``routes`` field is what we are interested in to find the A-train. The values in there are a little complex.
 
 .. code-block:: sql
 
   SELECT DISTINCT routes FROM nyc_subway_stations;
-
-::
+  
+:: 
 
  A,C,G
@@ -136 +136 @@
 .. note::
 
-   Le mot clef ``DISTINCT`` permet d'éliminer les répétitions de lignes de notre résultat. Dans ce mot clef, notre requête renverrait 491 résultats au lieu de 73.
-
-Donc pour trouver le train A, nous allons demander toutes 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 champ route.
-
-.. code-block:: sql
-
-   SELECT DISTINCT routes
-   FROM nyc_subway_stations AS subways
+   The ``DISTINCT`` keyword eliminates duplicate rows from the result.  Without the ``DISTINCT`` keyword, the query above identifies 491 results instead of 73.
+   
+So to find the A-train, we will want any row in ``routes`` that has an 'A' in it. We can do this a number of ways, but today we will use the fact that :command:`strpos(routes,'A')` will return a non-zero number if 'A' is in the routes field.
+
+.. code-block:: sql
+
+   SELECT DISTINCT routes 
+   FROM nyc_subway_stations AS subways 
    WHERE strpos(subways.routes,'A') > 0;
-
+   
 ::
 
@@ -157 +157 @@
   A,B,C,D
   A,C,E
-
-Essayons de regrouper la répartition raciale dans un rayon de 200 mÚtres de la ligne du train A.
-
-.. code-block:: sql
-
-  SELECT
-    100.0 * Sum(popn_white) / Sum(popn_total) AS white_pct,
-    100.0 * Sum(popn_black) / Sum(popn_total) AS black_pct,
+  
+Let's summarize the racial make-up of within 200 meters of the A-train line.
+
+.. code-block:: sql
+
+  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
@@ -173 +173 @@
 ::
 
-        white_pct      |      black_pct      | popn_total
+        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 radicalement différente de la répartition générale de la ville de New York.
-
-Jointures 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 trains 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 plusieurs lignes de métro à la fois. Pour faire ceci, nous créerons une table qui permettra d'énumérer toutes les lignes que nous voulons regrouper.
+So the racial make-up along the A-train isn't radically different from the make-up of New York City as a whole. 
+
+Advanced Join
+-------------
+
+In the last section we saw that the A-train didn't serve a population that differed much from the racial make-up of the rest of the city. Are there any trains that have a non-average racial make-up?
+
+To answer that question, we'll add another join to our query, so that we can simultaneously calculate the make-up of many subway lines at once. To do that, we'll need to create a new table that enumerates all the lines we want to summarize.
 
 .. code-block:: sql
 
     CREATE TABLE subway_lines ( route char(1) );
-    INSERT INTO subway_lines (route) VALUES
+    INSERT INTO subway_lines (route) VALUES 
       ('A'),('B'),('C'),('D'),('E'),('F'),('G'),
       ('J'),('L'),('M'),('N'),('Q'),('R'),('S'),
@@ -195 +195 @@
       ('7');
 
-Maintenant nous pouvons joindre les tables des lignes de métro à notre requête précédente.
-
-.. code-block:: sql
-
-    SELECT
+Now we can join the table of subway lines onto our original query.
+
+.. code-block:: sql
+
+    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,
+      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
@@ -214 +214 @@
 ::
 
-     route | white_pct | black_pct | popn_total
+     route | white_pct | black_pct | popn_total 
     -------+-----------+-----------+------------
      S     |      30.1 |      59.5 |      32730
@@ -239 +239 @@
 
 
-Comme précédemment, les jointures créent une table virtuelle de toutes les combinaisons possibles et disponibles à l'aide des contraintes de type ``JOIN ON`. Ces lignes sont ensuite utilisées dans le regroupement ``GROUP``. La magie spatiale tient dans l'utilisation de la fonction ``ST_DWithin`` qui s'assure que les blocs sont suffisamment proches des lignes de métros incluses dans le calcul.
-
-Liste de fonctions
-------------------
-
-`ST_Contains(geometry A, geometry B) <http://postgis.org/docs/ST_Contains.html>`_: retourne TRUE si et seulement si aucun point 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) <http://postgis.org/docs/ST_DWithin.html>`_: retourne TRUE si les géométries sont distantes du rayon donné.
-
-`ST_Intersects(geometry A, geometry B) <http://postgis.org/docs/ST_Intersects.html>`_: retourne TRUE si les géométries/géographies "s'intersectent spatialement" (partage une portion de l'espace) et FALSE sinon (elles sont disjointes).
-
-`round(v numeric, s integer) <http://www.postgresql.org/docs/7.4/interactive/functions-math.html>`_: fonction de PostgreSQL qui arrondit à s décimales.
-
-`strpos(chaîne, sous-chaîne) <http://www.postgresql.org/docs/current/static/functions-string.html>`_: fonction de chaîne de caractÚres de PostgreSQL qui retourne la position de la sous-chaine.
-
-`sum(expression) <http://www.postgresql.org/docs/8.2/static/functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE>`_: fonction d'agrégation de PostgreSQL qui retourne la somme d'un ensemble de valeurs.
-
-.. rubric:: Notes de bas de page
+As before, the joins create a virtual table of all the possible combinations available within the constraints of the ``JOIN ON`` restrictions, and those rows are then fed into a ``GROUP`` summary. The spatial magic is in the ``ST_DWithin`` function, that ensures only census blocks close to the appropriate subway stations are included in the calculation.
+
+Function List
+-------------
+
+`ST_Contains(geometry A, geometry B) <http://postgis.org/docs/ST_Contains.html>`_: Returns true if and only if no points of B lie in the exterior of A, and at least one point of the interior of B lies in the interior of A.
+
+`ST_DWithin(geometry A, geometry B, radius) <http://postgis.org/docs/ST_DWithin.html>`_: Returns true if the geometries are within the specified distance of one another. 
+
+`ST_Intersects(geometry A, geometry B) <http://postgis.org/docs/ST_Intersects.html>`_: Returns TRUE if the Geometries/Geography "spatially intersect" - (share any portion of space) and FALSE if they don't (they are Disjoint). 
+
+`round(v numeric, s integer) <http://www.postgresql.org/docs/7.4/interactive/functions-math.html>`_: PostgreSQL math function that rounds to s decimal places
+
+`strpos(string, substring) <http://www.postgresql.org/docs/current/static/functions-string.html>`_: PostgreSQL string function that returns an integer location of a specified substring.
+
+`sum(expression) <http://www.postgresql.org/docs/8.2/static/functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE>`_: PostgreSQL aggregate function that returns the sum of records in a set of records.
+
+.. rubric:: Footnotes
 
 .. [#PostGIS_Doco] http://postgis.org/documentation/manual-1.5/