21. Geometry Constructing 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

  • ST_Area(geometry) returns the area of the geometry

  • ST_Centroid(geometry) returns the geometry centroid

  • ST_Transform(geometry, srid) converts geometries into different spatial reference systems

  • ST_Buffer(geometry, radius) returns an expanded geometry shape

  • ST_Contains(geometry1, geometry2) returns truw if geometry1 contains geometry2

  • ST_Union(geometry[]) returns the aggregate union of all geometries in the group

  • ST_GeometryType(geometry) returns the type of the geometry

  • ST_NumGeometries(geometry) returns the number of geometries in a collection or 1 for simple geometries

  • ST_Intersection(geometry, geometry) returns the area that the two input geometries share in common

Remember the tables we have available:

  • nyc_census_blocks

    • name, popn_total, boroname, geom

  • nyc_streets

    • name, type, geom

  • nyc_subway_stations

    • name, geom

  • nyc_neighborhoods

    • name, boroname, geom

21.1. Exercises

  • How many census blocks don’t contain their own centroid?

    SELECT Count(*)
      FROM nyc_census_blocks
      WHERE NOT
        ST_Contains(
          geom,
          ST_Centroid(geom)
        );
    
    481
    
  • Union all the census blocks into a single output. What kind of geometry is it? How many parts does it have?

    CREATE TABLE nyc_census_blocks_merge AS
      SELECT ST_Union(geom) AS geom
      FROM nyc_census_blocks;
    
    SELECT ST_GeometryType(geom)
      FROM nyc_census_blocks_merge;
    
    ST_MultiPolygon
    
    SELECT ST_NumGeometries(geom)
      FROM nyc_census_blocks_merge;
    
    63
    
  • What is the area of a one unit buffer around the origin? How different is it from what you would expect? Why?

    SELECT ST_Area(ST_Buffer('POINT(0 0)', 1));
    
    3.121445152258052
    

    Note

    A unit circle (circle with radius of one) should have an area of pi, 3.1415926… The difference is due to the linear stroking of the edges of the buffer. The buffer has a finite number of edges. Increasing the number of edges in the buffer will get the value closer to pi, but it will always be smaller due to the linearization.

  • The Brooklyn neighborhoods of ‘Park Slope’ and ‘Carroll Gardens’ are going to war! Construct a polygon delineating a 100 meter wide DMZ on the border between the neighborhoods. What is the area of the DMZ?

    CREATE TABLE brooklyn_dmz AS
      SELECT
        ST_Intersection(
          ST_Buffer(ps.geom, 50),
          ST_Buffer(cg.geom, 50))
        AS geom
      FROM
        nyc_neighborhoods ps,
        nyc_neighborhoods cg
      WHERE ps.name = 'Park Slope'
      AND cg.name = 'Carroll Gardens';
    
    SELECT ST_Area(geom) FROM brooklyn_dmz;
    

    Note

    It is easy to buffer both the neighborhoods of interest, but to get the intersection requires a self-join of the table, creating one relation (ps) with just the “Park Slope” record and another (cg) with just the “Carroll Gardens” record. Note that the area of the intersection is in square meters because we are still working in UTM 18 (EPSG:26918).

    180990.964207547