source: trunk/workshop-foss4g/geometries_exercises.rst @ 15

.. _geometries_exercises:

Section 9: Geometry Exercises
=============================

Here's a reminder of all the functions we have seen so far. They should be useful for the exercises!

 * :command:`sum(expression)` aggregate to return a sum for a set of records
 * :command:`count(expression)` aggregate to return the size of a set of records
 * :command:`ST_GeometryType(geometry)` returns the type of the geometry
 * :command:`ST_NDims(geometry)` returns the number of dimensions of the geometry
 * :command:`ST_SRID(geometry)` returns the spatial reference identifier number of the geometry
 * :command:`ST_X(point)` returns the X ordinate
 * :command:`ST_Y(point)` returns the Y ordinate
 * :command:`ST_Length(linestring)` returns the length of the linestring
 * :command:`ST_StartPoint(geometry)` returns the first coordinate as a point
 * :command:`ST_EndPoint(geometry)` returns the last coordinate as a point
 * :command:`ST_NPoints(geometry)` returns the number of coordinates in the linestring
 * :command:`ST_Area(geometry)` returns the area of the polygons
 * :command:`ST_NRings(geometry)` returns the number of rings (usually 1, more if there are holes)
 * :command:`ST_ExteriorRing(polygon)` returns the outer ring as a linestring
 * :command:`ST_InteriorRingN(polygon, integer)` returns a specified interior ring as a linestring
 * :command:`ST_Perimeter(geometry)` returns the length of all the rings
 * :command:`ST_NumGeometries(multi/geomcollection)` returns the number of parts in the collection
 * :command:`ST_GeometryN(geometry, integer)` returns the specified part of the collection
 * :command:`ST_GeomFromText(text)` returns ``geometry``
 * :command:`ST_AsText(geometry)` returns WKT ``text``
 * :command:`ST_AsEWKT(geometry)` returns EWKT ``text``
 * :command:`ST_GeomFromWKB(bytea)` returns ``geometry``
 * :command:`ST_AsBinary(geometry)` returns WKB ``bytea``
 * :command:`ST_AsEWKB(geometry)` returns EWKB ``bytea``
 * :command:`ST_GeomFromGML(text)` returns ``geometry``
 * :command:`ST_AsGML(geometry)` returns GML ``text``
 * :command:`ST_GeomFromKML(text)` returns ``geometry``
 * :command:`ST_AsKML(geometry)` returns KML ``text``
 * :command:`ST_AsGeoJSON(geometry)` returns JSON ``text``
 * :command:`ST_AsSVG(geometry)` returns SVG ``text``

Also remember the tables we have available:

 * ``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

Exercises
---------

 * **"What is the area of the 'West Village' neighborhood?"**
 
   .. code-block:: sql

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

     1044614.53027344

   .. note::

      The area is given in square meters. To get an area in hectares, divide by 10000. To get an area in acres, divide by 4047.

 * **"What is the area of Manhattan in acres?"** (Hint: both ``nyc_census_blocks`` and ``nyc_neighborhoods`` have a ``boroname`` in them.)
 
   .. code-block:: sql

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

   :: 
   
     13965.3201224118

   or...

   .. code-block:: sql

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

   :: 
   
     14572.1575543757


 * **"How many census blocks in New York City have a hole in them?"**
 
   .. code-block:: sql

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

   :: 
   
     66 
   
 * **"What is the total length of streets (in kilometers) in New York City?"** (Hint: The units of measurement of the spatial data are meters, there are 1000 meters in a kilometer.)
  
    .. code-block:: sql

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

   :: 
   
     10418.9047172

 * **"How long is 'Columbus Cir' (Columbus Circle)?**
 
     .. code-block:: sql
 
      SELECT ST_Length(the_geom)
        FROM nyc_streets
        WHERE name = 'Columbus Cir';

     :: 
   
       308.34199

 * **"What is the JSON representation of the boundary of the 'West Village'?"**
 
   .. code-block:: sql

     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]]]]}

The geometry type is "MultiPolygon", interesting!
   
      
 * **"How many polygons are in the 'West Village' multipolygon?"**
 
   .. code-block:: sql

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

   ::

      1
       
   .. note::
   
      It is not uncommon to find single-element MultiPolygons in spatial tables. Using MultiPolygons allows a table with only one geometry type to store both single- and multi-geometries without using mixed types.
       
       
 * **"What is the length of streets in New York City, summarized by type?"**
 
   .. code-block:: sql

      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::

      The ``ORDER BY length DESC`` clause sorts the result by length in descending order. The result is that most prevalent types are first in the list.