# 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?**注解

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`

注解

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

注解

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.