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

注解

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

注解

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