10. Geometry Exercises

Here’s a reminder of all the functions we have seen so far. 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_GeometryType(geometry) returns the type of the geometry

• ST_NDims(geometry) returns the number of dimensions of the geometry

• ST_SRID(geometry) returns the spatial reference identifier number of the geometry

• ST_X(point) returns the X ordinate

• ST_Y(point) returns the Y ordinate

• ST_Length(linestring) returns the length of the linestring

• ST_StartPoint(geometry) returns the first coordinate as a point

• ST_EndPoint(geometry) returns the last coordinate as a point

• ST_NPoints(geometry) returns the number of coordinates in the linestring

• ST_Area(geometry) returns the area of the polygons

• ST_NRings(geometry) returns the number of rings (usually 1, more if there are holes)

• ST_ExteriorRing(polygon) returns the outer ring as a linestring

• ST_InteriorRingN(polygon, integer) returns a specified interior ring as a linestring

• ST_Perimeter(geometry) returns the length of all the rings

• ST_NumGeometries(multi/geomcollection) returns the number of parts in the collection

• ST_GeometryN(geometry, integer) returns the specified part of the collection

• ST_GeomFromText(text) returns geometry

• ST_AsText(geometry) returns WKT text

• ST_AsEWKT(geometry) returns EWKT text

• ST_GeomFromWKB(bytea) returns geometry

• ST_AsBinary(geometry) returns WKB bytea

• ST_AsEWKB(geometry) returns EWKB bytea

• ST_GeomFromGML(text) returns geometry

• ST_AsGML(geometry) returns GML text

• ST_GeomFromKML(text) returns geometry

• ST_AsKML(geometry) returns KML text

• ST_AsGeoJSON(geometry) returns JSON text

• ST_AsSVG(geometry) returns SVG text

Also remember the tables we have available:

• nyc_census_blocks

  • blkid, popn_total, boroname, geom

• nyc_streets

  • name, type, geom

• nyc_subway_stations

  • name, geom

• nyc_neighborhoods

  • name, boroname, geom

10.1. Exercises

• What is the area of the ‚West Village‘ neighborhood?

  SELECT ST_Area(geom)
    FROM nyc_neighborhoods
    WHERE name = 'West Village';
  

  1044614.5296486
  

  Bemerkung

  The area is given in square meters. To get an area in hectares, divide by 10000. To get an area in acres, divide by 4047.

• What is the geometry type of ‘Pelham St’? The length?

  SELECT
     ST_GeometryType(geom),
     ST_Length(geom)
    FROM nyc_streets
    WHERE name = 'Pelham St';
  

  ST_MultiLineString
  50.323
  

• What is the GeoJSON representation of the ‚Broad St‘ subway station?

  SELECT
   ST_AsGeoJSON(geom)
  FROM nyc_subway_stations
  WHERE name = 'Broad St';
  

  {"type":"Point",
   "crs":{"type":"name","properties":{"name":"EPSG:26918"}},
   "coordinates":[583571.905921312,4506714.341192182]}
  

• What is the total length of streets (in kilometers) in New York City? (Hint: The units of measurement of the spatial data are meters, there are 1000 meters in a kilometer.)

  SELECT Sum(ST_Length(geom)) / 1000
    FROM nyc_streets;
  

  10418.9047172
  

• What is the area of Manhattan in acres? (Hint: both nyc_census_blocks and nyc_neighborhoods have a boroname in them.)

  SELECT Sum(ST_Area(geom)) / 4047
    FROM nyc_neighborhoods
    WHERE boroname = 'Manhattan';
  

  13965.3201224118
  

  or…

  SELECT Sum(ST_Area(geom)) / 4047
    FROM nyc_census_blocks
    WHERE boroname = 'Manhattan';
  

  14601.3987215548
  

• What is the most westerly subway station?

  SELECT ST_X(geom), name
    FROM nyc_subway_stations
    ORDER BY ST_X(geom)
    LIMIT 1;
  

  Tottenville
  

• How long is ‚Columbus Cir‘ (aka Columbus Circle)?

  SELECT ST_Length(geom)
    FROM nyc_streets
    WHERE name = 'Columbus Cir';
  

  308.34199
  

• What is the length of streets in New York City, summarized by type?

  SELECT type, Sum(ST_Length(geom)) AS length
  FROM nyc_streets
  GROUP BY type
  ORDER BY length DESC;
  

                         type                       |      length
  --------------------------------------------------+------------------
   residential                                      | 8629870.33786606
   motorway                                         | 403622.478126363
   tertiary                                         | 360394.879051303
   motorway_link                                    | 294261.419479668
   secondary                                        | 276264.303897926
   unclassified                                     | 166936.371604458
   primary                                          | 135034.233017947
   footway                                          | 71798.4878378096
   service                                          |  28337.635038596
   trunk                                            | 20353.5819826076
   cycleway                                         | 8863.75144825929
   pedestrian                                       | 4867.05032825026
   construction                                     | 4803.08162103562
   residential; motorway_link                       | 3661.57506293745
   trunk_link                                       | 3202.18981240201
   primary_link                                     | 2492.57457083536
   living_street                                    | 1894.63905457332
   primary; residential; motorway_link; residential | 1367.76576941335
   undefined                                        |  380.53861910346
   steps                                            | 282.745221342127
   motorway_link; residential                       |  215.07778911517
  

  Bemerkung

  The ORDER BY length DESC clause sorts the result by length in descending order. The result is that most prevalent types are first in the list.