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
Nota
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
andnyc_neighborhoods
have aboroname
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
Nota
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.