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:
name, type, geom
name, boroname, geom
How far is New York from Seattle? What are the units of the answer?
New York =
POINT(-74.0064 40.7142)and Seattle =
SELECT ST_Distance( 'POINT(-74.0064 40.7142)'::geography, 'POINT(-122.3331 47.6097)'::geography );
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;
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.