source: trunk/workshop-foss4g/joins_exercises.rst @ 23

.. _joins_exercises:

Partie 13 : exercice jointures spatial
=======================================

Voici un petit rappel de certaines de fonctions. Astuce: elles pourraient être utiles pour les exercices !

 * :command:`sum(expression)` agrégation retournant la somme d'un ensemble
 * :command:`count(expression)` agrégation retournant le nombre d'éléments d'un ensemble
* :command:`ST_Area(geometry)` returns the area of the polygons
* :command:`ST_AsText(geometry)` returns WKT ``text``
* :command:`ST_Contains(geometry A, geometry B)` retourne vrai si la géométrie A contient la géométrie B 
* :command:`ST_Distance(geometry A, geometry B)` retourne la distance minimum entre deux géométries
* :command:`ST_DWithin(geometry A, geometry B, radius)` retourne vrai si la A est distante d'au plus radius de B
* :command:`ST_GeomFromText(text)` returns ``geometry``
* :command:`ST_Intersects(geometry A, geometry B)` returns the true if geometry A intersects geometry B
* :command:`ST_Length(linestring)` returns the length of the linestring
* :command:`ST_Touches(geometry A, geometry B)` retourne vrai si le contour extérieur de A touche B
* :command:`ST_Within(geometry A, geometry B)` retourne vrai si A est hors de B

Souvenez-vous des tables à notre disposition : 

 * ``nyc_census_blocks`` 
 
   * name, popn_total, boroname, the_geom
 
 * ``nyc_streets``
 
   * name, type, the_geom
   
 * ``nyc_subway_stations``
 
   * name, routes, the_geom
 
 * ``nyc_neighborhoods``
 
   * name, boroname, the_geom

Exercices
---------

 * **"Quelle station de métros se situe dans le quartier 'Little Italy' ? Quelle est l'itinéraire de métros à emprunter ?"**
 
   .. code-block:: sql
 
     SELECT s.name, s.routes 
     FROM nyc_subway_stations AS s
     JOIN nyc_neighborhoods AS n 
     ON ST_Contains(n.the_geom, s.the_geom)  
     WHERE n.name = 'Little Italy';

   :: 
  
       name    | routes 
    -----------+--------
     Spring St | 6
     
 * **"Quels sont les quartiers desservis pas le train numéro 6 ?"** (Astuce: la colonnes ``routes`` de la table ``nyc_subway_stations`` on les valeurs suivante 'B,D,6,V' et 'C,6')
 
   .. code-block:: sql
  
    SELECT DISTINCT n.name, n.boroname 
    FROM nyc_subway_stations AS s
    JOIN nyc_neighborhoods AS n 
    ON ST_Contains(n.the_geom, s.the_geom)  
    WHERE strpos(s.routes,'6') > 0;
    
   ::
  
            name        | boroname  
    --------------------+-----------
     Midtown            | Manhattan
     Hunts Point        | The Bronx
     Gramercy           | Manhattan
     Little Italy       | Manhattan
     Financial District | Manhattan
     South Bronx        | The Bronx
     Yorkville          | Manhattan
     Murray Hill        | Manhattan
     Mott Haven         | The Bronx
     Upper East Side    | Manhattan
     Chinatown          | Manhattan
     East Harlem        | Manhattan
     Greenwich Village  | Manhattan
     Parkchester        | The Bronx
     Soundview          | The Bronx

   .. note::
  
     Nous avons utilisé le mot clef ``DISTINCT`` pour supprimer les répétitions dans notre ensemble derésultats où il y avait plus d'une seule station de métros dans le quartier.    
 * **"AprÚs le 11 septembre, le quartier de 'Battery Park' était interdit d'accÚs pendant plusieurs jours. Combien de personnes ont dût être évacués ?"**
 
   .. code-block:: sql
 
     SELECT Sum(popn_total)
     FROM nyc_neighborhoods AS n
     JOIN nyc_census_blocks AS c 
     ON ST_Intersects(n.the_geom, c.the_geom)  
     WHERE n.name = 'Battery Park';
   
   :: 

     9928
    
 * **"Quelle est la densité de population (personne / km^2) des quartier de 'Upper West Side' et de 'Upper East Side' ?"** (Astuce: il y a 1000000 m^2 dans un km^2.)
 
   .. code-block:: sql
   
     SELECT 
       n.name, 
       Sum(c.popn_total) / (ST_Area(n.the_geom) / 1000000.0) AS popn_per_sqkm
     FROM nyc_census_blocks AS c
     JOIN nyc_neighborhoods AS n
     ON ST_Intersects(c.the_geom, n.the_geom)
     WHERE n.name = 'Upper West Side'
     OR n.name = 'Upper East Side'
     GROUP BY n.name, n.the_geom;
     
   ::
   
           name       |  popn_per_sqkm   
     -----------------+------------------
      Upper East Side | 47943.3590089405
      Upper West Side | 39729.5779474286