# 19. Geography Exercises¶

Here’s a reminder of all the functions we have seen so far. They should be useful for the exercises!

• Sum(number) adds up all the numbers in the result set

• ST_GeogFromText(text) returns a geography

• ST_Distance(geography, geography) returns the distance between geographies

• ST_Transform(geometry, srid) returns geometry, in the new projection

• ST_Length(geography) returns the length of the line

• ST_Intersects(geometry, geometry) returns true if the objects are not disjoint in planar space

• ST_Intersects(geography, geography) returns true if the objects are not disjoint in spheroidal space

Also remember the tables we have available:

• `nyc_streets`

• name, type, geom

• `nyc_neighborhoods`

• name, boroname, geom

## 19.1. Exercises¶

• How far is New York from Seattle? What are the units of the answer?

Nota

New York = `POINT(-74.0064 40.7142)` and Seattle = `POINT(-122.3331 47.6097)`

```SELECT ST_Distance(
'POINT(-74.0064 40.7142)'::geography,
'POINT(-122.3331 47.6097)'::geography
);
```
```3875538.57141352
```
• What is the total length of all streets in New York, calculated on the spheroid?

```SELECT Sum(
ST_Length(Geography(
ST_Transform(geom,4326)
)))
FROM nyc_streets;
```
```10421999.666
```

Nota

The length calculated in the planar «UTM Zone 18» projection is 10418904.717, 0.02% different. UTM is good at preserving area and distance, within the zone boundaries.

• Does ‘POINT(1 2.0001)’ intersect with ‘POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))’ in geography? In geometry? Why the difference?

```SELECT ST_Intersects(
'POINT(1 2.0001)'::geography,
'POLYGON((0 0,0 2,2 2,2 0,0 0))'::geography
);

SELECT ST_Intersects(
'POINT(1 2.0001)'::geometry,
'POLYGON((0 0,0 2,2 2,2 0,0 0))'::geometry
);
```
```true and false
```

Nota

The upper edge of the square is a straight line in geometry, and passes below the point, so the square does not contain the point. The upper edge of the square is a great circle in geography, and passes above the point, so the square does contain the point.