Introduction to PostGIS
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:
- name, popn_total, boroname, geom
- name, type, geom
- name, routes, geom
- name, boroname, geom
“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';
Recall: the function AS is used to give a table another name by using an alias, which can make queries easier to read and write. In this case, s is an alias for nyc_subway_stations, n is an alias for nyc_neighborhoods, s.name refers to the name column in the nyc_subway_stations table, etc.
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
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';
“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
Boundless provides commercial open source software for internet mapping and geospatial application development. We are dedicated to the growth and support of open source software.
This work is licensed under a Creative Non Commercial-Commons Attribution-Share Alike 3.0 United States License. Feel free to use this material, but we ask that you please retain the Boundless branding, logos and style.