# 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

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