# Introduction to PostGIS

PostGIS extends PostgreSQL with robust spatial database management capabilities. More info

# 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:

`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';

Note

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

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

**Previous**: 13. Spatial Joins

**Next**: 15. Spatial Indexing

#### Table Of Contents

#### About Boundless

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.

#### License

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.