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


Continue Reading

Previous: 13. Spatial Joins

Next: 15. Spatial Indexing

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.

Creative Commons License