Partie 9 : Exercices sur les géométries

Voici un petit rappel de toutes les fonction que nous avons abordé jusqu’à présent. Elles devraient être utiles pour les exercices !

• sum(expression) agrégation retournant la somme d’un ensemble
• count(expression) agrégation retournant le nombre d’éléments d’un ensemble
• ST_GeometryType(geometry) retourne le type de la géométrie
• ST_NDims(geometry) retourne le nombre de dimensions
• ST_SRID(geometry) retourne l’identifiant du système de référence spatiale
• ST_X(point) retourne la coordonnée X
• ST_Y(point) retourne la coordonnée Y
• ST_Length(linestring) retourne la longueur d’une ligne
• ST_StartPoint(geometry) retourne le premier point d’une ligne
• ST_EndPoint(geometry) retourne le dernier point d’une ligne
• ST_NPoints(geometry) retourne le nombre de points d’une ligne
• ST_Area(geometry) retourne l’aire d’un polygone
• ST_NRings(geometry) retourne le nombre de contours (1 ou plus si il y a des trous)
• ST_ExteriorRing(polygon) retourne le contour extérieur (ligne) d’un polygone
• ST_InteriorRingN(polygon, integer) retourne le contour intérieur (ligne) d’un polygone
• ST_Perimeter(geometry) retourne la longueur de tous les contours
• ST_NumGeometries(multi/geomcollection) retourne le nombre de composantes dans une collection
• ST_GeometryN(geometry, integer) retourne la nième entité de la collection
• ST_GeomFromText(text) retourne geometry
• ST_AsText(geometry) retourne WKT text
• ST_AsEWKT(geometry) retourne EWKT text
• ST_GeomFromWKB(bytea) retourne geometry
• ST_AsBinary(geometry) retourne WKB bytea
• ST_AsEWKB(geometry) retourne EWKB bytea
• ST_GeomFromGML(text) retourne geometry
• ST_AsGML(geometry) retourne GML text
• ST_GeomFromKML(text) retourne geometry
• ST_AsKML(geometry) retourne KML text
• ST_AsGeoJSON(geometry) retourne JSON text
• ST_AsSVG(geometry) retourne SVG text

Souvenez-vous aussi des tables disponibles:

• nyc_census_blocks
  • name, popn_total, boroname, the_geom
• nyc_streets
  • name, type, the_geom
• nyc_subway_stations
  • name, the_geom
• nyc_neighborhoods
  • name, boroname, the_geom

Exercices

• “Quelle est l’aire du quartier ‘West Village’?”

  SELECT ST_Area(the_geom)
    FROM nyc_neighborhoods
    WHERE name = 'West Village';
  

  1044614.53027344
  

  Note

  L’aire est donnée en mètres carrés. Pour obtenir l’aire en hectare, divisez par 10000. Pour obtenir l’aire en acres, divisez par 4047.

• “Quelle est l’aire de Manhattan en acres ?” (Astuce: nyc_census_blocks et nyc_neighborhoods ont toutes les deux le champ boroname.)

  SELECT Sum(ST_Area(the_geom)) / 4047
    FROM nyc_neighborhoods
    WHERE boroname = 'Manhattan';
  

  13965.3201224118
  

  or...

  SELECT Sum(ST_Area(the_geom)) / 4047
    FROM nyc_census_blocks
    WHERE boroname = 'Manhattan';
  

  14572.1575543757
  

• “Combien de blocs de la ville de New York ont des trous ?”

  SELECT Count(*)
    FROM nyc_census_blocks
    WHERE ST_NRings(the_geom) > 1;
  

  66
  

• “Quel est la longueur totale des rues (en kilomètres) dans la ville de New York ?” (Astuce: l’unité de mesure des données spatiales est le mètre, il y a 1000 mètres dans un kilomètre.)

  SELECT Sum(ST_Length(the_geom)) / 1000
    FROM nyc_streets;
  

  10418.9047172
  

• “Quelle est la longueur de ‘Columbus Cir’ (Columbus Circle) ?”

  SELECT ST_Length(the_geom)
    FROM nyc_streets
    WHERE name = 'Columbus Cir';
  

  308.34199
  

• “Quelle est le contour de ‘West Village’ au format JSON ?”

  SELECT ST_AsGeoJSON(the_geom)
    FROM nyc_neighborhoods
    WHERE name = 'West Village';
  

  {"type":"MultiPolygon","coordinates":
   [[[[583263.2776595836,4509242.6260239873],
      [583276.81990686338,4509378.825446927], ...
      [583263.2776595836,4509242.6260239873]]]]}
  

La géométrie de type “MultiPolygon”, intéressant !

• “Combien de polygones sont dans le multi-polygone ‘West Village’ ?”

  SELECT ST_NumGeometries(the_geom)
    FROM nyc_neighborhoods
    WHERE name = 'West Village';
  

  1
  

  Note

  Il n’est pas rare de trouver des éléments de type multi-polygone ne contenant qu’un seul polygone dans des tables. L’utilisation du type multi-polygone permet d’utiliser une seule table pour y stocker des géométries simples et multiples sans mélanger les types.

• “Quel est la longueur des rues de la ville de New York, suivant leur type ?”

  SELECT type, Sum(ST_Length(the_geom)) AS length
   FROM nyc_streets
   GROUP BY type
   ORDER BY length DESC;
  

                         type                       |      length
  --------------------------------------------------+------------------
   residential                                      | 8629870.33786606
   motorway                                         | 403622.478126363
   tertiary                                         | 360394.879051303
   motorway_link                                    | 294261.419479668
   secondary                                        | 276264.303897926
   unclassified                                     | 166936.371604458
   primary                                          | 135034.233017947
   footway                                          | 71798.4878378096
   service                                          |  28337.635038596
   trunk                                            | 20353.5819826076
   cycleway                                         | 8863.75144825929
   pedestrian                                       | 4867.05032825026
   construction                                     | 4803.08162103562
   residential; motorway_link                       | 3661.57506293745
   trunk_link                                       | 3202.18981240201
   primary_link                                     | 2492.57457083536
   living_street                                    | 1894.63905457332
   primary; residential; motorway_link; residential | 1367.76576941335
   undefined                                        |  380.53861910346
   steps                                            | 282.745221342127
   motorway_link; residential                       |  215.07778911517

  Note

  La clause ORDER BY length DESC ordonne le résultat par la valeur des longueurs dans l’ordre décroissant. Le résultat avec la plus grande valeur se retrouve au début la liste de résultats.