source: trunk/workshop-foss4g/spatial_relationships_exercises.rst @ 20

.. _spatial_relationships_exercises:

Section 11: Spatial Relationships Exercises
===========================================

Here's a reminder of the functions we saw in the last section. 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_Contains(geometry A, geometry B)` returns true if geometry A contains geometry B 
* :command:`ST_Crosses(geometry A, geometry B)` returns true if geometry A crosses geometry B
* :command:`ST_Disjoint(geometry A , geometry B)` returns true if the geometries do not "spatially intersect" 
* :command:`ST_Distance(geometry A, geometry B)` returns the minimum distance between geometry A and geometry B
* :command:`ST_DWithin(geometry A, geometry B, radius)` returns true if geometry A is radius distance or less from geometry B
* :command:`ST_Equals(geometry A, geometry B)` returns true if geometry A is the same as geometry B
* :command:`ST_Intersects(geometry A, geometry B)` returns true if geometry A intersects geometry B
* :command:`ST_Overlaps(geometry A, geometry B)` returns true if geometry A and geometry B share space, but are not completely contained by each other.
* :command:`ST_Touches(geometry A, geometry B)` returns true if the boundary of geometry A touches geometry B
* :command:`ST_Within(geometry A, geometry B)` returns true if geometry A is within geometry B

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 geometry value for the street named 'Atlantic Commons'?"**
 
   .. code-block:: sql

     SELECT the_geom
       FROM nyc_streets
       WHERE name = 'Atlantic Commons';

   ::
   
     01050000202669000001000000010200000002000000093235673BE82141F319CD89A22E514170E30E0ADFE82141CB2D3EFFA52E5141
     
 * **"What neighborhood and borough is Atlantic Commons in?"**
     
   .. code-block:: sql

     SELECT name, boroname 
     FROM nyc_neighborhoods 
     WHERE ST_Intersects(
       the_geom,
       '01050000202669000001000000010200000002000000093235673BE82141F319CD89A22E514170E30E0ADFE82141CB2D3EFFA52E5141'
     );

   ::
     
          name    | boroname 
      ------------+----------
       Fort Green | Brooklyn
     

 * **"What streets does Atlantic Commons touch?"** 
 
   .. code-block:: sql

     SELECT name 
     FROM nyc_streets 
     WHERE ST_Touches(
       the_geom, 
       '01050000202669000001000000010200000002000000093235673BE82141F319CD89A22E514170E30E0ADFE82141CB2D3EFFA52E5141'
     );
    
   ::
  
          name      
     ---------------
      S Oxford St
      Cumberland St

   .. image:: ./spatial_relationships/atlantic_commons.jpg
  

 * **"Approximately how many people live on (within 50 meters of) Atlantic Commons?"**
 
   .. code-block:: sql

     SELECT Sum(popn_total)
       FROM nyc_census_blocks
       WHERE ST_DWithin(
        the_geom,
        '01050000202669000001000000010200000002000000093235673BE82141F319CD89A22E514170E30E0ADFE82141CB2D3EFFA52E5141',
        50
        );
        
   :: 
   
     1186