12. Spatial Relationships Exercises

Here's a reminder of the functions we saw in the last section. They should be useful for the exercises!

  • sum(expression) aggregate to return a sum for a set of records

  • count(expression) aggregate to return the size of a set of records

  • ST_Contains(geometry A, geometry B) returns true if geometry A contains geometry B

  • ST_Crosses(geometry A, geometry B) returns true if geometry A crosses geometry B

  • ST_Disjoint(geometry A , geometry B) returns true if the geometries do not "spatially intersect"

  • ST_Distance(geometry A, geometry B) returns the minimum distance between geometry A and geometry B

  • ST_DWithin(geometry A, geometry B, radius) returns true if geometry A is radius distance or less from geometry B

  • ST_Equals(geometry A, geometry B) returns true if geometry A is the same as geometry B

  • ST_Intersects(geometry A, geometry B) returns true if geometry A intersects geometry B

  • ST_Overlaps(geometry A, geometry B) returns true if geometry A and geometry B share space, but are not completely contained by each other.

  • ST_Touches(geometry A, geometry B) returns true if the boundary of geometry A touches geometry B

  • 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

    • blkid, popn_total, boroname, geom

  • nyc_streets

    • name, type, geom

  • nyc_subway_stations

    • name, geom

  • nyc_neighborhoods

    • name, boroname, geom

12.1. Exercises

  • What is the geometry value for the street named 'Atlantic Commons'?

    SELECT ST_AsText(geom)
      FROM nyc_streets
      WHERE name = 'Atlantic Commons';
    
    MULTILINESTRING((586781.701577724 4504202.15314339,586863.51964484 4504215.9881701))
    
  • What neighborhood and borough is Atlantic Commons in?

    SELECT name, boroname
    FROM nyc_neighborhoods
    WHERE ST_Intersects(
      geom,
      ST_GeomFromText('LINESTRING(586782 4504202,586864 4504216)', 26918)
    );
    
        name    | boroname
    ------------+----------
     Fort Green | Brooklyn
    

    注解

    "Hey, why did you change from a 'MULTILINESTRING' to a 'LINESTRING'?" Spatially they describe the same shape, so going from a single-item multi-geometry to a singleton saves a few keystrokes.

    More importantly, we also rounded the coordinates to make them easier to read, which does actually change results: we couldn't use the ST_Touches() predicate to find out which roads join Atlantic Commons, because the coordinates are not exactly the same anymore.

  • What streets does Atlantic Commons join with?

    SELECT name
    FROM nyc_streets
    WHERE ST_DWithin(
      geom,
      ST_GeomFromText('LINESTRING(586782 4504202,586864 4504216)', 26918),
      0.1
    );
    
         name
    ------------------
     Cumberland St
     Atlantic Commons
    
    _images/atlantic_commons.jpg
  • Approximately how many people live on (within 50 meters of) Atlantic Commons?

    SELECT Sum(popn_total)
      FROM nyc_census_blocks
      WHERE ST_DWithin(
       geom,
       ST_GeomFromText('LINESTRING(586782 4504202,586864 4504216)', 26918),
       50
      );
    
    1438