# 14. Spatial Joins Exercises¶

Here’s a reminder of some of the functions we have seen. Hint: 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_Area(geometry)**returns the area of the polygons**ST_AsText(geometry)**returns WKT`text`

**ST_Contains(geometry A, geometry B)**returns the true if geometry A contains geometry B**ST_Distance(geometry A, geometry B)**returns the minimum distance between geometry A and geometry B**ST_DWithin(geometry A, geometry B, radius)**returns the true if geometry A is radius distance or less from geometry B**ST_GeomFromText(text)**returns`geometry`

**ST_Intersects(geometry A, geometry B)**returns the true if geometry A intersects geometry B**ST_Length(linestring)**returns the length of the linestring**ST_Touches(geometry A, geometry B)**returns the true if the boundary of geometry A touches geometry B**ST_Within(geometry A, geometry B)**returns the true if geometry A is within geometry B

Also remember the tables we have available:

`nyc_census_blocks`

name, popn_total, boroname, geom

`nyc_streets`

name, type, geom

`nyc_subway_stations`

name, routes, geom

`nyc_neighborhoods`

name, boroname, geom

## 14.1. Exercises¶

**“What subway station is in ‘Little Italy’? What subway route is it on?”**SELECT s.name, s.routes FROM nyc_subway_stations AS s JOIN nyc_neighborhoods AS n ON ST_Contains(n.geom, s.geom) WHERE n.name = 'Little Italy';

name | routes -----------+-------- Spring St | 6

**“What are all the neighborhoods served by the 6-train?”**(Hint: The`routes`

column in the`nyc_subway_stations`

table has values like ‘B,D,6,V’ and ‘C,6’)SELECT DISTINCT n.name, n.boroname FROM nyc_subway_stations AS s JOIN nyc_neighborhoods AS n ON ST_Contains(n.geom, s.geom) WHERE strpos(s.routes,'6') > 0;

name | boroname --------------------+----------- Midtown | Manhattan Hunts Point | The Bronx Gramercy | Manhattan Little Italy | Manhattan Financial District | Manhattan South Bronx | The Bronx Yorkville | Manhattan Murray Hill | Manhattan Mott Haven | The Bronx Upper East Side | Manhattan Chinatown | Manhattan East Harlem | Manhattan Greenwich Village | Manhattan Parkchester | The Bronx Soundview | The Bronx

Note

We used the

`DISTINCT`

keyword to remove duplicate values from our result set where there were more than one subway station in a neighborhood.**“After 9/11, the ‘Battery Park’ neighborhood was off limits for several days. How many people had to be evacuated?”**SELECT Sum(popn_total) FROM nyc_neighborhoods AS n JOIN nyc_census_blocks AS c ON ST_Intersects(n.geom, c.geom) WHERE n.name = 'Battery Park';

`17153`

**“What are the population density (people / km^2) of the ‘Upper West Side’ and ‘Upper East Side’?”**(Hint: There are 1000000 m^2 in one km^2.)SELECT n.name, Sum(c.popn_total) / (ST_Area(n.geom) / 1000000.0) AS popn_per_sqkm FROM nyc_census_blocks AS c JOIN nyc_neighborhoods AS n ON ST_Intersects(c.geom, n.geom) WHERE n.name = 'Upper West Side' OR n.name = 'Upper East Side' GROUP BY n.name, n.geom;

name | popn_per_sqkm -----------------+------------------ Upper East Side | 48524.4877489857 Upper West Side | 40152.4896080024