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

Note

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 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
```
• “How many census blocks in New York City have a hole in them?”

```SELECT Count(*)
FROM nyc_census_blocks
WHERE ST_NumInteriorRings(ST_GeometryN(geom,1)) > 0;
```

Note

The ST_NRings() functions might be tempting, but it also counts the exterior rings of multi-polygons as well as interior rings. In order to run ST_NumInteriorRings() we need to convert the MultiPolygon geometries of the blocks into simple polygons, so we extract the first polygon from each collection using ST_GeometryN(). Yuck!

```43
```
• “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
```
• “How long is ‘Columbus Cir’ (Columbus Circle)?

```SELECT ST_Length(geom)
FROM nyc_streets
WHERE name = 'Columbus Cir';
```
```308.34199
```
• “What is the JSON representation of the boundary of the ‘West Village’?”

```SELECT ST_AsGeoJSON(geom)
FROM nyc_neighborhoods
WHERE name = 'West Village';
```
```{"type":"MultiPolygon","coordinates":
[[[[583263.2776595836,4509242.6260239873],
[583276.81990686338,4509378.825446927], ...
[583263.2776595836,4509242.6260239873]]]]}
```

The geometry type is “MultiPolygon”, interesting!

• “How many polygons are in the ‘West Village’ multipolygon?”

```SELECT ST_NumGeometries(geom)
FROM nyc_neighborhoods
WHERE name = 'West Village';
```
```1
```

Note

It is not uncommon to find single-element MultiPolygons in spatial tables. Using MultiPolygons allows a table with only one geometry type to store both single- and multi-geometries without using mixed types.

• “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
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
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.